Home » Posts tagged 'Query'
Tag Archives: Query
Today I received a question about comparing a performance counter value with the previous performance counter value. In the example, we look at free space on C and compare the current value with the previous. As there are few examples in the community, I thought this would be a good example to share.
Perf | where Computer == "idala" | where CounterName == "% Free Space" | where InstanceName == "C:" | serialize | extend prevValue = prev(CounterValue, 1) | extend diffvalue = CounterValue - prevValue | extend trend = case(CounterValue < prevValue, "Free Space Reduces", CounterValue > prevValue, "Free Space Increases", "No difference") | project TimeGenerated, InstanceName, CounterValue, prevValue, diffvalue, trend | order by TimeGenerated desc
First, we filter on a computer, counter name, and instance name. Before we can use Window functions on the result, we need to SERIALIZE it. SERIALIZE will freeze the result; in other words, freeze the order on the rows.
We use EXTEND to create a new column and assign it the value of the previous counterValue. “1” after CounterValue means we look on the 1 previous row; this is possible to do as we have a serialized set of rows.
We then use EXTEND to create a new column that will hold the difference (diffvalue) between the current counter value and the previous counter value.
Even if it is simple to see if the value has reduced or increased manually, we use CASE, and another EXTEND to write different text strings in a column depending on the current counter value and previous counter value.
Today I would like to quickly share two queries. The first query counts number of devices that sent a heartbeat last month. The second query shows the number of devices sent heartbeat per month, for the current year.
let LastMonthEndDate = (startofmonth(now()) - 1h); let LastMonthStartDate = startofmonth(LastMonthEndDate); Heartbeat | where TimeGenerated between(LastMonthStartDate .. (LastMonthEndDate)) | distinct Computer | summarize ['Devices Last Month'] = count()
let YearStartDate = (startofyear(now()) - 1h); Heartbeat | where TimeGenerated between(YearStartDate .. (now())) | extend Month = (datetime_part("month", TimeGenerated)) | summarize Devices = dcount(Computer) by Month