Getting Data In

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

shpot
New Member

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.

Tags (2)
0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

@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
Happy Splunking!

View solution in original post

renjith_nair
SplunkTrust
SplunkTrust

@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
Happy Splunking!

jaxjohnny2000
Builder

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)]

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@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")
Happy Splunking!
0 Karma

jaxjohnny2000
Builder

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")

0 Karma

shpot
New Member

This worked perfectly. Thank you so much!

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 1 release of new security content via the ...

There's No Place Like Chrome and the Splunk Platform

Watch On DemandMalware. Risky Extensions. Data Exfiltration. End-users are increasingly reliant on browsers to ...

The Great Resilience Quest: 5th Leaderboard Update

The fifth leaderboard update for The Great Resilience Quest is out &gt;&gt; &#x1f3c6; Check out the ...