Using lookup (.csv file) to filter events

[bagarwal]

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

[bagarwal]

Hi @somesoni2,

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

Best Regards,
Binay Agarwal

[bagarwal]

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

[somesoni2 · Accepted solution]

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]

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

[bagarwal]

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

[somesoni2]

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]

Thanks @somesoni2. It helps 🙂

[bagarwal]

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

[somesoni2]

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?

[bagarwal]

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