topic Re: How to compare fields from csv and search, then add the result as a new column in Splunk Search

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

romainbouajila — Mon, 21 Oct 2019 05:11:27 GMT

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 !

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

gcusello — Tue, 22 Oct 2019 07:00:29 GMT

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

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

woodcock — Tue, 22 Oct 2019 08:24:09 GMT

Like this:

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

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

romainbouajila — Tue, 22 Oct 2019 09:28:25 GMT

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

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

romainbouajila — Tue, 22 Oct 2019 09:37:45 GMT

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

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

gcusello — Tue, 22 Oct 2019 10:30:08 GMT

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

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

woodcock — Tue, 22 Oct 2019 13:40:39 GMT

I updated my answer; try it now.

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

romainbouajila — Thu, 24 Oct 2019 08:55:09 GMT

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

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

gcusello — Thu, 24 Oct 2019 09:03:54 GMT

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

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

woodcock — Fri, 25 Oct 2019 22:49:49 GMT

Come back and try it now.