topic Re: help with an inputlookup issue in Splunk Search

help with an inputlookup issue

jip31 — Fri, 10 May 2019 12:28:58 GMT

Hello
I use the search below wich runs perfectly

(index="X" sourcetype=XmlWinEventLog source="XmlWinEventLog:System" EventCode=* (Level=1 OR Level=2 OR Level=3)) OR (index=master-data-lookups sourcetype="itop:view_splunk_assets")
 | eval host=coalesce(HOSTNAME,host)
 | eval time=if(EventCode="*",_time,null())
 | stats values(sourcetype) as sts max(time) as _time values(SITE) as SITE values(ROOM) as ROOM values(TOWN) as TOWN values(CLIENT_USER) as CLIENT_USER values(COUNTRY) as COUNTRY values(OS) as OS by host
 | where NOT (mvcount(sts)=1 AND sts="X:view_splunk_assets")
  | table _time host COUNTRY TOWN SITE ROOM CLIENT_USER OS 
  | sort -_time – COUNTRY

But I need to do the search from a list of host in a csv file
So I put [|inputlookup host.csv | table host] at the beginning of my search but it doesn't works even if I am sure that events exists between my CSV file

**[|inputlookup host.csv | table host]** (index="x" sourcetype=XmlWinEventLog source="XmlWinEventLog:System" EventCode=* (Level=1 OR Level=2 OR Level=3)) OR (index=master-data-lookups sourcetype="itop:view_splunk_assets")
 | eval host=coalesce(HOSTNAME,host)
 | eval time=if(EventCode="*",_time,null())
 | stats values(sourcetype) as sts max(time) as _time values(SITE) as SITE values(ROOM) as ROOM values(TOWN) as TOWN values(CLIENT_USER) as CLIENT_USER values(COUNTRY) as COUNTRY values(OS) as OS by host
 | where NOT (mvcount(sts)=1 AND sts="x:view_splunk_assets")
  | table _time host COUNTRY TOWN SITE ROOM CLIENT_USER OS 
  | sort -_time – COUNTRY

what is strange is that when I specify an hostname which also exists in my csv file, I have results and the 2 indexes I query

I have another issue with the field _time
I want to catch the field _time when an event for index="X" sourcetype=XmlWinEventLog source="XmlWinEventLog:System" EventCode=* (Level=1 OR Level=2 OR Level=3) occurs
For the moment the field _time which is displayed is the _time when I run the query....
Could you help me please??

Re: help with an inputlookup issue

koshyk — Fri, 10 May 2019 12:46:18 GMT

Do you have "hostname" in your CSV or "host"?

Please try like..

[|inputlookup host.csv | fields host] (index="ai-wkst-wineventlog-fr" sourcetype=XmlWinEventLog source="XmlWinEventLog:System" EventCode=* (Level=1 OR Level=2 OR Level=3)) OR (index=master-data-lookups sourcetype="itop:view_splunk_assets")
| ...

Re: help with an inputlookup issue

jip31 — Fri, 10 May 2019 13:10:07 GMT

hello
i have host in my CSV
and your code doesnt works....

Re: help with an inputlookup issue

jip31 — Mon, 13 May 2019 05:15:14 GMT

Hello all
Could you have a look to my issue?
I need help please 😉

Re: help with an inputlookup issue

jip31 — Wed, 30 Sep 2020 00:32:48 GMT

I have done this I dont succeed to match with index=x sourcetype="x:view_splunk_assets"
index="x" sourcetype=XmlWinEventLog source="XmlWinEventLog:System" EventCode=* (Level=1 OR Level=2 OR Level=3)
[| inputlookup host.csv
| table host]
| dedup host
| table _time host EventCode
| join type=left host
[ search index=x sourcetype="x:view_splunk_assets"
| stats count by HOSTNAME SITE ROOM TOWN CLIENT_USER COUNTRY OS
| fields - count
| rename HOSTNAME as host]
| table _time host COUNTRY TOWN SITE ROOM CLIENT_USER OS
| sort -_time limit=10

Re: help with an inputlookup issue

nabeel652 — Mon, 13 May 2019 06:15:11 GMT

If there is any other in the lookup to use as destination field (for example just add a field "found" with value 1) - why not use

