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
Legend

@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
---
What goes around comes around. If it helps, hit it with Karma 🙂

View solution in original post

renjith_nair
Legend

@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
---
What goes around comes around. If it helps, hit it with Karma 🙂

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
Legend

@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")
---
What goes around comes around. If it helps, hit it with Karma 🙂
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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...