topic Re: manipulating different table rows in Splunk Search

manipulating different table rows

Ctpelster — Mon, 17 Jan 2022 10:55:06 GMT

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

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

Re: manipulating different table rows

ITWhisperer — Mon, 17 Jan 2022 11:06:22 GMT

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

Re: manipulating different table rows

Ctpelster — Mon, 17 Jan 2022 15:25:25 GMT

Yes, it worked ! thank you.

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