https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331040#M98541 <P>Hmm...I could imagine this being caused by a case mis-match (lower-case vs. upper-case) across entries in the lookup table and values in the log entries. Let's try a revision with hosts converted all to lower-case. I'll update the original post with a conversion case.</P> Mon, 23 Oct 2017 20:21:58 GMT elliotproebstel 2017-10-23T20:21:58Z https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331028#M98529 <P>I have a query as follows to display the list of hosts which are seen in last 24 hours and hosts which are not seen in last 24 hours from a list of lookup table hosts. which is working fine. But I also want to see the list of hosts which are neither seen in last 24 hours nor not seen in 24 hours. I mean the hosts which are never in splunk </P> <P>The following is my query </P> <P>| metadata type=hosts | search [| inputlookup hosts_test.csv | search environment="PROD" | rename my_hostname as host | eval host=lower(host) | eval recentTime=0<BR /> | table host] <BR /> | eval lastTime=coalesce(lastTime,0)<BR /> | eval timeDiff=now()-lastTime<BR /> | eval last_seen_in_24_hours=case(timeDiff==NULL, "never in Splunk", timeDiff&gt;86400,"Systems not reported to Splunk from last 24 hours", 1==1,"Systems reported to Splunk in last 24 hours")<BR /> | eval lastReported=if(lastTime=0,"never",strftime(lastTime,"%F %T")) </P> <P>Following is the result :-</P> <P><IMG src="https://community.splunk.com/storage/temp/219577-hosts.png" alt="alt text" /></P> <P>Now I also want to see the remaining hosts which are never in splunk as "never in splunk". I'm trying to display the number for never seen hosts in the report along with last seen in 24 hours and not seen in 24 hours like below </P> <P>last_seen_in_24_hours<BR /> systems not reported to splunk in last 24 hours 43<BR /> systems reported to splunk in last 24 hours 768<BR /> systems never reported to splunk 76</P> Tue, 29 Sep 2020 16:27:09 GMT https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331028#M98529 pavanae 2020-09-29T16:27:09Z https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331029#M98530 <P>I think this should do it:</P> <P><CODE>| metadata type=hosts | search [| inputlookup mssp_dashboard_hosts_test.csv | search hpam_environment="PROD" | eval host=lower(my_hostname) | fields host ] | append [| inputlookup mssp_dashboard_hosts_test.csv | search hpam_environment="PROD" | eval host=lower(my_hostname) | eval recentTime=0, lastTime=0 | fields host recentTime lastTime ] | dedup host | eval category=case(recentTime&gt;=relative_time(now(), "-24h"), "Systems reported to Splunk in last 24 hours", (recentTime&lt;relative_time(now(), "-24h") AND recentTime&gt;0), "Systems reported to Splunk more than 24 hours ago", recentTime=0, "Systems never reported to Splunk") | stats dc(host) as "Total Hosts" BY category</CODE></P> <P>Note that your approach with applying "lastTime=0" in the inputlookup portion of your original search was having no effect - by feeding the data from the inputlookup directly into a search, Splunk was discarding all the hosts that weren't found. I added a second inputlookup inside an append command, and then I applied <CODE>dedup</CODE> to remove the hosts that had been found.</P> <P>If there is any chance that the hostnames in your csv file have different capitalization than the values in the log entries, this conversion might be necessary:</P> <P><CODE>| metadata type=hosts | search [| inputlookup mssp_dashboard_hosts_test.csv | search hpam_environment="PROD" | eval host=lower(my_hostname) | fields host ] | eval host=lower(host) | append [| inputlookup mssp_dashboard_hosts_test.csv | search hpam_environment="PROD" | 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", (recentTime&lt;relative_time(now(), "-24h") AND recentTime&gt;0), "Systems reported to Splunk more than 24 hours ago", recentTime=0, "Systems never reported to Splunk") | stats dc(host) as "Total Hosts" BY category</CODE></P> Mon, 23 Oct 2017 18:58:04 GMT https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331029#M98530 elliotproebstel 2017-10-23T18:58:04Z https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331030#M98531 <P>I meant to mention: it might be helpful to consult the docs for metadata to determine if you really want to use <CODE>lastTime</CODE> or if <CODE>recentTime</CODE> is more appropriate for your use case:<BR /> <A href="http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/Metadata">http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/Metadata</A></P> <P>My search uses recentTime, because that references the last <CODE>indextime</CODE> of an event from the particular host, which seemed most useful for determining when a host actually last contacted Splunk. But if <CODE>lastTime</CODE> really is best for your use case, replace <CODE>recentTime</CODE> with <CODE>lastTime</CODE> in the last portion of my code above.</P> Mon, 23 Oct 2017 19:00:43 GMT https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331030#M98531 elliotproebstel 2017-10-23T19:00:43Z https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331031#M98532 <P>Thanks for the quick response @elliotproebstel. I still see the result displays only 2 field values and no results for "Systems never reported to splunk" looks like Splunk was still discarding all the hosts that weren't found.</P> Mon, 23 Oct 2017 19:03:04 GMT https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331031#M98532 pavanae 2017-10-23T19:03:04Z https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331032#M98533 <P>Oops! Yes, there is a cut and paste error in my code. I'm going to fix it above - but the issue is the extra pipe after <CODE>append</CODE>. Sorry.</P> Mon, 23 Oct 2017 19:10:35 GMT https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331032#M98533 elliotproebstel 2017-10-23T19:10:35Z https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331033#M98534 <P>No issues. I corrected that. Please find my updated responce. :).</P> Mon, 23 Oct 2017 19:13:29 GMT https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331033#M98534 pavanae 2017-10-23T19:13:29Z https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331034#M98535 <P>Ahhh, I'm sorry for all the back and forth. I forgot to apply the conversion on the second <CODE>inputlookup</CODE>. Will fix now.</P> Mon, 23 Oct 2017 19:25:44 GMT https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331034#M98535 elliotproebstel 2017-10-23T19:25:44Z https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331035#M98536 <P>Great. Thanks a lot for your time. <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Mon, 23 Oct 2017 19:27:04 GMT https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331035#M98536 pavanae 2017-10-23T19:27:04Z https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331036#M98537 <P>By not converting <CODE>my_hostname</CODE> to <CODE>host</CODE> in the subsearch, the <CODE>inputlookup</CODE> was appending nothing to the parent search. Sorry! Should be fixed now.</P> Mon, 23 Oct 2017 19:27:14 GMT https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331036#M98537 elliotproebstel 2017-10-23T19:27:14Z https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331037#M98538 <P>Hi @elliotproebstel still no change. Only 2 values are displaying and no "Systems never reported to Splunk"</P> Mon, 23 Oct 2017 19:38:19 GMT https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331037#M98538 pavanae 2017-10-23T19:38:19Z https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331038#M98539 <P>Hmm...Try this code and see if there are any hosts with <CODE>recentTime=0</CODE> and <CODE>lastTime=0</CODE>: </P> <P><CODE>| metadata type=hosts | search [| inputlookup mssp_dashboard_hosts_test.csv | search hpam_environment="PROD" | eval host=lower(my_hostname) | fields host ] | append [| inputlookup mssp_dashboard_hosts_test.csv | search hpam_environment="PROD" | eval host=lower(my_hostname) | eval recentTime=0, lastTime=0 | fields host recentTime lastTime ] | dedup host</CODE></P> <P>If not, are you certain there are any hosts in mssp_dashboard_hosts_test.csv with <CODE>hpam_environment="PROD"</CODE> that have never reported to Splunk?</P> Tue, 29 Sep 2020 16:27:14 GMT https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331038#M98539 elliotproebstel 2020-09-29T16:27:14Z https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331039#M98540 <P>Hi @elliotproebstel It worked and displayed the results as follows </P> <P>category Total Hosts<BR /> Systems never reported to Splunk 402<BR /> Systems reported to Splunk in last 24 hours 966<BR /> Systems reported to Splunk more than 24 hours ago 21</P> <P>But the total number of hosts in the lookup is 1066. which means the Systems never reported to Splunk shoul be 1066-(966+21)=79. But here the 79 count has been displayed as 402 looks like something wrong with the calculation.</P> Mon, 23 Oct 2017 20:09:03 GMT https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331039#M98540 pavanae 2017-10-23T20:09:03Z https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331040#M98541 <P>Hmm...I could imagine this being caused by a case mis-match (lower-case vs. upper-case) across entries in the lookup table and values in the log entries. Let's try a revision with hosts converted all to lower-case. I'll update the original post with a conversion case.</P> Mon, 23 Oct 2017 20:21:58 GMT https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331040#M98541 elliotproebstel 2017-10-23T20:21:58Z https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331041#M98542 <P>great worked now. Thank you </P> Mon, 23 Oct 2017 20:56:19 GMT https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331041#M98542 pavanae 2017-10-23T20:56:19Z https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331042#M98543 <P>Glad I could help!</P> Mon, 23 Oct 2017 20:59:02 GMT https://community.splunk.com/t5/Splunk-Search/how-to-find-the-number-of-hosts-that-never-reported-to-splunk/m-p/331042#M98543 elliotproebstel 2017-10-23T20:59:02Z