Query including multiple CSV and Rex

[metalshad05]

Hello everyone, it's been a long long time that I've not used splunk. I would need some help to do a query or two please...

There's 3 csv;
Data1.csv = First CSV contains all the boxes hostnames in the field with this format "tsk-site-type" with other fields (ip, serialNumber, etc)
Data2.csv = Second CSV contains the sites that we will deploy this year
Data3.csv = Third CSV contains all the sites with the geographic coordinates

I would like to have 2 things;
-A list of all the information concatenated (including the site, the type, the coodinates and if it will be deployed this year)
-A map with the hostnames of the sites that we will deploy this year

All of this will go on a dashboard.

Thanks for helping

[woodcock]

Assuming all 3 datasets have a field called "site", like this:

| inputlookup Data1.csv | rename Data1Site AS site
| inputlookup append=t Data2.csv | rename Data2Site AS site
| inputlookup append=t Data3.csv | rename Data3Site AS site
| stats values(*) AS * BY site

then just play around with the maps on the visualization tab.

[metalshad05]

Hi woodcock,

Thanks a lot for your time! I juste need 2 more things as asked in my question;

For Data1Site, I need to extract the site name and the type in 2 new columns using regex
For Data2Site, I need some kind of flag stating if it will be done this year please

Thanks again

[woodcock]

Did you try it? It should provide a basis for all of your logic.

[metalshad05]

I did, it's not working because I need to do the regex to have the site name
The actual format is
Data1.csv contains hostname in lowercase: "tsk-site-type" (ie tsk-ranc-rscc01)
Data2.csv contains Site in uppercase (ie RANC)

So I need at least a rex to get a new column for the site name and the type to do the join by site

Thanks in advance

[woodcock]

Fine, then you need to do a | rename and also | eval site=lower(site).

[somesoni2]

Those csv's, are they added to Splunk as lookup table file or are indexed?

[metalshad05]

Hi Somesoni2,

They are lookup tables

| inputlookup Data1.csv

Thanks for your help!

[somesoni2]

Give this a try

| inputlookup Data1.csv | table hostname ip serialNumber | eval site=replace(hostname,"^([^\-]+)\-([^\-]+)\-([^\-]+)$","\2") | lookup Data2.csv site OUTPUT site as WillDeployThisYear | eval WillDeployThisYear =if(isnotnull(WillDeployThisYear),"Yes","No") | lookup Data3.csv site OUTPUT langFieldNameHere longFieldNameHere