Splunk Search

CPU time delta calculation

vk1544
Explorer

Hi i have the below data 

_time SQL_ID NEWCPUTIME
2023-10-25T12:02:10.140+01:00 ABCD 155.42
2023-10-25T11:57:10.140+01:00 ABCD 146.76
2023-10-25T11:47:10.156+01:00 ABCD 129.34
2023-10-25T11:42:10.163+01:00 ABCD 118.84
2023-10-25T12:07:10.070+01:00 ABCD 163.27
2023-10-25T11:52:10.150+01:00 ABCD 139.34

 

EXPECTED OUTPUT is

 

output      
_time SQL_ID NEWCPUTIME delta
2023-10-25T12:07:10.070+01:00 ABCD 163.27 7.85
2023-10-25T12:02:10.140+01:00 ABCD 155.42 8.66
2023-10-25T11:57:10.140+01:00 ABCD 146.76 7.42
2023-10-25T11:52:10.150+01:00 ABCD 139.34 10
2023-10-25T11:47:10.156+01:00 ABCD 129.34 10.5
2023-10-25T11:42:10.163+01:00 ABCD 118.84 118.84

 

SPLUNK  output  which is not correct

output      
_time SQL_ID NEWCPUTIME delta
2023-10-25T12:07:10.070+01:00 ABCD 163.27  
2023-10-25T12:02:10.140+01:00 ABCD 155.42 7.85
2023-10-25T11:57:10.140+01:00 ABCD 146.76 8.66
2023-10-25T11:52:10.150+01:00 ABCD 139.34 7.42
2023-10-25T11:47:10.156+01:00 ABCD 129.34 10
2023-10-25T11:42:10.163+01:00 ABCD 118.84 10.5

 

im using the below query 

index=data sourcetype=dataset source="/usr2/data/data_STATISTICS.txt"  SQL_ID= ABCD |streamstats current=f window=1 global=f last(NEWCPUTIME) as last_field by SQL_ID |eval NEW_CPU_VALUE =abs(last_field - NEWCPUTIME) |table _time,SQL_ID, last_field,NEWCPUTIME,NEW_CPU_VALUE

 

i tried using delta command as well however im not getting the expected output as well 

Labels (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

The output may not be what is desired, but it is correct.  The streamstats and delta commands compute the difference between the current result and the previous result rather than between the current result and the next result (which is unseen at the time).

One workaround may be to surround the streamstats or delta command with reverse, which will change the order of events and then change it back.

index=data sourcetype=dataset source="/usr2/data/data_STATISTICS.txt"  SQL_ID= ABCD 
| reverse
|streamstats current=f window=1 global=f last(NEWCPUTIME) as last_field by SQL_ID
| reverse 
|eval NEW_CPU_VALUE =abs(last_field - NEWCPUTIME) 
|table _time,SQL_ID, last_field,NEWCPUTIME,NEW_CPU_VALUE

 

---
If this reply helps you, Karma would be appreciated.
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.
Get Updates on the Splunk Community!

Beyond Detection: How Splunk and Cisco Integrated Security Platforms Transform ...

Financial services organizations face an impossible equation: maintain 99.9% uptime for mission-critical ...

Customer success is front and center at .conf25

Hi Splunkers, If you are not able to be at .conf25 in person, you can still learn about all the latest news ...

.conf25 Global Broadcast: Don’t Miss a Moment

Hello Splunkers, .conf25 is only a click away.  Not able to make it to .conf25 in person? No worries, you can ...