Why isn't my lookup command working?

[khanlarloo]

Hi,

I have a problem when searching my lookup field. I added a lookup file to my search with 3 fields (Vulnerability, Risk, Reference). But when I'm searching with stats command or something else it shows me a different result.

Are there any solutions?

[vishaltaneja070]

Hi khanlarloo,

First please clear if you are using automatic lookup or not?

In case of automatic lookup, run a basic query and check if you are getting the field (Vulnerability ,Risk,Reference) in fields side bar or not. If yes, then check the fields name properly because fields name are case-sensitive.
If the fields are not visible in fields sidebar then, there is issue with automatic look. Please check the lookup setting that will solve your issue.

If you haven't used automatic lookup then you need to declare lookup command in the search like
sourcetype=csv | lookup ios.csv output Vulnerability, Risk, reference | stats count by Vulnerability

Make sure the fields name above should match exactly with the field name in csv file, even they are case sensitive.

In case of any issue, reply back.

Thanks.

[khanlarloo]

i'm using automatic lookup and i get the field in fields side bar. but when i want to use basic search like stats it doesn't show me the exact result.
It shows me duplicat result

[paulbannister]

Do have bit more detail about how you are calling the lookup and what your report looks like? i.e is this an automatic lookup or are you calling in within the report

[khanlarloo]

it is automatic lookup.

[harsmarvania57]

which fields have you mapped with automatic lookup ? When you try to search sourcetype=csv without stats command, are you able to see Vulnerability ,Risk,Refrence fields on left hand side as Interesting Fields?

[khanlarloo]

yes.i can see them in right pannel.but when i search them it shows different result.not showing the exact number
sourcetype=csv | stats count by Vulnerability Risk Refrence

[harsmarvania57]

Can you please explain "not showing exact number" ? what value are you expecting and what values you are getting?

[khanlarloo]

when i search in my csv file for exam i want to know the number of my vulnerability the number is 10 but when i do this in splunk it shows me more than 10

[hettervik]

I don't know if I'm understanding the issue at hand correctly, but when you're using stats with several clauses (vulnerability, risk and refrence), it doesn't show the total count of each clause, but the count of all combinations of the values of the clauses.

If you try to only count by one clause, e.g. vulnerability, do you get the number you are expecting then?

[khanlarloo]

should i use subsearch?

[khanlarloo]

no i don't get good result

[harsmarvania57]

In which csv you have only 10 vulnerability, "net.csv" (means source file)? If yes then can you please try to search using sourcetype=csv source=*net.csv* | stats count by Vulnerability ?

[khanlarloo]

no vulnerability is in lookup file.

[harsmarvania57]

So I have confusion here about file which you are talking about, can you please provide some sample data from source and lookup file which you are trying for automatic lookup (Please mask sensitive data or alter the data).

[khanlarloo]

My lookup file
IOSVersion,Vulnerability,Risk,Refrence
12.2(55)SE10,Cisco IOS and IOS XE Software DHCP Remote Code,Critical,
12.2(55)SE10,Cisco IOS Software for Cisco Industrial Ethernet Switches,High,
12.2(55)SE10,Multiple Cisco Products Manipulation Vulnerability,Medium,
12.2(55)SE10,SNMP Remote Code Execution Vulnerabilities in Ci,High,

My Csv file
Hostname,IOSVersion,IPAddress,index,source,sourcetype,splunk_server
Gaandi(2nd-Floor),12.2(55)SE10,,main,Network Switch.csv,csv,LAPTOP-IRG241OV
Gaandi(3rd-Floor),12.2(55)SE10,,main,Network Switch.csv,csv,LAPTOP-IRG241OV
Dispatching,12.2(55)SE10,,main,Network Switch.csv,csv,LAPTOP-IRG241OV

[harsmarvania57]

ok, so I have replicated your sample data in my lab environment and I am assuming you are mapping source data and lookup data with IOSVersionfield so in this case when you try to run search sourcetype=csv | stats count by Vulnerability it is giving count 3 for each Vulnerability which is correct because each Vulnerability is matching with IOSVersion in all 3 rows in source data so that's why it is giving count 3 for all 4 Vulnerability.

Please correct me if I misunderstood your question/comment.

[khanlarloo]

do i need subsearch ?

[gcusello]

Hi khanlarloo,
could you share your search?
Anyway check the file names and put a special attention to the cases of your values that could not match and if there are duplicate values!
Bye.
Giuseppe

[khanlarloo]

sourcetype=csv | stats count by Vulnerability

[khanlarloo]

i add a csv file named net.csv my lookup csv file is ios.csv
no there are no duplivate value