how to sum all average values within the hour and timechart only the latest?

[kamgineer]

I guess my question is a little hard to explain... so let me start by giving you an example of the data I'm working with:

2016-04-14 09:12:02.393040   VolID=164   Name=qai-mqa2qa                       alloc=4000 used=0    service=mssql      database=qai-qa    
2016-04-14 09:12:02.393040   VolID=162   Name=sf-ny2-vm-win-9                  alloc=2000 used=937  service=vmware     vmware_os=windows   
2016-04-14 09:12:02.393040   VolID=157   Name=sf-ny2-vm-win-10                 alloc=2000 used=343  service=vmware     vmware_os=windows   
2016-04-14 09:12:02.393040   VolID=156   Name=exchange-ex23-1                  alloc=2000 used=805  service=exchange  

2016-04-14 10:12:02.393040   VolID=164   Name=qai-mqa2qa                       alloc=4000 used=0    service=mssql      database=qai-qa    
2016-04-14 10:12:02.393040   VolID=162   Name=sf-ny2-vm-win-9                  alloc=2000 used=937  service=vmware     vmware_os=windows   
2016-04-14 10:12:02.393040   VolID=157   Name=sf-ny2-vm-win-10                 alloc=2000 used=343  service=vmware     vmware_os=windows   
2016-04-14 10:12:02.393040   VolID=156   Name=exchange-ex23-1                  alloc=2000 used=805  service=exchange  

2016-04-14 11:12:02.393040   VolID=164   Name=qai-mqa2qa                       alloc=4000 used=0    service=mssql      database=qai-qa    
2016-04-14 11:12:02.393040   VolID=162   Name=sf-ny2-vm-win-9                  alloc=2000 used=937  service=vmware     vmware_os=windows   
2016-04-14 11:12:02.393040   VolID=157   Name=sf-ny2-vm-win-10                 alloc=2000 used=343  service=vmware     vmware_os=windows   
2016-04-14 11:12:02.393040   VolID=156   Name=exchange-ex23-1                  alloc=2000 used=805  service=exchange  

So, "about" every hour I send the above data to Splunk. I say "about" every hour because it can sometimes be several times an hour. The only unique thing about every "dump" is the exact time stamp. For every "dump" data, all of the time stamps will be the same for that dump.

Is there a way to sum up all of the data by "service" for every uniq dump?

For example. something like this works (except for the time when there is more than one "dump" per hour):

index=solidfire service host=sf.ny2.hcmny.com| timechart span=1hr sum(used) by service

And for part 2 of the question, how would i only display the "most recent" sum?
Something like this works, but when I put it in a dashboard, the sum seems to grow to a cumulative sum, instead of the "latest" sum:

index=solidfire service host=sf.ny2.hcmny.com| timechart span=1hr sum(used) by service |tail 1

So I want my end result to look something like this:

[somesoni2 · Accepted solution]

Answer 1 : Use this to group your summary by the dumps (assuming same timestamp as stated)
Updated corrected typo on over_time, it should be over _time

 index=solidfire service host=sf.ny2.hcmny.com | bucket span=1m _time |chart sum(used) over _time by service

Answer 2: MOst recent sum

Working query:

 index=solidfire service host=sf.ny2.hcmny.com  | eventstats max(_time) as max by service | where _time=max  | stats sum(used) by service

other attempts

 index=solidfire service host=sf.ny2.hcmny.com | bucket span=1m _time | dedup _time | stats sum(used) by service

OR

 index=solidfire service host=sf.ny2.hcmny.com | bucket span=1m _time | stats latest(used) as used by service | stats sum(used) by service

[kamgineer]

hi, and thank you!

So Answer #1 looks good, I didn't know about the "over _time" command, thank you,

Answer #2 on the other hand does not give me the right results, the sum numbers are definitely wrong, see stats below:

service sum(used)
dpm 1662
exchange    804
mssql   1
oracle  7516
vmware  277

[somesoni2]

For Answer 2, which query did you try?

[kamgineer]

i tried both:

1) index=solidfire service host=sf.ny2.hcmny.com | bucket span=1m _time | dedup _time | stats sum(used) by service
results:

service sum(used)
dpm          5995
exchange    4338
mssql           1180
oracle          0
vmware  57555

2) index=solidfire service host=sf.ny2.hcmny.com | bucket span=1m _time | stats latest(used) as used by service | stats sum(used) by service
results:

service sum(used)
dpm         1662
exchange    804
mssql           1
oracle          7516
vmware           277

[somesoni2]

Give this a try

index=solidfire service host=sf.ny2.hcmny.com  | eventstats max(_time) as max by service | where _time=max  | stats sum(used) by service

[kamgineer]

that did it! thank you again!