topic Re: Why isn't my lookup command working? in Splunk Search

Why isn't my lookup command working?

khanlarloo — Tue, 28 Nov 2017 07:58:32 GMT

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?

Re: Why isn't my lookup command working?

harsmarvania57 — Tue, 28 Nov 2017 08:30:54 GMT

Hi @ khanlarloo,

Can you please provide your splunk query if possible?

Re: Why isn't my lookup command working?

gcusello — Tue, 28 Nov 2017 08:32:10 GMT

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

Re: Why isn't my lookup command working?

paulbannister — Tue, 28 Nov 2017 08:32:20 GMT

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

Re: Why isn't my lookup command working?

khanlarloo — Tue, 28 Nov 2017 08:46:36 GMT

sourcetype=csv | stats count by Vulnerability

Re: Why isn't my lookup command working?

khanlarloo — Tue, 28 Nov 2017 08:47:12 GMT

it is automatic lookup.

Re: Why isn't my lookup command working?

khanlarloo — Tue, 28 Nov 2017 08:49:39 GMT

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

Re: Why isn't my lookup command working?

harsmarvania57 — Tue, 28 Nov 2017 08:52:55 GMT

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?

Re: Why isn't my lookup command working?

khanlarloo — Tue, 28 Nov 2017 08:58:11 GMT

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

Re: Why isn't my lookup command working?

harsmarvania57 — Tue, 28 Nov 2017 09:02:27 GMT

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

Re: Why isn't my lookup command working?

khanlarloo — Tue, 28 Nov 2017 09:19:30 GMT

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

Re: Why isn't my lookup command working?

harsmarvania57 — Tue, 28 Nov 2017 09:24:02 GMT

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 ?

Re: Why isn't my lookup command working?

khanlarloo — Tue, 28 Nov 2017 09:40:55 GMT

no vulnerability is in lookup file.

Re: Why isn't my lookup command working?

harsmarvania57 — Tue, 28 Nov 2017 09:49:05 GMT

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).

Re: Why isn't my lookup command working?

khanlarloo — Tue, 28 Nov 2017 12:30:07 GMT

do i need subsearch ?

Re: Why isn't my lookup command working?

khanlarloo — Tue, 28 Nov 2017 12:39:53 GMT

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

Re: Why isn't my lookup command working?

hettervik — Tue, 28 Nov 2017 13:02:29 GMT

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?

Re: Why isn't my lookup command working?

khanlarloo — Tue, 28 Nov 2017 13:14:43 GMT

no i don't get good result

Re: Why isn't my lookup command working?

vishaltaneja070 — Tue, 28 Nov 2017 13:16:05 GMT

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.

Re: Why isn't my lookup command working?

harsmarvania57 — Tue, 28 Nov 2017 13:26:57 GMT

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.