Kusto: query for ssl cert expiry date

Copper Contributor

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
| where EventLog == "Microsoft-Windows-CertificateServicesClient-Lifecycle-System/Operational"
| where EventID == 1003
| parse EventData with * "<SubjectName>" subject: string "<" *
| parse EventData with * "<NotValidAfter>" CertExpDate: datetime "<" *
| extend DaysLeft = TimeGenerated - CertExpDate
| project Computer, CertExpDate, DaysLeft,EventID, RenderedDescription, subject, Message
//| top 20 by DaysLeft asc
| order by DaysLeft asc

 

 

Output  "29.23:44:22.9440000"

 

I need to trim to above value in days format only. I'e   "29".

 

I'm available on Microsoft Teams @email address removed for privacy reasons if anyone can help me instantly.

 

Thanks in advance.

 

4 Replies

Hello @Bhagyesh1984,

 

You can use "parse" operator to parse the output and then "project" only the number of days.

parse operator - Azure Data Explorer | Microsoft Learn 

tried but no luck..
@Bhagyesh1984
Have you managed to work this out? I am also looking for similar function.

Thanks

@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