topic Re: Sum of values and max (or last) value in the same query in Splunk Search

Sum of values and max (or last) value in the same query

piretro999 — Fri, 13 Apr 2018 14:17:05 GMT

Hello I have to build up a query on Splunk, on wich I am a real newbie.
I have a sheet in wich every record contains a name, an event, some points related to the single event and a sum of the points that comes from all the previous events plus the present one.
E.G.
|Person |Action |Pt |Tot|
|John |"eats a pie" | 1 | 1|
|John |"does a jump" | 3| 4|
|John |"goes to bed" | 5| 9|
|Tim |"tells a lie" | 7| 11 |

The query should show should be something like this
|Person |Pt|Tot|
|John | 9| 9|
|Tim | 7| 11|

As a next step, if the two values does not match (like for Tim), then an alert is raised.
What query can I implement?
Thank you.
Paolo

Re: Sum of values and max (or last) value in the same query

somesoni2 — Fri, 13 Apr 2018 16:47:34 GMT

Try something like this

your base search which gives fields Person, Action Pt Tot
| stast sum(Pt) as Pt max(Tot) as Tot by Person
| where Pt!=Tot

You can setup alert using this query when "number of events is greater than 0" (means there is a mismatch in Pt and Tot)

Re: Sum of values and max (or last) value in the same query

adonio — Fri, 13 Apr 2018 16:57:05 GMT

hello there,
considering the Tot field values are accumulative and accurate you can try something like this maybe:
... your search ... | stats sum(Pt) as Points max(Tot) as accum_total by Person
then apply your alert rule either in search or while saving as an alert.
in search | where Point!=accum_total
in alert | search Point!=accum_total
hope it helps