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 !!
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
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
Yes, it worked ! thank you.
I changed the sort option in the query, instead of using the reverse you suggested.