Splunk Search
Highlighted

Query including multiple CSV and Rex

New Member

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

Tags (3)
0 Karma
Highlighted

Re: Query including multiple CSV and Rex

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

Re: Query including multiple CSV and Rex

New Member

Hi Somesoni2,

They are lookup tables

| inputlookup Data1.csv

Thanks for your help!

0 Karma
Highlighted

Re: Query including multiple CSV and Rex

SplunkTrust
SplunkTrust

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
Highlighted

Re: Query including multiple CSV and Rex

Esteemed Legend

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.

0 Karma
Highlighted

Re: Query including multiple CSV and Rex

New Member

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

0 Karma
Highlighted

Re: Query including multiple CSV and Rex

Esteemed Legend

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

0 Karma
Highlighted

Re: Query including multiple CSV and Rex

New Member

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

0 Karma
Highlighted

Re: Query including multiple CSV and Rex

Esteemed Legend

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

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.