https://community.splunk.com/t5/Splunk-Search/how-to-table-the-hosts-missing-in-splunk-with-lookup-file-for/m-p/529176#M149412 <P>When you run that query what do you get?&nbsp; How is that different from what you expect to get?</P><P>When working with subsearches it helps to start with the subsearch by itself.&nbsp; Do that and you'll probably get an error message about the stats command not having a "span=1d" option.&nbsp; That's easily fixed by removing the option - it doesn't make sense in this context.&nbsp; Something that doesn't make sense is using the distinct_count function.&nbsp; Knowing you have 99 hosts reporting doesn't help, either.&nbsp; Expanding the subsearch results in "search NOT 99" - not very useful, is it?&nbsp; I'll let you determine if "test.log" is a valid sourcetype and "Host" and valid field in your environment.</P><P>Once you have a working subsearch, add "| format" to it.&nbsp; This will show the string that will be added to the search command.&nbsp; If the result is not valid as part of "search NOT" then the subsearch will have to be modified.</P><P>Finally (for now), the ServersNotReporting field doesn't exist so the final report will be a list of timestamps and an empty column.</P><P>Here's a suggested query&nbsp;</P><LI-CODE lang="markup">| inputlookup HostDetails.csv | fields Host country datacenter | search NOT [search index=_internal sourcetype="test.log" | dedup host | return 1000 host ] | eval Time = strftime(_time, "%Y-%d-%m") | rename host as ServersNotReporting | table Time ServersNotReporting</LI-CODE><P>&nbsp;</P> Thu, 12 Nov 2020 16:56:25 GMT richgalloway 2020-11-12T16:56:25Z https://community.splunk.com/t5/Splunk-Search/how-to-table-the-hosts-missing-in-splunk-with-lookup-file-for/m-p/529163#M149407 <P><SPAN>I've been poking around Splunk Answers for a while today and can't quite match the scenario I've got.</SPAN></P><P><SPAN>I have a 100 hosts in lookup And in splunk index, mostly reports 100 hosts but sometime few servers will miss reporting. I want to have a table with date and "ServersNotReporting"<BR /><BR />| inputlookup HostDetails.csv | table Host country datacenter&nbsp;<BR />| search NOT [search index=_internal sourcetype="test.log" | stats dc(Host) AS host&nbsp;span=1d ]<BR />| eval Time = strftime(_time, "%Y-%d-%m") | fields - _time | table Time ServersNotReporting<BR /><BR /></SPAN></P><P><SPAN>Probably my approach&nbsp;is wrong, but I don't know how to do this. Please help.. Thanks in Advance.</SPAN></P> Thu, 12 Nov 2020 16:00:03 GMT https://community.splunk.com/t5/Splunk-Search/how-to-table-the-hosts-missing-in-splunk-with-lookup-file-for/m-p/529163#M149407 vvemula 2020-11-12T16:00:03Z https://community.splunk.com/t5/Splunk-Search/how-to-table-the-hosts-missing-in-splunk-with-lookup-file-for/m-p/529176#M149412 <P>When you run that query what do you get?&nbsp; How is that different from what you expect to get?</P><P>When working with subsearches it helps to start with the subsearch by itself.&nbsp; Do that and you'll probably get an error message about the stats command not having a "span=1d" option.&nbsp; That's easily fixed by removing the option - it doesn't make sense in this context.&nbsp; Something that doesn't make sense is using the distinct_count function.&nbsp; Knowing you have 99 hosts reporting doesn't help, either.&nbsp; Expanding the subsearch results in "search NOT 99" - not very useful, is it?&nbsp; I'll let you determine if "test.log" is a valid sourcetype and "Host" and valid field in your environment.</P><P>Once you have a working subsearch, add "| format" to it.&nbsp; This will show the string that will be added to the search command.&nbsp; If the result is not valid as part of "search NOT" then the subsearch will have to be modified.</P><P>Finally (for now), the ServersNotReporting field doesn't exist so the final report will be a list of timestamps and an empty column.</P><P>Here's a suggested query&nbsp;</P><LI-CODE lang="markup">| inputlookup HostDetails.csv | fields Host country datacenter | search NOT [search index=_internal sourcetype="test.log" | dedup host | return 1000 host ] | eval Time = strftime(_time, "%Y-%d-%m") | rename host as ServersNotReporting | table Time ServersNotReporting</LI-CODE><P>&nbsp;</P> Thu, 12 Nov 2020 16:56:25 GMT https://community.splunk.com/t5/Splunk-Search/how-to-table-the-hosts-missing-in-splunk-with-lookup-file-for/m-p/529176#M149412 richgalloway 2020-11-12T16:56:25Z