topic Calculate on one field in multiple events in Splunk Search

Calculate on one field in multiple events

JuGuSm — Wed, 20 Feb 2019 09:53:18 GMT

Hi,

I collect json data like this:

{"timestamp":"2019.02.19-10:20:30","label":"xxx","size":"100"}
{"timestamp":"2019.02.19-10:20:30","label":"yyy","size":"250"}
{"timestamp":"2019.02.19-10:20:30","label":"zzz","size":"300"}

{"timestamp":"2019.02.20-10:20:30","label":"xxx","size":"400"}
{"timestamp":"2019.02.20-10:20:30","label":"yyy","size":"500"}
{"timestamp":"2019.02.20-10:20:30","label":"zzz","size":"900"}

and I would like to calculate difference between the two sizes of the same label, i.e.:

+-------+--------+--------+-----------+
| label | size_1 | size_2 | diff_size |
+-------+--------+--------+-----------+
| xxx   | 100    | 400    | 300       |
+-------+--------+--------+-----------+
| yyy   | 250    | 500    | 250       |
+-------+--------+--------+-----------+
| zzz   | 300    | 900    | 600       |
+-------+--------+--------+-----------+

I tried many methods (chart over by, xqueries, subsearches, etc) but I never get this result 😕

Re: Calculate on one field in multiple events

FrankVl — Wed, 20 Feb 2019 10:02:51 GMT

You could try something like this:

...your search to get to the data...
| stats range(size) as diff_size earliest(size) as size_1 latest(size) as size_2 by label

Re: Calculate on one field in multiple events

ashajambagi — Wed, 20 Feb 2019 10:17:21 GMT

Try this:

| makeresults 
| eval "timestamp"="2019.02.19-10:20:30","label"="xxx","size"="100" 
| append 
    [ makeresults 
    | eval 
        "timestamp"="2019.02.19-10:20:30","label"="yyy","size"="250"] 
| append 
    [ makeresults 
    | eval
        "timestamp"="2019.02.19-10:20:30","label"="zzz","size"="300"] 
| append 
    [ makeresults 
    | eval "timestamp"="2019.02.20-10:20:30","label"="xxx","size"="400"] 
| append 
    [ makeresults 
    | eval "timestamp"="2019.02.20-10:20:30","label"="yyy","size"="500"] 
| append 
    [ makeresults 
    | eval 
        "timestamp"="2019.02.20-10:20:30","label"="zzz","size"="900"] 
| fields - _time timestamp
| stats  first(size) as size_1 last(size) as size_2 range(size) as diff_size by label

Re: Calculate on one field in multiple events

JuGuSm — Tue, 29 Sep 2020 23:18:31 GMT

That's great! It is almost what I want but sometimes size_1 is greater than size_2 and I need to have size_1 equal to the first value and size_2 to the last 😕

In fact, I misspoke because there is 3 values and it makes thing more complicated :

{"timestamp":"2019.02.18-10:20:30","label":"xxx","size":"400"}
{"timestamp":"2019.02.18-10:20:30","label":"yyy","size":"250"}
{"timestamp":"2019.02.18-10:20:30","label":"zzz","size":"300"}

{"timestamp":"2019.02.19-10:20:30","label":"xxx","size":"500"}
{"timestamp":"2019.02.19-10:20:30","label":"yyy","size":"400"}
{"timestamp":"2019.02.19-10:20:30","label":"zzz","size":"900"}

{"timestamp":"2019.02.20-10:20:30","label":"xxx","size":"450"}
{"timestamp":"2019.02.20-10:20:30","label":"yyy","size":"300"}
{"timestamp":"2019.02.20-10:20:30","label":"zzz","size":"600"}

+-------+--------+--------+--------+----------+----------+
| label | size_1 | size_2 | size_3 | diff_1_2 | diff_2_3 |
+-------+--------+--------+--------+----------+----------+
| xxx   | 400    | 500    | 450    | 100      | -50      |
+-------+--------+--------+--------+----------+----------+
| yyy   | 250    | 400    | 300    | 150      | -100     |
+-------+--------+--------+--------+----------+----------+
| zzz   | 300    | 900    | 600    | 600      | -300     |
+-------+--------+--------+--------+----------+----------+

Re: Calculate on one field in multiple events

JuGuSm — Wed, 20 Feb 2019 10:53:12 GMT

Thank you very much for reply. Are first() and last() different from earliest() and latest()? Because it can be very usefull in this case but I'm not sure because it seems to generate the same result than FrankVl's answer.

Re: Calculate on one field in multiple events

FrankVl — Wed, 20 Feb 2019 11:45:26 GMT

first() and last() depend on how the events were sorted before the stats command was invoked. Earliest and latest always return the earliest and latest based on _time.

Re: Calculate on one field in multiple events

FrankVl — Wed, 20 Feb 2019 12:08:16 GMT

That indeed makes it a bit more complicated.

You could try:

...
| stats earliest(size) as size_1 list(size) as size_2 latest(size) as size_3 by label
| eval size_2 = mvindex(size_2, 1)
| eval diff_1_2 = size_2 - size_1
| eval diff_2_3 = size_3 - size_2

list(size) will return a multivalued field with all 3 sizes, in the original order. Using the mvindex you then pick the second (index starts at 0) value. Then you can calculate the two diffs.