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

[aparnaa]

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

[somesoni2]

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"

OR

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

[ngox0061]

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"

[DMohn · Accepted solution]

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"

[danataylor]

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

[aparnaa]

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