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
Revered Legend

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
Revered Legend

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!

Stay Connected: Your Guide to July Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...