streamstats to get last value with field clause

[robertlabrie]

I'm using streamstats to get some values from the last event, but I need to do it where that last event has a property matching a value.

So I'm trying to solve the problem of inaccurate PercentProcessorTime with the Windows perf data. I started with the discussion here, but it's morphed beyond that now. So to start, I've got a WMI query which leverages Win32_PerfRawData_PerfProc_Process.

wql = Select IDProcess,Name,PercentProcessorTime,TimeStamp_PerfTime,Frequency_PerfTime,PercentUserTime,TimeStamp_Sys100NS from Win32_PerfRawData_PerfProc_Process where Name = "SQLsafeBackupService" OR Name = "sqlwriter" OR Name = "sqlservr" OR Name = "SQLAGENT" OR Name = "sqlservr#1" OR Name = "SQLAGENT#1" OR Name = "w3wp" OR Name = "sqlbrowser"

Then using my Google-foo, I found this math to cook the value:

eval cputime = 100 * (PercentProcessorTime - last_PercentProcessorTime) / (Timestamp_Sys100NS - last_Timestamp_Sys100NS)

So far so good, this search actually does exactly what I want:

earliest=-10m index=rel_test Name=sqlservr | reverse | streamstats current=f last(PercentProcessorTime) as last_PercentProcessorTime last(Timestamp_Sys100NS) as last_Timestamp_Sys100NS | eval cputime = 100 * (PercentProcessorTime - last_PercentProcessorTime) / (Timestamp_Sys100NS - last_Timestamp_Sys100NS) | timechart span=3 avg(cputime)

Except that I'm stuck with just one "Name". In the data, Name is a unique identifier which identifies a process. So I need to compare sqlservr to the last sqlservr, but if I include all processes (which is what I want), then I have no way to compare to the last sqlservr and not the last w3wp. Does this make sense? Does anyone have a solution? I tried sorting on Name, but the deltas in the counters when the Name changes throws the results out of whack. After running timechart, if I could throw away the first row, that would work too

[richgalloway]

Just a shot in the dark, but have you tried grouping by Name in the streamstats command?

earliest=-10m index=rel_test | reverse | streamstats current=f last(PercentProcessorTime) as last_PercentProcessorTime last(Timestamp_Sys100NS) as last_Timestamp_Sys100NS by Name | eval cputime = 100 * (PercentProcessorTime - last_PercentProcessorTime) / (Timestamp_Sys100NS - last_Timestamp_Sys100NS) | timechart span=3 avg(cputime)

[robertlabrie]

This gets close, but when you group by, the first value of the second name gets subtracted from the last value of the first name, and the chart gets way out of whack

[sundareshr]

You may be able to mitigate that by adding current=f to the streamstats command