how to table the hosts missing in splunk with lookup file for span=1d and for 7 days

[vvemula]

I've been poking around Splunk Answers for a while today and can't quite match the scenario I've got.

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"

| inputlookup HostDetails.csv | table Host country datacenter 
| search NOT [search index=_internal sourcetype="test.log" | stats dc(Host) AS host span=1d ]
| eval Time = strftime(_time, "%Y-%d-%m") | fields - _time | table Time ServersNotReporting

Probably my approach is wrong, but I don't know how to do this. Please help.. Thanks in Advance.

[richgalloway]

When you run that query what do you get?  How is that different from what you expect to get?

When working with subsearches it helps to start with the subsearch by itself.  Do that and you'll probably get an error message about the stats command not having a "span=1d" option.  That's easily fixed by removing the option - it doesn't make sense in this context.  Something that doesn't make sense is using the distinct_count function.  Knowing you have 99 hosts reporting doesn't help, either.  Expanding the subsearch results in "search NOT 99" - not very useful, is it?  I'll let you determine if "test.log" is a valid sourcetype and "Host" and valid field in your environment.

Once you have a working subsearch, add "| format" to it.  This will show the string that will be added to the search command.  If the result is not valid as part of "search NOT" then the subsearch will have to be modified.

Finally (for now), the ServersNotReporting field doesn't exist so the final report will be a list of timestamps and an empty column.

Here's a suggested query 

| 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