https://community.splunk.com/t5/Splunk-Search/How-to-search-the-distinct-count-of-a-field-based-on-a-condition/m-p/271453#M81713 <P>It is also subject to subsearch limits; my solution is not.</P> Tue, 13 Oct 2015 19:18:53 GMT woodcock 2015-10-13T19:18:53Z https://community.splunk.com/t5/Splunk-Search/How-to-search-the-distinct-count-of-a-field-based-on-a-condition/m-p/271449#M81709 <P>Here is an example of my search:</P> <PRE><CODE>Search | stats dc(eval(DISPLAYNAME)) AS Total,dc(eval(STATUS="Offline")) AS Offline| eval Percentage=round((100*(Total-Offline)/Total))."%" | fields - Total, Offline </CODE></PRE> <P>My goal is to take the total number of devices as referenced by the <CODE>dc(DISPLAYNAME)</CODE>. Then, I'd like to take the same number, but under the condition that the <STRONG>STATUS</STRONG> field is equal to "Offline". When I do the count, I get a correct number until the next data import, then it duplicates as it should. Distinct count only returns a "1". I guess I'm not quite sure how to eval the <STRONG>DISPLAYNAME</STRONG> field again, but only for offline devices.</P> <P>Any help would be appreciated.</P> <P>Thanks</P> Tue, 13 Oct 2015 18:03:32 GMT https://community.splunk.com/t5/Splunk-Search/How-to-search-the-distinct-count-of-a-field-based-on-a-condition/m-p/271449#M81709 jawebb 2015-10-13T18:03:32Z https://community.splunk.com/t5/Splunk-Search/How-to-search-the-distinct-count-of-a-field-based-on-a-condition/m-p/271450#M81710 <P>Like this:</P> <PRE><CODE>| eval DISPLAYNAMEforOFFLINES = if((STATUS="Offline"), DISPLAYNAME, null()) | stats dc(eval(DISPLAYNAME)) AS Total dc(DISPLAYNAMEforOFFLINES) AS Offline| eval Percentage=round((100*(Total-Offline)/Total))."%" | fields - Total, Offline </CODE></PRE> Tue, 13 Oct 2015 18:40:24 GMT https://community.splunk.com/t5/Splunk-Search/How-to-search-the-distinct-count-of-a-field-based-on-a-condition/m-p/271450#M81710 woodcock 2015-10-13T18:40:24Z https://community.splunk.com/t5/Splunk-Search/How-to-search-the-distinct-count-of-a-field-based-on-a-condition/m-p/271451#M81711 <P>Thank you! Works great. I also found that the join command worked for me as well, but I prefer your way.</P> <P>Search | stats dc(eval(DISPLAYNAME)) AS Total| join [search "index" (STATUS=Offline) | stats dc(eval(DISPLAYNAME)) AS Offline] | eval Percentage=round((100*(Total-Offline)/Total))."%" | fields - Total, Offline</P> Tue, 13 Oct 2015 18:43:56 GMT https://community.splunk.com/t5/Splunk-Search/How-to-search-the-distinct-count-of-a-field-based-on-a-condition/m-p/271451#M81711 jawebb 2015-10-13T18:43:56Z https://community.splunk.com/t5/Splunk-Search/How-to-search-the-distinct-count-of-a-field-based-on-a-condition/m-p/271452#M81712 <P>The <CODE>join</CODE> approach would run the search twice, so it's not a great idea if avoidable (it is).</P> Tue, 13 Oct 2015 18:46:42 GMT https://community.splunk.com/t5/Splunk-Search/How-to-search-the-distinct-count-of-a-field-based-on-a-condition/m-p/271452#M81712 martin_mueller 2015-10-13T18:46:42Z https://community.splunk.com/t5/Splunk-Search/How-to-search-the-distinct-count-of-a-field-based-on-a-condition/m-p/271453#M81713 <P>It is also subject to subsearch limits; my solution is not.</P> Tue, 13 Oct 2015 19:18:53 GMT https://community.splunk.com/t5/Splunk-Search/How-to-search-the-distinct-count-of-a-field-based-on-a-condition/m-p/271453#M81713 woodcock 2015-10-13T19:18:53Z