topic Re: Using a lookup file in a subsearch in Splunk Search

Using a lookup file in a subsearch

Makinde — Tue, 29 Sep 2020 09:05:44 GMT

I have an original search to identify some vulnerabilities in my network, one of the fields in the search string is the Server_name field, however I want it to pull that information from my lookup file, so I am going to have to do a search in a search.

One challenge I have is my server names in Splunk are the FQDN but the server_name in my lookup file is just the server name not the FQDN so for me to get a match I need to use a wildcard (*) i.e. Server_name in Splunk is WLTYZ.domain.com while the server name in the lookup file is WLTYZ but I need my search string to match WLTYZ.domain.com in the search results when it uses the result WLTYZ from the lookup file.

I am thinking of putting the wildcard before and after the lookup search string so my search string looks like this;

index=main host_name=*[| inputlookup UCMDB.csv where MD="Ken Bell" | table "Server Name"]* | dedup host_name, qid | stats count by host_name

Do you think this will work? If not what would you recommend?

Re: Using a lookup file in a subsearch

martin_mueller — Sun, 13 Mar 2016 17:13:36 GMT

That's not how filtering by subsearch results work. Try this:

index=main [inputlookup UCMDB.csv where MD="Ken Bell" | rename "Server Name" as host_name | fields host_name | eval host_name = host_name."*"] | stats dc(qid) by host_name

Note, I've only added the asterisk to the end because of how you described the FQDN difference - wildcards at the beginning of search terms are terribly inefficient. I've also replaced the dedup | stats count with a stats dc(), should do the same thing but faster.

Re: Using a lookup file in a subsearch

Makinde — Tue, 29 Sep 2020 09:05:48 GMT

Hi Martin,

I tried the search string you suggested, it wasn't working so I started troubleshooting
First let's start with FQDN with no difference so I removed the "*" just to simplify it all.
First I was able to confirm inputlookup UCMDB.csv where MD="Ken Bell" | rename "Server Name" as host_name | fields host_name works fine, but when I put it all in the search it isn't working.
I confirmed the hostname returneddoes exist in the index so logically it should work however I can't see any results when I run the search.
Any ideas?

Re: Using a lookup file in a subsearch

Makinde — Tue, 29 Sep 2020 09:05:50 GMT

After further troubleshooting, I noticed "| inputlookup UCMDB.csv where MD="Ken Bell" | rename "Server Name" as host_name | fields host_name" works fine but when I add the "| eval host_name = host_name" section the search returns no values in the fields.
I think the problem is with the eval function. Any ideas?

Re: Using a lookup file in a subsearch

martin_mueller — Sun, 13 Mar 2016 21:25:19 GMT

To troubleshoot, split the search into two parts. First, run this:

| inputlookup UCMDB.csv where MD="Ken Bell" | rename "Server Name" as host_name | fields host_name | eval host_name = host_name."*" | format

The result should be a list of host_name="foo*" filters concatenated with a bunch of parentheses and ORs. If that list looks okay, copy it into this:

index=main PASTEHERE | stats dc(qid) by host_name