Splunk Search

manipulating different table rows

Ctpelster
Engager

Hi, 

I want to create the following excel table using splunk. The first 3 columns are based on the output of a query, something like this: 
<query>index=mfpublic sourcetype=SMF100 IFCID=1 DB2_SHARING_GROUP_NAME=$ssid_tok$ DB2_SUBSYSTEM="DBXH"
| table _time DB2_SSID CPU_accumulated
</query>
The last column is the result of a math operation between first row and second row. Using Excel, column D has the formula: C2-C3 in the first row, then C3-C4 in the second, then C4-C5, and so on. 

(A) Time(B) DB2_SSID(C) CPU_accumulated(D) Difference
17-1-2022 11:20DBXH35536318819569
17-1-2022 11:19DBXH35534361919437
17-1-2022 11:18DBXH35532418221579
17-1-2022 11:17DBXH35530260322657
17-1-2022 11:16DBXH35527994619793
17-1-2022 11:15DBXH355260153-

 

Is it possible to do this math operation between columns from different rows to create another column ? 

After having this "column D" I want to create a line chart based on this information. 

Thanks a lot for your help !! 

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

You can use streamstats only you would need to reverse the order of your events (from your example)

| makeresults
| eval _raw="Time	DB2_SSID	CPU_accumulated	Difference
17-1-2022 11:20	DBXH	355363188	19569
17-1-2022 11:19	DBXH	355343619	19437
17-1-2022 11:18	DBXH	355324182	21579
17-1-2022 11:17	DBXH	355302603	22657
17-1-2022 11:16	DBXH	355279946	19793
17-1-2022 11:15	DBXH	355260153	-"
| multikv forceheader=1
| table Time DB2_SSID CPU_accumulated
| reverse
| streamstats range(CPU_accumulated) as Difference window=2
| reverse

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You can use streamstats only you would need to reverse the order of your events (from your example)

| makeresults
| eval _raw="Time	DB2_SSID	CPU_accumulated	Difference
17-1-2022 11:20	DBXH	355363188	19569
17-1-2022 11:19	DBXH	355343619	19437
17-1-2022 11:18	DBXH	355324182	21579
17-1-2022 11:17	DBXH	355302603	22657
17-1-2022 11:16	DBXH	355279946	19793
17-1-2022 11:15	DBXH	355260153	-"
| multikv forceheader=1
| table Time DB2_SSID CPU_accumulated
| reverse
| streamstats range(CPU_accumulated) as Difference window=2
| reverse
0 Karma

Ctpelster
Engager

Yes, it worked ! thank you. 

I changed the sort option in the query, instead of using the reverse you suggested. 

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...