Splunk Search

How to use Where condition in lookup .csv file

bagarwal
Path Finder

Hi,

I have created a lookup file name file1.csv . There are two columns in the file "Application" and "Allow" and looks like below .
Application, Allow
Paint, Y
abc*, Y
xyz*,
aaa*, Y
zzz,

Now, I want to run a query to get the output of application name where Allow = Y.

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

I am very new in working with lookup and would be really helpful if anyone can suggest changes need to be made in the query to match the where condition and get the result or any other way to get the desired result.

Thanks in advance

Best Regards,
Binay Agarwal

Tags (2)
0 Karma

pinkyy
New Member

hi,if csv file contain src_ip and src_prt,i need all data except src_ip mentioned in the csv file..what will be the lookup query ?

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

You can use where clause in inputlookup like

|inputlookup geo_attr_countries.csv where (country="Austria")|table continent subregion

https://docs.splunk.com/Documentation/Splunk/6.5.1/SearchReference/Inputlookup#Optional_arguments

If you want to use it together with another search query , try just lookup after defining the lookup definitions. For eg: if you have a field Application in your search query, you can try index=indexname|fields Application|lookup mylookupname|search Allow="Y"
where mylookupname is the lookup definition you created out of your file

Reference : http://docs.splunk.com/Documentation/Splunk/6.5.1/SearchReference/Lookup

Happy Splunking!
0 Karma

bagarwal
Path Finder

I am trying to run with the below query but no result found

index="indexname" [ | inputlookup file1.csv where (Allow='Y')| fields Application, Allow | rename Application as App ] | table host, App

Also tried like this :
index="indexname" [ | inputlookup file1.csv | fields Application, Allow | rename Application as App | search Allow='Y' ] | table host, App
No result found.

Note: the reason I use rename Application as App as there is no fieldname by Application in raw event. The field Application is only in my lookup file as a column header.

Anything I am missing out in the query, please help

Thanks & Regards,
Binay Agarwal

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

Hi Binay,

Here is what I have done ,
- Created an input lookup file similar to yours (Application, Allow)
- Uploaded the lookup file
- Added a lookup definition myapplookup as file based
- Created a dummy search to simulate your search and lookup in the lookup file to get the rest of the fields

|stats count |eval App="Paint,abc*,xyz*,zzz"|eval Host="Host1,Host2,Host3,Host4"|makemv delim="," App|makemv delim="," Host|mvexpand App|mvexpand Host
|table Host App|rename App as Application|lookup myapplookup Application|search Allow="Y"

Ignore the first line of the search , it's just a dummy to simulate.

Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...