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!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...