Splunk Search

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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...