Heartbeat availability according to ComputerIP

[Oleg__D]

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 . Ideally graph should render the 30 days timeline with list of computers and on which day computer was not responsive.

Here is the query I started with, but I guess summarize is not the thing I am looking for, since it just show the sum:

Heartbeat
| where TimeGenerated > ago(30d)
| where Computer contains "GRP"
| summarize dcount(ComputerIP) by bin(TimeGenerated, 1d),
Computer
| project tostring(split(Computer, ".")[0]), dcount_ComputerIP
| render timechart title="Availability computers - daily"

Could you tell please if there is a better way to build it? So, it would have time scale on X-axis and servers on Y-axis. The graph basically should go down when the server was not available.

Thank you.

[#2]

@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 :D

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

[#3 · Accepted solution]

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

[#4]

Thanks a lot! Didn't even thought about series statement. Now it looks exactly how it should be.
Actually you helped me to find out that ComputerIP is not the best value to use in this case :)

I will try to combine it with availability rate (buckets) calculation.