Show SSL certs due to expire across windows servers

[stuconz]

I have a CIM compliant log that includes an ssl_end_time which I am having trouble getting splunk to show me only certificates that are due to expire in the next x days.

Below is my query. Any suggestions on how I can get the query to show me only certs that are going to expire based on the ssl_end_time, for example, in the next 30 days?

index=* tag="certificate" ssl_is_valid!=false ssl_subject!="CN=sa*"
| dedup ssl_subject
| convert timeformat="%Y/%m/%d" ctime(ssl_end_time)
| sort +ssl_end_time
| table ssl_start_time ssl_end_time ssl_subject

The log I am getting the data from:
{timestamp="2020-10-29T04:02:49+13:00"
src_host=""
transport=""
ssl_end_time="1745081196"
ssl_engine=""
ssl_hash="sha256RSA"
ssl_is_valid="False"
ssl_issuer="CN=Company Root Certification Authority, DC=company, DC=xx, DC=xx"
ssl_serial="0000000000000000" ssl_start_time="04/19/2018 16:36:36"
ssl_subject="CN=Company Enterprise Certification Authority, DC=company, DC=xx, DC=xx"
ssl_subject_common_name="CN=Company Enterprise Certification Authority, DC=company, DC=xx, DC=xx"
store=""
logtype="certificate"}

thanks for looking

[renjith_nair · Accepted solution]

Try adding this to your search

|eval ssl_end_time=1745081196|eval ExpiryInDays=round((ssl_end_time-now())/86400)

You can further filter based on the no of days. For e.f where ExpiryInDays < 30

[stuconz]

Thanks for that info mate.
Over the last few days I have tried variations of that type of search (with the exception of the rounding), but while I can get a list of all the certificates that are expiring from the logs (ssl_is_valid), and I can get it to convert the expiry date to human readable (for dashboard), I then am having trouble trying to calculate from that converted epoch date whether or not it falls into the time frame requested...e,g <30 days (-30d@d)

[renjith_nair]

Time frame -30d refers to the timestamp at which the events are received and might not have any relationship with the expiry date of the cert. For e.g. you can push the certificate events everyday and in that case, you just need -1d and calculate the expiry as mentioned.

[stuconz]

thanks again, your solution has worked. It was just the order of things that I got incorrect.

New basic search returns number of days before certificate expiry:

index=* tag="certificate" ssl_is_valid!=false ssl_subject!="CN=sa*"
| dedup ssl_subject
| sort +ssl_end_time
| table ssl_end_time host ssl_hash ssl_subject
| eval ExpiryInDays=round((ssl_end_time-now())/86400)
| convert timeformat="%Y/%m/%d" ctime(ssl_end_time)

Thanks you and all the best
~Stu