Splunk Search

Conditional count in stats function

willial
Communicator

I need to do the following:

Get a distinct count of serial numbers where a selected date falls within a particular range. Figuring out if a serial number is valid per table row is easy:

eval inService=if(enteredService<beginTime AND (isnull(leftService) OR leftService>beginTime),1,0)

The problem is I want to sum up all of these 1s but only once for each distinct serial number (a serial number can appear in the table multiple times).

Currently my stats line looks like this:

stats sum(quarterPortion) AS sumQuarter dc(serialNumber) AS countSerNum by modelClass

I'm trying to get a ratio between sumQuarter (the amount something is used) and countSerNum (the number of things available for use). This is set up so that I can look at any historical quarter for sumQuarter, but sadly dc(serialNumber) only gives me the current count, not the historical count for the selected timeframe.

This is coming from a database query, so I can't just restrict the time range ahead of the search. I want to do something like this:

dc(eval if(enteredService<beginTime AND (isnull(leftService) OR leftService>beginTime),serNum))

Obviously this doesn't exactly work.

Tags (3)
1 Solution

somesoni2
SplunkTrust
SplunkTrust

May be something like this...

your base search | eval inService=if(enteredService<beginTime AND (isnull(leftService) OR leftService>beginTime),1,0) | stats sum(quarterPortion) AS sumQuarter dc(serialNumber) AS countSerNum by modelClass,inService |eval countSerNum=countSerNum*inService | stats sum(sumQuarter) as sumQuarter max(countSerNum) as countSerNum by modelClass

OR

your base search | eval serialNumber=if(enteredService<beginTime AND (isnull(leftService) OR leftService>beginTime),serialNumber,null())| stats sum(quarterPortion) AS sumQuarter dc(serialNumber) AS countSerNum by modelClass

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

May be something like this...

your base search | eval inService=if(enteredService<beginTime AND (isnull(leftService) OR leftService>beginTime),1,0) | stats sum(quarterPortion) AS sumQuarter dc(serialNumber) AS countSerNum by modelClass,inService |eval countSerNum=countSerNum*inService | stats sum(sumQuarter) as sumQuarter max(countSerNum) as countSerNum by modelClass

OR

your base search | eval serialNumber=if(enteredService<beginTime AND (isnull(leftService) OR leftService>beginTime),serialNumber,null())| stats sum(quarterPortion) AS sumQuarter dc(serialNumber) AS countSerNum by modelClass

willial
Communicator

I think #2 is working properly (as far as I can tell). I'll revisit this if I discover I'm wrong. Otherwise, high marks for cleverness.

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