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
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
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
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
The join
approach would run the search twice, so it's not a great idea if avoidable (it is).
It is also subject to subsearch limits; my solution is not.