Using a lookup table, how do you compare a list to show results?

[shpot]

Newbie user needing some help please.

I have a list of servers in a CSV lookup file. I want to find out if every server on that list is reporting into Splunk. Using the lookup table as the master record, if the server is found, I'd like it to display a "Yes" next to the server name. If it is not found, I'd like it to say "No". I've been trying with input lookup and metadata type=hosts, but I'm stuck on the Yes/No. Thank you for your help in advance.

[renjith_nair · Accepted solution]

@shpot,

Assuming that you have the CSV file "servers.csv" which has the unique "host" field in it — if not, rename the field to host and dedup.

|inputlookup servers.csv|append [|metadata type=hosts index=_* |fields host]|stats count as _c by host
|eval Found=if(_c>1,"Yes","No")

OR

|inputlookup servers.csv|join host type=outer [|metadata type=hosts index=_*|eval Found="Yes"|fields host,Found]
|fillnull value="No" Found

[jaxjohnny2000]

I see this is a similar, but not the same. I cannot use metadata however, but only an index.

The goal is to see what fqdn values are in the CSV, but not the index.

This gives me the reverse of what i need - it gives me what's only in the index. I need what is NOT in the index, but IS in the CSV.

index=security_idx | rex field=fqdn "(?[^.]*)" | stats count by fqdn index | where NOT [inputlookup security_managed.csv | eval fqdn=lower(fqdn) | table fqdn]

This just merges the two fqdn fields together in the outputlookup, so I get all the values, not just what is missing from the index.
| inputlookup security_managed.csv | eval fqdn=lower(fqdn) | where NOT [search index=security_av | rex field=fqdn "(?[^.]*)" | eval fqdn=lower(fqdn)]

[renjith_nair]

@jaxjohnny2000 ,
Try

     index=security_idx | rex field=fqdn "(?[^.]*)" | stats count by fqdn|eval source="search"
     |append [|inputlookup security_managed.csv | eval fqdn=lower(fqdn) | fields fqdn|eval source="lookup"]
     |stats count,values(source) as source by fqdn|sort - count|eval found=if(mvcount(mvdedup(source)) > 1,"Yes","No")

[jaxjohnny2000]

ok, I see what you did. The code essentially searches both the lookup and the search giving us a value as to where it found it (search or lookup), and then places that in the source field as a multi-value field. Next it give us a "yes" value in the found field if there are more than two sources in the source field. All I have to do now, is add the | where found="yes" to the end. This is terrific, thank you.

BTW, I had a typo in the REX code.

index=security_idx
| rex field=fqdn "(?<fqdn>[^.])"
| stats count by fqdn
| eval source="search"
| append [| inputlookup security_managed.csv
| fields "DNS Name" | rename "DNS Name" as fqdn | rex field=fqdn "(?<fqdn>[^.])"
| eval fqdn=lower(fqdn)
| fields fqdn
| eval source="lookup"]
| stats count,values(source) as source by fqdn
| sort 0 - count
| eval found=if(mvcount(mvdedup(source)) > 1,"Yes","No")

[shpot]

This worked perfectly. Thank you so much!