diff between same field between two iterations

[rajkumar_2]

Hi,

I have the below sample data collected after process and using table command every 24 hours. for each time, db, id, we need to calculated the difference. For example the output should be:

prevtime latesttime DB ID diffCN diffSYM diffIS diffSED diffSEC diffSCR diffSCRSYM diffSCRIS diffSCRSED
2017-01-30T02:15:16.000-0800 2017-01-31T02:15:22.000-0800 yods 10019488 0 0 0 0 0 0 0
2017-01-30T02:15:16.000-0800 2017-01-31T02:15:22.000-0800 yods 10000004 0.95 1.38 0.8 1.42 1.32 -1.52 1.48

in above 2 row, for db yods(id 10000004) diffCN value 0.95 came from subtracting 83.24-82.29.
We need to get same for the remaining rows too. Please help.

Sample Data:

_time DB ID CN SYM IS SED SEC SCR SCRSYM SCRIS SCRSED
2017-01-30T02:15:16.000-0800 yods 10019488 0 0 100 0 0 0 0 100 0
2017-01-30T02:15:16.000-0800 yods 10000004 82.29 84.89 78.98 76.53 82.21 34.35 82.56 2.1 0.44
2017-01-30T02:15:16.000-0800 pnrb02 10010160 38.17 54 56.85 32.13 39.47 0 25.35 55 0
2017-01-30T02:15:16.000-0800 caun2 10019060 86.5 84.41 81.37 62.74 90.87 35.74 80.04 0.19 0
2017-01-30T02:15:16.000-0800 cana1s 10018996 0 0 0 0 0 0 0 0 0
2017-01-30T02:15:16.000-0800 produc1 10010352 81.35 88.38 90.54 81.62 81.35 33.24 88.38 11.08 2.43
2017-01-30T02:15:28.000-0800 casn20 10013160 84.07 86.51 78.63 61.84 89.19 28.23 79.32 0.15 0.08

2017-01-31T02:15:22.000-0800 yods 10019488 0 0 100 0 0 0 0 100 0
2017-01-31T02:15:22.000-0800 yods 10000004 83.24 86.27 79.78 77.95 83.53 32.83 84.04 1.51 0.33
2017-01-31T02:15:22.000-0800 pnrb02 10010160 83.24 82.08 92.92 73.87 83.52 0 3.08 92.61 0
2017-01-31T02:15:22.000-0800 caun2 10019060 89.15 89 84.46 64.81 91.94 39.44 85.78 0.15 0
2017-01-31T02:15:22.000-0800 cana1s 10018996 0 0 0 0 0 0 0 0 0
2017-01-31T02:15:22.000-0800 produc1 10010352 92.09 96.33 90.55 89.68 92.06 6.88 96.33 0.96 0.26
2017-01-31T02:15:58.000-0800 casn20 10013160 83.69 86.27 78.11 61.34 88.87 27.78 79.14 0.19 0.1

[somesoni2 · Accepted solution]

Try like this

your current search giving difference right now | eventstats values(diffCN) as diffCN by _time DB

UPdate
Assuming the selected time range only selects data for two days

index=foo sourcetype=bar
| stats min(_time) as prevtime max(_time) as latesttime
earliest(CN) as prevCN latest(CN) as latestCN
earliest(SYM) as prevSYM latest(SYM) as latestSYM
earliest(IS) as prevIS latest(IS) as latestIS
earliest(SED) as prevSED latest(SED) as latestSED
earliest(SEC) as prevSEC latest(SEC) as latestSEC
earliest(SCR) as prevSCR latest(SCR) as latestSCR
earliest(SCRSYM) as prevSCRSYM latest(SCRSYM) as latestSCRSYM
earliest(SCRIS) as prevSCRIS latest(SCRIS) as latestSCRIS
earliest(SCRSED) as prevSCRSED latest(SCRSED) as latestSCRSED by DB ID
| eval diffCN=latestCN-prevCN
| eval diffSYM=latestSYM-prevSYM
| eval diffIS=latestIS-prevIS
| eval diffSED=latestSED-prevSED
| eval diffSEC=latestSEC-prevSEC
| eval diffSCR=latestSCR-prevSCR
| eval diffSCRSYM=latestSCRSYM-prevSCRSYM
| eval diffSCRIS=latestSCRIS-prevSCRIS
| eval diffSCRSED=latestSCRSED-prevSCRSED
| table prevtime latesttime DB ID diff*
| convert ctime(*time) timeformat="%Y-%m-%dT%H:%M:%S.%N%z"

[rajkumar_2]

Thank you. I've only sample data mentioned. Need a query to calculate diffCN.

[rajkumar_2]

Thank you. The query fits the purpose.