(index="X" sourcetype=XmlWinEventLog source="XmlWinEventLog:System" EventCode=* (Level=1 OR Level=2 OR Level=3)) OR (index=master-data-lookups sourcetype="itop:view_splunk_assets") | lookup host.csv host output found | where found=1 |...

Re: help with an inputlookup issue

jip31 — Wed, 30 Sep 2020 00:32:51 GMT

yes you have right
it works with lookup instead lookup
I have done this :
(index="x" sourcetype=XmlWinEventLog source="XmlWinEventLog:System" EventCode=* (Level=1 OR Level=2 OR Level=3)) OR (index=master-data-lookups sourcetype="xview_splunk_assets")
| rename HOSTNAME as host
| lookup host.csv host OUTPUT host

| stats values(SITE) as SITE values(ROOM) as ROOM values(TOWN) as TOWN values(CLIENT_USER) as CLIENT_USER values(COUNTRY) as COUNTRY values(OS) as OS by host 
| table _time host COUNTRY TOWN SITE ROOM CLIENT_USER OS 
| sort -_time - COUNTRY limit=10

I have just an issue with _time
I want to retrieve the field _time which correspond to the time of event creation time in index="x" sourcetype=XmlWinEventLog source="XmlWinEventLog:System" EventCode=* (Level=1 OR Level=2 OR Level=3)

Re: help with an inputlookup issue

DavidHourani — Mon, 13 May 2019 07:33:20 GMT

Hi @jip31,

Seems like you've got quite the challenge there. This will do the trick for filtering only the hosts that are in the CSV file :

(index="ai-wkst-wineventlog-fr" sourcetype=XmlWinEventLog source="XmlWinEventLog:System" EventCode=* (Level=1 OR Level=2 OR Level=3)) OR (index=master-data-lookups sourcetype="itop:view_splunk_assets")  [|inputlookup host.csv | return 9999 host]

More info about using the return command can be found here :

https://docs.splunk.com/Documentation/Splunk/7.2.6/SearchReference/Return
If you need to put the results in tabular format and still keep the earliest time then you can go append this stats search :

 | stats earliest(_time) as _time values(SITE) as SITE values(ROOM) as ROOM values(TOWN) as TOWN values(CLIENT_USER) as CLIENT_USER values(COUNTRY) as COUNTRY values(OS) as OS by host

Let me know how that works out for you.

Cheers,
David

Re: help with an inputlookup issue

jip31 — Wed, 30 Sep 2020 00:32:54 GMT

oh I just realize that there is no matching with lookup host.csv because if I add a specific eventcode I have always results even if there is no events for this eventcode.......

(index="X" sourcetype=XmlWinEventLog source="XmlWinEventLog:System" EventCode=6008 (Level=1 OR Level=2 OR Level=3)) OR (index=master-data-lookups sourcetype="X:view_splunk_assets")
| rename HOSTNAME as host
| lookup host.csv host OUTPUT host
| stats values(SITE) as SITE values(ROOM) as ROOM values(TOWN) as TOWN values(CLIENT_USER) as CLIENT_USER values(COUNTRY) as COUNTRY values(OS) as OS by host

Re: help with an inputlookup issue

jip31 — Wed, 30 Sep 2020 00:33:02 GMT

hi david
can you confirm that [| inputlookup host.csv
| return 9999 host] is just used for testing my subsearch
or does it replace [| inputlookup host.csv | table host]??

and if i am doing this I have any results...
(index="x" sourcetype=XmlWinEventLog source="XmlWinEventLog:System" (Level=1 OR Level=2 OR Level=3)) OR (index=master-data-lookups sourcetype="x:view_splunk_assets")
[| inputlookup host.csv
| table host]
| rename HOSTNAME as host
| stats earliest(_time) as _time values(SITE) as SITE values(ROOM) as ROOM values(TOWN) as TOWN values(CLIENT_USER) as CLIENT_USER values(COUNTRY) as COUNTRY values(OS) as OS by host

PS : in index=master-data-lookups sourcetype="x:view_splunk_assets" the fields host is called HOSTNAME so I have added a rename HOSTNAME as host

Re: help with an inputlookup issue

DavidHourani — Mon, 13 May 2019 09:34:58 GMT

yes, use return, not table.

