Splunk Search

help with an inputlookup issue

jip31
Motivator

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??

Tags (1)
0 Karma
1 Solution

DavidHourani
Super Champion

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

View solution in original post

0 Karma

DavidHourani
Super Champion

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

0 Karma

jip31
Motivator

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

0 Karma

DavidHourani
Super Champion

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
0 Karma

jip31
Motivator

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

0 Karma

DavidHourani
Super Champion

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 😉

0 Karma

jip31
Motivator

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!

0 Karma

jip31
Motivator

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 hostalt text

0 Karma

DavidHourani
Super Champion

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

0 Karma

jip31
Motivator

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

0 Karma

nabeel652
Builder

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 |...
0 Karma

jip31
Motivator

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)

0 Karma

jip31
Motivator

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

0 Karma

nabeel652
Builder
| 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
0 Karma

jip31
Motivator

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

0 Karma

jip31
Motivator

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

0 Karma

koshyk
Super Champion

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")
| ...
0 Karma

jip31
Motivator

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...