Forum Discussion

Bhagyesh1984's avatar
Bhagyesh1984
Copper Contributor
Oct 10, 2022

Kusto: query for ssl cert expiry date

I'm looking for windows cert expiry data for total days left by using below kusto query. I can get the remaining days left but it is not in right format.  Need only days left.   KQL Query   Event...
kusto666's avatar
kusto666
Copper Contributor
Jan 20, 2023
Bhagyesh1984
Have you managed to work this out? I am also looking for similar function.

Thanks
Bhagyesh1984's avatar
Bhagyesh1984
Copper Contributor
Jan 23, 2023

kusto666  Yes,

 

Here is the query..

 

Event
| where EventLog == "Microsoft-Windows-CertificateServicesClient-Lifecycle-System/Operational"
| where EventID == 1003
| parse EventData with * "<SubjectName>" subject: string "<" *
| parse EventData with * "<NotValidAfter>" CertExpDate: datetime "<" *
| extend remainingday = CertExpDate - TimeGenerated
//| extend val = format_timespan(DaysLeft, 'd')
| extend DaysLeft = datetime_diff('day', CertExpDate, TimeGenerated)
//| summarize count() by subject, CertExpDate, val, RenderedDescription,Message, Computer
| where DaysLeft <= 45
| summarize max(TimeGenerated)by DaysLeft, Computer, CertExpDate, EventID,subject
//| summarize count() by subject, CertExpDate,EventID, RenderedDescription,Message, DaysLeft, Computer
//| top 20 by DaysLeft asc
//| order by DaysLeft asc

Resources