topic Re: How can I do a cumulative timechart with dedup? in Splunk Search

How can I do a cumulative timechart with dedup?

ITSX — Thu, 14 Jan 2016 21:56:43 GMT

I've got some data with three applicable fields, hostname, requirement, and requirementstatus. Each day I may receive hundreds of log for a single hostname, or none at all. a stream of logs might look like

>hostname="comp1" requirement=foo requirementstatus=failed
>hostname="comp1" requirement=bar requirementstatus=passed
>hostname="comp1" requirement=foo requirementstatus=passed
>hostname="comp1" requirement=bar requirementstatus=passed

I can get the current requirementstatus for each requirement with

>|dedup 1 hostname,requirement sortby - _time|table hostname,requirement,requirementstatus

but how do i get the current, cumulative status per day in a timechart?

So on day one, I have 50 hostnames with a requirement "foo" and 25 have a requirementstatus of "passed" and 25 "failed"
Day two, 10 hostnames that previously had a requirementstatus of "failed" are now "passed", and they're the only logs received on day two

my chart when filtered to requirement "foo" should show day1 with 25 passes and 25 fails, day 2 with 35 passes and 15 fails.

I appreciate any insight you can give.

Re: How can I do a cumulative timechart with dedup?

ITSX — Thu, 14 Jan 2016 22:29:32 GMT

I guess what i'm trying to do is have my timechart bins be day1, day1+2, day1+2+3, etc., if that makes any sense.

Re: How can I do a cumulative timechart with dedup?

aljohnson_splun — Thu, 14 Jan 2016 23:05:15 GMT

Here is one way to do one requirement:

requirement=foo
| timechart 
count(eval(requirementstatus="passed")) as passes, 
count(eval(requirementstatus="failed")) as fails
by hostname
| streamstats sum(passes) as cumsum_passes, sum(fails) as cumsum_fails

It sounds like you are mostly looking for the streamstats command.

Re: How can I do a cumulative timechart with dedup?

ITSX — Thu, 14 Jan 2016 23:44:48 GMT

|bucket span=1d _time
 |chart 
 count(eval(requirementstatus="passed")) as passesPerPeriod, 
 count(eval(requirementstatus="failed")) as failsperPeriod
 by _time
 | streamstats sum(passesPerPeriod) as CumulativePassed, sum(failsperPeriod) as CumulativeFailed

works splendidly. Thank you!

Re: How can I do a cumulative timechart with dedup?

ITSX — Fri, 15 Jan 2016 00:04:58 GMT

Well, I thought it worked, but it's not able to decrease count. So if a fail later becomes a pass, it will +1 the pass, but the fail count stays the same, due to losing the

|dedup 1 hostname,requirement sortby - _time

functionailty. but I think this might also be solvable with streamstats. I'll dig further in. Thanks again!

Re: How can I do a cumulative timechart with dedup?

aljohnson_splun — Fri, 15 Jan 2016 02:23:10 GMT

You can do some tricky work probably with case to make the success equal to 1, and the fail equal to -1 - then you can use streamstats sum to add it up and have the failures decrease the total.

|bucket span=1d _time
|chart 
count(eval(requirementstatus="passed")) as passesPerPeriod, 
count(eval(requirementstatus="failed")) as failsperPeriod
| eval requirementstatus_count = case(
requirementstatus="passed", 1, 
requirementstatus="failed", -1)
| streamstats 
sum(passesPerPeriod) as CumulativePassed
sum(failsperPeriod) as CumulativeFailed
sum(requirementstatus_count) as period_balanced_total

Or something of the sort ... ?

Re: How can I do a cumulative timechart with dedup?

aljohnson_splun — Fri, 15 Jan 2016 02:24:48 GMT

Or if you need the period balanced total by time-bucket

 |bucket span=1d _time
 |chart 
 count(eval(requirementstatus="passed")) as passesPerPeriod, 
 count(eval(requirementstatus="failed")) as failsperPeriod
 | eval requirementstatus_count = case(
 requirementstatus="passed", 1, 
 requirementstatus="failed", -1)
 | streamstats 
 sum(passesPerPeriod) as CumulativePassed
 sum(failsperPeriod) as CumulativeFailed
 | eventstats sum(requirementstatus_count) as period_balanced_total by _time