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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...