Server Availability query from Incident data

[samn123]

I have a lookup table with fields Application name and host, and i have a realtime Incident data with index, sourcetype and ServerName. I have two things to be retrieved.
1. If there is a incident created with ServerName matching the host in the lookup table and the Summary field as Server down then display the result as Down
2. if there is no incident ticket created for the host then the result should always show as UP ( meaning by default the result should be UP for last 60 mins unless we have a ticket with above criteria)
below is the query which i tried but it is showing the result only when there are any relevant data in the index

| inputlookup ServerFile
| rename "host" as "ServerName"
| join type=inner ServerName
[ search index=Data sourcetype="incidents"
| eval Result=if(SHORT_DESCRIPTION like "host Down%", "DOWN","UP")
| table ServerName Result]

[woodcock]

Do not SQL when you Splunk. Forget about join entirely; when you think join type=inner, you should think | lookup:

index=*Data* sourcetype="incidents" 
| lookup ServerFile host AS ServerName
| eval Result=if(SHORT_DESCRIPTION like "host Down%", "DOWN","UP") 
| table ServerName Result

[koshyk]

Please try something like

| inputlookup ServerFile.csv 
| rename host as ServerName
| join type=left ServerName 
    [ search index=Data sourcetype="incidents" | fields SHORT_DESCRIPTION,ServerName]
| eval Result=if(SHORT_DESCRIPTION like "host Down%", "DOWN","UP") 
| table ServerName Result

[tom_frotscher]

Hi,

if you want a result for every host in your lookup, you need to change the join type to outer. This will show every entry from the main search (your inputlookup) even if it does not find a match in the subsearch (your incident data).

There is a nice image in the documentation, that shows how the types of join works: https://docs.splunk.com/Documentation/Splunk/7.2.6/SearchReference/Join#Optional_arguments

So you need to make sure to have all known hosts into your lookup table.