https://community.splunk.com/t5/Splunk-Search/how-to-display-a-list-of-hosts-which-satisfies-a-condition/m-p/341211#M101165 <P>Take a look at the search you started with. I'm going to truncate it after the <CODE>eval/case</CODE> statement:<BR /> <CODE>| 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&gt;=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")</CODE></P> <P>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 <CODE>case</CODE> statement should contain <CODE>"Systems reported to Splunk in last 24 hours", (recentTime0)</CODE> but should rather contain <CODE>"Systems reported to Splunk in last 24 hours", (recentTime&gt;0)</CODE>. </P> <P>So dive into the <CODE>case</CODE> statement itself. If you aren't familiar with it, here's a good place to start:<BR /> <A href="http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/ConditionalFunctions">http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/ConditionalFunctions</A></P> <P>The statement is assigning a value to a field called <CODE>category</CODE>, based on the value of the field <CODE>recentTime</CODE>. If <CODE>recentTime</CODE> is greater than the value of <CODE>relative_time(now(), "-24h")</CODE> - which means it's looking for events with a timestamp in <CODE>recentTime</CODE> that is within the last 24 hours - then the event will get <CODE>category="Systems reported to Splunk in last 24 hours"</CODE>. If <CODE>recentTime</CODE> 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 <CODE>recentTime&gt;0</CODE>. Here it's looking for any valid (non-zero) timestamp in <CODE>recentTime</CODE>, which indicates that the system logged some events at some time. Finally, it looks for events where <CODE>recentTime=0</CODE>, which indicates that the system never checked in at all. </P> <P>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:<BR /> <CODE>| search category="Systems reported to Splunk in last 24 hours" | stats values(host) AS systems_seen_in_last_24_hours</CODE></P> <P>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:<BR /> <CODE>| 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&gt;=relative_time(now(), "-24h") | stats values(host)</CODE></P> Wed, 01 Nov 2017 20:49:51 GMT elliotproebstel 2017-11-01T20:49:51Z https://community.splunk.com/t5/Splunk-Search/how-to-display-a-list-of-hosts-which-satisfies-a-condition/m-p/341208#M101162 <P>I have a query as follows </P> <P>| 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&gt;=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" </P> <P>Which gives the result as follows </P> <P><IMG src="https://community.splunk.com/storage/temp/218659-today-pic.png" alt="alt text" /></P> <P>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 </P> <P>never_reported_systems</P> <P>kjhkj<BR /> fkjhk<BR /> vkjhk<BR /> bkljhk<BR /> nkljhk<BR /> nkjh</P> Tue, 29 Sep 2020 16:32:33 GMT https://community.splunk.com/t5/Splunk-Search/how-to-display-a-list-of-hosts-which-satisfies-a-condition/m-p/341208#M101162 pavanae 2020-09-29T16:32:33Z https://community.splunk.com/t5/Splunk-Search/how-to-display-a-list-of-hosts-which-satisfies-a-condition/m-p/341209#M101163 <P>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:<BR /> <CODE>| 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</CODE></P> Wed, 01 Nov 2017 15:50:07 GMT https://community.splunk.com/t5/Splunk-Search/how-to-display-a-list-of-hosts-which-satisfies-a-condition/m-p/341209#M101163 elliotproebstel 2017-11-01T15:50:07Z https://community.splunk.com/t5/Splunk-Search/how-to-display-a-list-of-hosts-which-satisfies-a-condition/m-p/341210#M101164 <P>thanks for the answer @elliotproebstel. what if I want to display the "Systems reported to Splunk in last 24 hours"?</P> Wed, 01 Nov 2017 18:54:01 GMT https://community.splunk.com/t5/Splunk-Search/how-to-display-a-list-of-hosts-which-satisfies-a-condition/m-p/341210#M101164 pavanae 2017-11-01T18:54:01Z https://community.splunk.com/t5/Splunk-Search/how-to-display-a-list-of-hosts-which-satisfies-a-condition/m-p/341211#M101165 <P>Take a look at the search you started with. I'm going to truncate it after the <CODE>eval/case</CODE> statement:<BR /> <CODE>| 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&gt;=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")</CODE></P> <P>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 <CODE>case</CODE> statement should contain <CODE>"Systems reported to Splunk in last 24 hours", (recentTime0)</CODE> but should rather contain <CODE>"Systems reported to Splunk in last 24 hours", (recentTime&gt;0)</CODE>. </P> <P>So dive into the <CODE>case</CODE> statement itself. If you aren't familiar with it, here's a good place to start:<BR /> <A href="http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/ConditionalFunctions">http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/ConditionalFunctions</A></P> <P>The statement is assigning a value to a field called <CODE>category</CODE>, based on the value of the field <CODE>recentTime</CODE>. If <CODE>recentTime</CODE> is greater than the value of <CODE>relative_time(now(), "-24h")</CODE> - which means it's looking for events with a timestamp in <CODE>recentTime</CODE> that is within the last 24 hours - then the event will get <CODE>category="Systems reported to Splunk in last 24 hours"</CODE>. If <CODE>recentTime</CODE> 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 <CODE>recentTime&gt;0</CODE>. Here it's looking for any valid (non-zero) timestamp in <CODE>recentTime</CODE>, which indicates that the system logged some events at some time. Finally, it looks for events where <CODE>recentTime=0</CODE>, which indicates that the system never checked in at all. </P> <P>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:<BR /> <CODE>| search category="Systems reported to Splunk in last 24 hours" | stats values(host) AS systems_seen_in_last_24_hours</CODE></P> <P>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:<BR /> <CODE>| 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&gt;=relative_time(now(), "-24h") | stats values(host)</CODE></P> Wed, 01 Nov 2017 20:49:51 GMT https://community.splunk.com/t5/Splunk-Search/how-to-display-a-list-of-hosts-which-satisfies-a-condition/m-p/341211#M101165 elliotproebstel 2017-11-01T20:49:51Z