topic Re: How to create a table which matches a lookup file and fields created at search time? in Splunk Search

How to create a table which matches a lookup file and fields created at search time?

aparnaa — Wed, 02 Nov 2016 16:26:37 GMT

Hi All

I am trying to create a search which will give me an output similar to below

Index   Server Name Application Name
Web   Server 1          ABC
app      Server 3           HUG
DB       Server 4           SMILE

In the above table: Server Name, Application Name are from a lookup file named inventory.csv
We have also created a lookup named inventorys

Only values matching host and Server Name must be displayed in the table,
I am looking for unique listing only

host and index are fields created during search time

I tried many commands but i am not able to find a search that will correlate the "Server Name"in the lookup files with host in the event

I think it should be a simple search but since I am new to Splunk, i am not able to find the answer

Thank you for helping

Regards
aparna

Re: How to create a table which matches a lookup file and fields created at search time?

DMohn — Wed, 02 Nov 2016 17:00:13 GMT

Assuming the index and host fields come from your base search, and Server Name and Application Name are from your lookup file, where host and Server Name should correlate, your search will look like this:

 <base search> |  lookup inventory.csv "Server Name" as host OUTPUT "Application Name" | table index host "Application Name" | rename host as "Server Name"

Re: How to create a table which matches a lookup file and fields created at search time?

somesoni2 — Wed, 02 Nov 2016 17:01:11 GMT

Try like this (assuming host and index are Splunk's default metadata fields)

| tstats count WHERE index=* by index host| table index host | lookup inventory.csv "Server Name" as host OUTPUT "Application Name" | where isnotnull('Application Name') | rename index as Index host as "Server Name"

| tstats count WHERE index=* [| inputlookup inventory.csv | table "Server Name" | rename "Server Name" as host] by index host | table index host | lookup inventory.csv "Server Name" as host OUTPUT "Application Name" | where isnotnull('Application Name') | rename index as Index host as "Server Name"

Re: How to create a table which matches a lookup file and fields created at search time?

aparnaa — Thu, 03 Nov 2016 07:08:48 GMT

thank you so much !
It worked exactly the way i wanted
I added dedup command to remove duplicate values

Re: How to create a table which matches a lookup file and fields created at search time?

danataylor — Mon, 28 Aug 2017 16:35:51 GMT

I've been attempting to implement this functionality for days. This finally helped me get it working. Thank you!

Re: How to create a table which matches a lookup file and fields created at search time?

ngox0061 — Wed, 23 Sep 2020 18:50:15 GMT

I'm new to Splunk and was wondering about the same thing. on the context below, is that the beginning of the search string? usually it starts with index=..... So what i'm trying to get is a lookup of

index=_internal* log_level=WARN OR log_level=ERR host=XPxx9* OR host=GPxx7* OR host=fsr*

but instead of listing like 30 of the host names with OR arguments, what's the ideal way to do it?

| tstats count WHERE index=* by index host| table index host | lookup inventory.csv "Server Name" as host OUTPUT "Application Name" | where isnotnull('Application Name') | rename index as Index host as "Server Name"