Forum Discussion

Oleg__D's avatar
Oleg__D
Copper Contributor
Nov 24, 2020
Solved

Heartbeat availability according to ComputerIP

Hello, Since I am novice in KQL I am struggling a bit with a chart I would like to build.  I would like the query to render a graph where I can watch the servers availability according to ComputerIP...
  • GaryBushey's avatar
    GaryBushey
    Nov 24, 2020

    Oleg__D You were very close.   What I did was make sure the bin(TimeGenerated,1d) value was passed through so you could use it as the X-axis and change dcount to count.  I also added names to everything, but that is just me, it isn't required.   The thing with dcount is, according to the documentation, it "Returns an **estimate** for the number of distinct values that are taken by a scalar expression in the summary group."  (** added for emphasis).  count is better for smaller sample sizes.  The only problem with this query is that it will not show any entries that do not have a value for given date so you would need to know how to interpret the results.

     

    Heartbeat
    | where TimeGenerated > ago(30d)
    | summarize CountComputerIP = count(ComputerIP) by Computer, newDate = bin(TimeGenerated, 1d)
    | project Computer = tostring(split(Computer, ".")[0]), CountComputerIP, newDate
    | render timechart title="Availability computers - daily"
     
    Rewriting the query as below will show you all those dates where a computer has 0 entries
     
    Heartbeat
    | make-series  CountComputerIP = count(ComputerIP) default=0 on TimeGenerated from ago(30d) to now() step 1d by Computer
    | render timechart title="Availability computers - daily"
JKatzmandu's avatar
JKatzmandu
Brass Contributor
Nov 24, 2020

Oleg__D 

 

Here's a query I came up with that isn't for a visualisation, but gives some interesting stats. I may play with this later because I need the challenge 😄

 

Heartbeat
| where TimeGenerated between (ago(30d) .. now())
| summarize min(TimeGenerated), max(TimeGenerated) by Computer, OSType
| extend coverage_duration = max_TimeGenerated - min_TimeGenerated
| extend how_old = now() - max_TimeGenerated
| where how_old > 1h
| sort by how_old desc

 

 

Resources