Splunk Search

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

Path Finder

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.

Labels (5)
0 Karma


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


If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...