Calculate on one field in multiple events

[JuGuSm]

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 😕

[ashajambagi]

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

[JuGuSm]

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.

[FrankVl]

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.

[FrankVl]

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

[JuGuSm]

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

[FrankVl]

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.