How to create a search to find the existence of a hostname in one index based on a list of hosts from a different index?

[dbcase]

Hi,

I have one index that I've extracted a list of hostnames from. The search looks like this

index=support source=*splunkLogs*|rex "(apache|weblogic)\/(?<hosts>\w+)"|dedup hosts|table hosts|sort hosts

The search provides a list of hosts like this

cox_cpe1
cox_cpe2
cox_cpe3
cox_cpe4
cox_cpe5
cox_cpe6
cox_portal1
cox_portal2
cox_portal3
cox_portal4

etc etc....

I then need to search through another index to make sure that hostname and sourcetype (sourcetype will be 1 of 2 values either access_combined or wls_managedserver) exists by day.

For example if cox_cpe1 with a sourcetype of access_combined exists for October 23rd then move on.... If it doesn't then capture that and proceed on to the next host from the list.

The first search was easy enough, not sure where to begin on the second one.

[somesoni2 · Accepted solution]

If the number of searches doesn't change that frequently (like hourly), they I would setup first search as daily scheduled saved search to save the list of servers in a lookup table file. I would then use this lookup table in second search so to avoid querying first index every time.

Search !: Scheduled to run daily, looking back last 24 Hr OR yesterday time range.

index=support source=*splunkLogs*|rex "(apache|weblogic)\/(?<host>\w+)"|stats count by host|table host | inputlookup host_lookup.csv append=t | dedup host |outputlookup host_lookup.csv

Updated
Search 2: Check if the hosts from lookup are reporting with those two sourcetypes, for specific time range

index=yourIndex sourcetype=access_combined or sourcetype=wls_managedserver [| inputlookup host_lookup.csv | table host ]
| chart count over host by sourcetype | inputlookup host_lookup.csv append=t | stats values(*) as * by host

To find host which have not reported, just add | where isnull(access_combined) OR isnull(wls_managedserver) to the end.

[dbcase]

Hi Somesoni2,

Hmmmm, odd..... I don't get any results from Search 2 with or without the isnull.

[dbcase]

hang on found one syntax error (lowercase or) now testing 🙂

[somesoni2]

Poomp... I also made a type in last stats (used hosts instead of host). Fixed.

[dbcase]

tks!!!! Works like a charm 🙂

One day I hope to be as smart as you guys!