Splunk Search

Using lookup (.csv file) to filter events

bagarwal
Path Finder

I am creating a new post, as though I found similar questions but could not get the right solution.

I want to run a query where I can filter events using lookup file. As the file contains a list of application name it will keep adding. So I created .csv file and lookup table, lookup definition.

File name is file1.csv
Note: In my .csv file there is only one column and it looks like below: File name is file1.csv

Application ( It is the column header)
Paint
abc*
xyz*
aaa*

Query is
index="index_name" [ | inputlookup "file1.csv" | fields Application ] | table field1, field2

The above query didn't fetch any result.

Anything I am missing. Kindly help. I really will be thankful if someone can provide the solution.

Thanks & Regards,
Binay Agarwal

0 Karma
1 Solution

somesoni2
Revered Legend

The above syntax expects there is a field called Application in your events.

IF you don't have that field and want to do text based search, try like this

index="index_name" [ | inputlookup "file1.csv" | table Application | rename Application as search ] | table field1, field2

If you have a field which contains this Application values, but have different name, try like this

index="index_name" [ | inputlookup "file1.csv" | table Application | rename Application as FieldNameInYourEvents] | table field1, field2

View solution in original post

bagarwal
Path Finder

Hi @somesoni2,

Thank You so much. This is exactly needed 🙂 A BIG THANK YOU once again. 🙂

Best Regards,
Binay Agarwal

0 Karma

bagarwal
Path Finder

Hi @somesoni2, all,

Would like to add feature in my lookup file .
Now I have 2 columns the .csv file
Application Category_Y
Paint Y
abc* Y
xyz*
aaa* Y

Now I want to write a query that gives the result when Catogory_Y =Y

Current Query:
index="index_name" [ | inputlookup | table Application | rename Application as App ] | table host, App

Kindly suggest changes need to be made in the query to match the condition and get the result.

Thanks in advance

Best Regards,
Binay Agarwal

0 Karma

somesoni2
Revered Legend

The above syntax expects there is a field called Application in your events.

IF you don't have that field and want to do text based search, try like this

index="index_name" [ | inputlookup "file1.csv" | table Application | rename Application as search ] | table field1, field2

If you have a field which contains this Application values, but have different name, try like this

index="index_name" [ | inputlookup "file1.csv" | table Application | rename Application as FieldNameInYourEvents] | table field1, field2

bagarwal
Path Finder

Hi @somesoni2, all,

Would like to add feature in my lookup file .
Now I have 2 columns the .csv file
Application, Category_Y
Paint, Y
abc*, Y
xyz*
aaa*, Y

Now I want to write a query that gives the result when Catogory_Y =Y

Current Query:
index="index_name" [ | inputlookup | table Application | rename Application as App ] | table host, App

Kindly suggest changes need to be made in the query to match the condition and get the result.

Thanks in advance

Best Regards,
Binay Agarwal

0 Karma

bagarwal
Path Finder

Hi,

Now since I am getting the result and to make it more flexible, in dashboard, I want to add input textbox where we can search via field values in application so for any particular value I can write in search box and run the query.

e.g. if my field2 contains abc ; I want to type abc is search box and click submit and all results containing abc of that field will pull out the report.

Can anyone please help in that.

Thanks & Regards,
Binay Agarwal

0 Karma

somesoni2
Revered Legend

See the example dashboard of the textbox form input on Splunk documentation.

http://docs.splunk.com/Documentation/Splunk/6.3.0/Viz/PanelreferenceforSimplifiedXML#input_type_.22t...

Also, would suggest to download and install "Splunk 6.x Dashboard Example" app which has this and many other sample dashboards.

bagarwal
Path Finder

Thanks @somesoni2. It helps 🙂

0 Karma

bagarwal
Path Finder

Hi,

The dashboard is perfectly prepared. One additional thing required. Need to add the field with username, but the challenge is user field is not available in raw event. So any way we can co-relate or pull username matches with the host name and display username in the report.

Query looks like this:

index="index_name" [ | inputlookup | table Application | rename Application as App ] | table host, App

And want to add one more column username so query should be | table host, username, App

Would be thankful if someone can help on this.

Thanks & Regards,
Binay Agarwal

0 Karma

somesoni2
Revered Legend

From where you can get the user name when you say you can pull from the host name? Is there a mapping exists which relates host with user?

0 Karma

bagarwal
Path Finder

Hi @somesoni2,

I mean if we can pull out from other source if hostname and username are present in say in firewall logs (different index ) or in different search head. Not sure how that could be .

and also if there is any way to retrieve username when the details are not present in raw logs at all.

Thanks inadvance.

Best Regards,
Binay Agarwal

0 Karma
Get Updates on the Splunk Community!

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...

Splunk and Fraud

Watch Now!Watch an insightful webinar where we delve into the innovative approaches to solving fraud using the ...