Splunk Search

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

kamgineer
Explorer

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:
alt text

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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
Explorer

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

somesoni2
SplunkTrust
SplunkTrust

For Answer 2, which query did you try?

0 Karma

kamgineer
Explorer

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

somesoni2
SplunkTrust
SplunkTrust

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

kamgineer
Explorer

that did it! thank you again!

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...