I have the query below that checks for the expiration date of a certificate, converts it to epoch time, and then basically changes the value of the result as it 'degrades' (gets closer to expiration). I have a feeling this is really messy and could be improved on, so I'm just looking for general recommendations on a better way of doing it. It works, but to me it looks excessive.
index=test host=mycertificateauthority| rex field=Line "(?\d{1,2}\/\d{1,2}\/\d{4})" | stats count by _time,host,date | eval dateepoch=strptime(date,"%m/%d/%Y") | eval thirtydays=(relative_time(dateepoch,"-30d")) | eval fifteendays=(relative_time(dateepoch,"-15d")) | eval fivedays=(relative_time(dateepoch,"-5d")) | eval result=case((now()<=thirtydays),"0",(now()>=thirtydays) AND (now()<=fifteendays) AND (now()<=fivedays) AND (now()<=dateepoch),"1",(now()>=thirtydays) AND (now()>=fifteendays) AND (now()<=fivedays) AND (now()<=dateepoch),"2",(now()>=thirtydays) AND (now()>=fifteendays) AND (now()>=fivedays) AND (now()<=dateepoch),"3",(now()>=thirtydays) AND (now()>=fifteendays) AND (now()>=fivedays) AND (now()>=dateepoch),"4")
Try like this
index=test host=mycertificateauthority| rex field=Line "(?<date>\d{1,2}\/\d{1,2}\/\d{4})" | stats count by _time,host,date | eval dateepoch=strptime(date,"%m/%d/%Y") | eval thirtydays=(relative_time(dateepoch,"-30d")) | eval fifteendays=(relative_time(dateepoch,"-15d")) | eval fivedays=(relative_time(dateepoch,"-5d"))
| eval result=case((now()<=thirtydays),"0",
(now()>=thirtydays) AND (now()<=fifteendays) ,"1",
(now()>=fifteendays) AND (now()<=fivedays) ,"2",
(now()>=fivedays) AND (now()<=dateepoch),"3",
(now()>=dateepoch),"4")
since you're using case, you don't have to repeat already checked expressions.
Try like this
index=test host=mycertificateauthority| rex field=Line "(?<date>\d{1,2}\/\d{1,2}\/\d{4})" | stats count by _time,host,date | eval dateepoch=strptime(date,"%m/%d/%Y") | eval thirtydays=(relative_time(dateepoch,"-30d")) | eval fifteendays=(relative_time(dateepoch,"-15d")) | eval fivedays=(relative_time(dateepoch,"-5d"))
| eval result=case((now()<=thirtydays),"0",
(now()>=thirtydays) AND (now()<=fifteendays) ,"1",
(now()>=fifteendays) AND (now()<=fivedays) ,"2",
(now()>=fivedays) AND (now()<=dateepoch),"3",
(now()>=dateepoch),"4")
since you're using case, you don't have to repeat already checked expressions.
Great, thank you. I was thinking I was missing something, this helps.
How fast does it run? You could package this up in a macro so its more user friendly.
If its fast and works correctly, then there isn't much need to clean it up