Splunk Search

streamstats to get last value with field clause

robertlabrie
Path Finder

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
SplunkTrust
SplunkTrust

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)
---
If this reply helps you, Karma would be appreciated.

robertlabrie
Path Finder

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

0 Karma

sundareshr
Legend

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

0 Karma
Get Updates on the Splunk Community!

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...