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.