How to compare fields from csv and search, then add the result as a new column

[romainbouajila]

Hello,

I'm having a little trouble solving this one.
I managed to extract all hosts in Splunk in a table with events counted by path with the following search :

search index=* 
| rex field=source "(?<file_path>.*\\\)" 
| rex field=source "(?<file_path>.*\/)" 
| chart count over file_path by host limit=0 
| fields - source    

In the other hand, I have a full inventory of our network in a csv file.
I would like to be able to filter Splunk hosts by Location (this Location field is present in the full inventory csv).
I believe that I have to use the Lookup command but I can't find out how to use it. (Both the csv and the search table have a hostname field)

The Splunk query above gives a table like this :

------------+-------+-------+-------+-----+
            | Path1 | Path2 | Path3 | ... |
------------+-------+-------+-------+-----+
Host_1      |  100  |    0  |   200 | ... |
Host_2      |  250  |  1200 |    2  | ... |
Host_3      |   0   |   10  |    0  | ... |
------------+-------+-------+-------+-----+

And I would like to add a Location column with information from the CSV to look like this :

------------+--------+-------+-------+-------+-----+
            |Location| Path1 | Path2 | Path3 | ... |
------------+--------+-------+-------+-------+-----+
Host_1      |   USA  |  100  |    0  |   200 | ... |
Host_2      |   U.K  |  250  |  1200 |    2  | ... |
Host_3      |   USA  |   0   |   10  |    0  | ... |
------------+--------+-------+-------+-------+-----+

Thank you for your help !

[woodcock]

Like this:

index=* 
| rex field=source "(?<file_path>((?:.*\\\)|(?:.*\/)))"
| chart count over file_path by host limit=0 
| lookup YouLookupFile.csv Hostname AS host

[romainbouajila]

Hello woodcock, I don't get how that query would add a new "Location" column to my table
Thanks for your help

[woodcock]

Come back and try it now.

[woodcock]

I updated my answer; try it now.

[gcusello]

Hi romainbouajila,
in a chart, you usually cannot use more than two fields, but you could use a workaround, if acceptable:

index=* 
| rex field=source "(?<file_path>.*\\\)" 
| rex field=source "(?<file_path>.*\/)" 
| lookup location_lookup.csv hostname OUTPUT Location
| eval host=hostname." (".Location.")"
| chart count over file_path by host limit=0 
| fields - source 

(it's not clear for me if you have always field hostname or host, anyway the search logic is correct)
In this way you have in the first column hostname and Location.

Ciao.
Giuseppe

[romainbouajila]

Ciao Giuseppe !
Thank you very much for your help. To answer your question, "Hostname" is in my CSV and "host" in data source.
I tried to run your query and that works just fine, thank you.
Is it possible though to add a column with the location ? That would make it way more easy to use in Excel or in a dashboard

[gcusello]

Hi romainbouajila,
if you have "Hostname" in CSV and "host" in search you have only to modify the lookup command

 index=* 
 | rex field=source "(?<file_path>.*\\\)" 
 | rex field=source "(?<file_path>.*\/)" 
 | lookup location_lookup.csv Hostname As host OUTPUT Location
 | eval host=host." (".Location.")"
 | chart count over file_path by host limit=0 
 | fields - source 

To divide again the host from the Location, you should try something like this:

 index=* 
 | rex field=source "(?<file_path>.*\\\)" 
 | rex field=source "(?<file_path>.*\/)" 
 | lookup location_lookup.csv Hostname As host OUTPUT Location
 | eval host=host." (".Location.")"
 | chart count over file_path by host limit=0 
 | rex field=host "^(?<hostname>[^\(]*)\((?<Location>.*)"
 | table hostname Location path*

Ciao.
Giuseppe

[romainbouajila]

Hi Giuseppe,

I tried running the first query and then the one you submitted and I don't have the same results.
My query returns 148 different file_path and the other one returns only 31. Do you know where that could come from ?
Also I think I will rework the csv "by hand" and not include the 3rd rex that splits hostname and location, in order to save some resources

[gcusello]

Hi romainbouajila,
if you can simplify your search it is surely an advantage even if I don't think that an extra rex weighs much on it.
In any case, to debug the results: remove the lines from the two searches one by one so as to see if and when you have the same number of results and if all the fields you use later are explained.

Ciao.
Giuseppe