how to display a list of hosts which satisfies a condition?

[pavanae]

I have a query as follows

| metadata type=hosts | search [| inputlookup ABCD.csv | eval Device=mvindex(split(Device,"."),0) | search NOT "Device Type"="alys*" | rename "Device" as my_hostname | eval host=lower(my_hostname) | fields host ] | eval host=lower(host) | append [| inputlookup ABCD.csv | eval Device=mvindex(split(Device,"."),0) | search NOT "Device Type"="alys*"| rename "Device" as my_hostname | eval host=lower(my_hostname) | eval recentTime=0, lastTime=0, host=lower(host) | fields host recentTime lastTime ] | dedup host | eval category=case(recentTime>=relative_time(now(), "-24h"), "Systems reported to Splunk in last 24 hours", (recentTime0), "Systems reported to Splunk more than 24 hours ago", recentTime=0, "Systems never reported to Splunk") | stats dc(host) AS total_hosts BY category | addcoltotals labelfield=category label="Total" | eventstats max(total_hosts) AS all_totals | search NOT category="Total" | eval Percentage=tostring(round(total_hosts/all_totals*100,2))."%" | fields category total_hosts Percentage | rename total_hosts as "Host Count"

Which gives the result as follows

Now instead of this. I want to modify my query to display only the list of hosts which are never reported to Splunk. It appears to be simple but when i tried to add the | search where category="Systems never reported to Splunk" .its not giving me any results. It would be great if anyone can help me to modify the query to display the results like below

never_reported_systems

kjhkj
fkjhk
vkjhk
bkljhk
nkljhk
nkjh

[elliotproebstel · Accepted solution]

Well, if you have just been adding a search clause to the end of the existing search, you will never get hostnames out, because the earlier transforming commands have discarded them. There's probably some optimization you could do, but this will likely suffice for a start:
| metadata type=hosts | search [| inputlookup ABCD.csv | eval Device=mvindex(split(Device,"."),0) | search NOT "Device Type"="alys*" | rename "Device" as my_hostname | eval host=lower(my_hostname) | fields host ] | eval host=lower(host) | append [| inputlookup ABCD.csv | eval Device=mvindex(split(Device,"."),0) | search NOT "Device Type"="alys*"| rename "Device" as my_hostname | eval host=lower(my_hostname) | eval recentTime=0, host=lower(host) | fields host recentTime ] | dedup host | where recentTime=0 | stats values(host) AS never_reported_systems

[pavanae]

thanks for the answer @elliotproebstel. what if I want to display the "Systems reported to Splunk in last 24 hours"?

[elliotproebstel]

Take a look at the search you started with. I'm going to truncate it after the eval/case statement:
| metadata type=hosts | search [| inputlookup ABCD.csv | eval Device=mvindex(split(Device,"."),0) | search NOT "Device Type"="alys*" | rename "Device" as my_hostname | eval host=lower(my_hostname) | fields host ] | eval host=lower(host) | append [| inputlookup ABCD.csv | eval Device=mvindex(split(Device,"."),0) | search NOT "Device Type"="alys*"| rename "Device" as my_hostname | eval host=lower(my_hostname) | eval recentTime=0, lastTime=0, host=lower(host) | fields host recentTime lastTime ] | dedup host | eval category=case(recentTime>=relative_time(now(), "-24h"), "Systems reported to Splunk in last 24 hours", (recentTime0), "Systems reported to Splunk more than 24 hours ago", recentTime=0, "Systems never reported to Splunk")

I copied that from your post. First, I'm going to point out that I believe there is a typo here - I don't think the case statement should contain "Systems reported to Splunk in last 24 hours", (recentTime0) but should rather contain "Systems reported to Splunk in last 24 hours", (recentTime>0).

So dive into the case statement itself. If you aren't familiar with it, here's a good place to start:
http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/ConditionalFunctions

The statement is assigning a value to a field called category, based on the value of the field recentTime. If recentTime is greater than the value of relative_time(now(), "-24h") - which means it's looking for events with a timestamp in recentTime that is within the last 24 hours - then the event will get category="Systems reported to Splunk in last 24 hours". If recentTime isn't within the last 24 hours, then it will advance to the next option in the case statement - one that checks to see if recentTime>0. Here it's looking for any valid (non-zero) timestamp in recentTime, which indicates that the system logged some events at some time. Finally, it looks for events where recentTime=0, which indicates that the system never checked in at all.

So if you use this truncated version of your search and then just filter the events by category, you can output any of the categories you please:
| search category="Systems reported to Splunk in last 24 hours" | stats values(host) AS systems_seen_in_last_24_hours

Moreover, if you are not looking to print the table for which you originally generated this query, you could do away with the category assignment entirely and narrow your search to this:
| metadata type=hosts | search [| inputlookup ABCD.csv | eval Device=mvindex(split(Device,"."),0) | search NOT "Device Type"="alys*" | rename "Device" as my_hostname | eval host=lower(my_hostname) | fields host ] | eval host=lower(host) | append [| inputlookup ABCD.csv | eval Device=mvindex(split(Device,"."),0) | search NOT "Device Type"="alys*"| rename "Device" as my_hostname | eval host=lower(my_hostname) | eval recentTime=0, lastTime=0, host=lower(host) | fields host recentTime lastTime ] | dedup host | where recentTime>=relative_time(now(), "-24h") | stats values(host)