In case you dont have the same field in both, I suggest you stay on the HOSTNAME field and create an alias for it in your index=x. That way you don't rely on the "host" field it self. Then in the csv use HOSTNAME instead of host.

If you'd rather keep thing as they are without making aliases then it should look something like that :

(index="x" sourcetype=XmlWinEventLog source="XmlWinEventLog:System" (Level=1 OR Level=2 OR Level=3)) OR (index=master-data-lookups sourcetype="x:view_splunk_assets") 
| rename HOSTNAME as host 
|search [| inputlookup host.csv | return 9999 host] 
| stats earliest(_time) as _time values(SITE) as SITE values(ROOM) as ROOM values(TOWN) as TOWN values(CLIENT_USER) as CLIENT_USER values(COUNTRY) as COUNTRY values(OS) as OS by host

Re: help with an inputlookup issue

jip31 — Wed, 30 Sep 2020 00:33:05 GMT

David
There is something which doesnt works because if I put an eventcode which dont exists like eventcode=a I have all the same events displayed...
Have you an idea please??

(index="ai-wkst-wineventlog-fr" sourcetype=XmlWinEventLog source="XmlWinEventLog:System" EventCode=a (Level=1 OR Level=2 OR Level=3)) OR (index=master-data-lookups sourcetype="itop:view_splunk_assets")
| rename HOSTNAME as host
| search
[| inputlookup host.csv
| return 9999 host]
| stats earliest(_time) as _time values(SITE) as SITE values(ROOM) as ROOM values(TOWN) as TOWN values(CLIENT_USER) as CLIENT_USER values(COUNTRY) as COUNTRY values(OS) as OS by host

Re: help with an inputlookup issue

jip31 — Mon, 13 May 2019 11:20:26 GMT

and _time needs to correspond to the time when an event for (index="ai-wkst-wineventlog-fr" sourcetype=XmlWinEventLog source="XmlWinEventLog:System" EventCode=a (Level=1 OR Level=2 OR Level=3) occurs...

Re: help with an inputlookup issue

DavidHourani — Mon, 13 May 2019 11:52:43 GMT

yeah that's normal, because you will still get events from index=master-data-lookups sourcetype="itop:view_splunk_assets" if you apply a wrong event code to the first part of your code 😄

As for the time, which one occurs first ? You can use earliest and latest based on that to have only this time to show

Re: help with an inputlookup issue

jip31 — Mon, 13 May 2019 15:48:03 GMT

But if the event code is wrong I want that any events are displayed and if I put a specific event code I need that only the events corresponding are displayed
What is the solution to do this please???
For the time I would have the time when an event corresponding to (index="ai-wkst-wineventlog-fr" sourcetype=XmlWinEventLog source="XmlWinEventLog:System" EventCode=1000 (Level=1 OR Level=2 OR Level=3)) occurs

Re: help with an inputlookup issue

DavidHourani — Mon, 13 May 2019 15:50:43 GMT

easiest solution I can think of is to alias time as something else for sourcetype=XmlWinEventLog, and use that field instead of the _time, that way you're sure to get the right one 😉

Re: help with an inputlookup issue

jip31 — Wed, 30 Sep 2020 00:33:23 GMT

I have done something else
as index=master-data-lookups sourcetype="itop:view_splunk_assets") is equal to | lookup lookup_cmdb_fo_all.csv i have written the code below

(index="ai-wkst-wineventlog-fr" sourcetype=XmlWinEventLog source="XmlWinEventLog:System" EventCode=37 (Level=1 OR Level=2 OR Level=3))
[| inputlookup host.csv
| table host]
| table _time host EventCode
| lookup lookup_cmdb_fo_all.csv HOSTNAME as host
| stats max(_time) as _time values(SITE) as SITE values(ROOM) as ROOM values(TOWN) as TOWN values(CLIENT_USER) as CLIENT_USER values(COUNTRY) as COUNTRY values(OS) as OS by host

now I have the time of the event for event code and I am also able to display events for a specific event code!

Re: help with an inputlookup issue

nabeel652 — Wed, 15 May 2019 04:02:41 GMT

| lookup host.csv host OUTPUT host

this will not work you cannot output same field that you are looking up. You will have to create a separate field for example like this

| inputlookup host.csv | eval found=1 | outputlookup host.csv

and then use

| lookup host.csv host OUTPUT found | where found=1