Forum Discussion

Arslan11's avatar
Arslan11
Brass Contributor
May 04, 2020

Machine not sedning pings

Kusto query 

Heartbeat
| where TimeGenerated > ago(24h)
| where Computer != "NH-CMVMAAZ.networkhg.org.uk" and Computer != "UAT-WVD-REL86-0.networkhg.org.uk"
| summarize LastCall = max(TimeGenerated) by Computer, ComputerEnvironment
| where LastCall < ago(10m

 

I need assistance with this query, I don't want to be reported for the following servers in not sending pings, those severs get shutdown at 10:00pm UK time and starts at 6:00am uk time.

 

I don't want those servers to be reported from 10:00pm to 6:00am, how can I amend my existing query and make this possible

  • Arslan11 

     

    Look out for a Blog post on KQL and Time from me on the Sentinel blog, hopefully later this week.  Here we get just the "hours" from the TimeGenerated and use that to say, I only want this period of Hours between 07am and 22pm.  Please remove the "hour" column when you are happy this works as expected. 

     

    Heartbeat
    | where TimeGenerated > ago(1d)
    | extend hour = datetime_part("hour", TimeGenerated)
    | where hour between (07 .. 22)
    | summarize LastCall = max(TimeGenerated) by Computer, ComputerEnvironment, hour
    | where LastCall < ago(10m)
    | order by hour asc 
    

       

    • Arslan11's avatar
      Arslan11
      Brass Contributor

      CliveWatsonThanks, you mentioned to remove the hour column, if I will do that, then the hour between will not work, or you want me to still remove it

       

       

      Heartbeat
      | where TimeGenerated > ago(1d)
      | where Computer != "NH-CMVMAAZ.networkhg.org.uk" and Computer != "UAT-WVD-REL86-0.networkhg.org.uk"
      //| where Computer == "demo2"
      | extend hour = datetime_part("hour", TimeGenerated)
      | where hour between (07 .. 22)
      | summarize LastCall = max(TimeGenerated) by Computer, ComputerEnvironment, hour
      | where LastCall < ago(10m)
      | order by hour asc

       

      • CliveWatson's avatar
        CliveWatson
        Icon for Microsoft rankMicrosoft

        Arslan11 

         

        Sorry I meant from the Summarize line (you do need it until then), summarize becomes this 

         

        | summarize LastCall = max(TimeGenerated) by Computer, ComputerEnvironment

         

        I just removed the ", hour" from the end of the line.

Resources