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??
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
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
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
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
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...
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 😉
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!
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
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
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
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 |...
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)
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
| 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
Hello all
Could you have a look to my issue?
I need help please 😉
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
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")
| ...
hello
i have host in my CSV
and your code doesnt works....