Splunk Search

How can I do a cumulative timechart with dedup?

ITSX
Explorer

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.

0 Karma

aljohnson_splun
Splunk Employee
Splunk Employee

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.

ITSX
Explorer

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!

aljohnson_splun
Splunk Employee
Splunk Employee

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 ... ?

0 Karma

aljohnson_splun
Splunk Employee
Splunk Employee

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

ITSX
Explorer
|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!

0 Karma

ITSX
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...