topic Conditional count in stats function in Splunk Search

Conditional count in stats function

willial — Tue, 05 Aug 2014 17:51:52 GMT

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.

Re: Conditional count in stats function

somesoni2 — Tue, 05 Aug 2014 18:11:31 GMT

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

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

Re: Conditional count in stats function

willial — Tue, 05 Aug 2014 18:48:03 GMT

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.