CPU time delta calculation

[vk1544]

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 

[richgalloway]

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