Archive

Calculate on one field in multiple events

JuGuSm
Path Finder

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 😕

0 Karma

ashajambagi
Communicator

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
0 Karma

JuGuSm
Path Finder

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.

0 Karma

FrankVl
Ultra Champion

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.

0 Karma

FrankVl
Ultra Champion

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
0 Karma

JuGuSm
Path Finder

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

FrankVl
Ultra Champion

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.

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!