How to search the distinct count of a field based on a condition, then calculate percentage?

[jawebb]

Here is an example of my search:

Search | stats dc(eval(DISPLAYNAME)) AS Total,dc(eval(STATUS="Offline")) AS Offline| eval Percentage=round((100*(Total-Offline)/Total))."%" | fields - Total, Offline

My goal is to take the total number of devices as referenced by the dc(DISPLAYNAME). Then, I'd like to take the same number, but under the condition that the STATUS 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 DISPLAYNAME field again, but only for offline devices.

Any help would be appreciated.

Thanks

[woodcock · Accepted solution]

Like this:

| 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

[jawebb]

Thank you! Works great. I also found that the join command worked for me as well, but I prefer your way.

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

[martin_mueller]

The join approach would run the search twice, so it's not a great idea if avoidable (it is).

[woodcock]

It is also subject to subsearch limits; my solution is not.