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
| 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

    • 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