How to Sum Latest and Previous Field1 from multiple Field2.

[keanhong]

Hi All,

I have a problem to form the logic for sorting Latest and Previous Data to compare.
Looking Field1=Status , and Field2=ID and sort by Latest compare with Previous.

Search and Filter Data as below.
Event 1 -> Time=10:02AM , Field1=100 , Field2=1
Event 2 -> Time=10:01AM, Field1=50, Field2=2

Event 3 -> Time=9:25AM, Field1=80, Field2=1
Event 4 -> Time=9:24AM, Field1=40, Field2=2

Event 5 -> Time=9:05AM, Field1=70, Field2=1
Event 6 -> Time=9:02AM, Field1=20, Field2=2

End Result
Total Field1=150(which sum from 100+50) by picking latest from Field2=1&2.
And compare previous result Field1=120(which sum from 80+40) by picking 2nd latest from Field2=1&2.

My objective is to present the values different for Single Value Visualization.

Thanks.

[woodcock]

So the right answer is 30, right? Try this:

| makeresults 
| eval raw="_time=10:02AM,Field1=100,Field2=1 _time=10:01AM,Field1=50,Field2=2 _time=9:25AM,Field1=80,Field2=1 _time=9:24AM,Field1=40,Field2=2 _time=9:05AM,Field1=70,Field2=1 _time=9:02AM,Field1=20,Field2=2" 
| makemv raw 
| mvexpand raw 
| rename raw AS _raw
| kv
| eval _time = strptime(time, "%H:%M%p")
| sort 0 - _time

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| streamstats count(eval(Field2==1)) AS sessionID
| stats sum(Field1) AS Field1 BY sessionID
| head 2
| stats range(Field1)

[keanhong]

Hi First of all, thanks for responding this.
Might be i miss interpret my question.

I'm collecting the event from 2 Devices and sum up their sessions count to present in Single Value Visualization Chart. So every 10 minutes the Splunk will receive the message from our poller.

Thus in order to present in the Single Value Visulization i need latest and 2nd latest for the chart to compare and return the result.

[woodcock]

Give a mockup of what you expect the result to be given the raw event data that you showed in the question.

[keanhong]

This is the single value chart that i expect in the end.
Maybe i basic understanding of splunk not that well. Apologize for this.

[woodcock]

No, show me some of the actual events and then show me the data you expect to be generated on the stats page (not the visualization page).

[Sukisen1981]

Try this -

<your index>| head 4
| streamstats count as row
| streamstats current=f window=1 last(Field1) as prev_field1,last(Field2) as prev_field2
| table row,Field1,prev_field1,Field2,prev_field2
| eval tot_fld1=if(row=2,Field1+prev_field1,0),tot_fld2=if(row=4,Field1+prev_field1,0)
| stats sum(tot_fld1) as field1_latest,sum(tot_fld2) as field1_second_latest

Choose trellis layout in single value viz.

[keanhong]

Hi, this is very close. I tried but somethings the result return in opposite and possible that we dont use the trellis? I tried few ways to edit based on the logic you given but still failed to produce it.

But thanks alot for responding!