hello
I use the search below in order to monitore the last reboot and the last logon date
`LastLogonBoot`
| eval SystemTime=strptime(SystemTime, "'%Y-%m-%dT%H:%M:%S.%9Q%Z'")
| stats latest(SystemTime) as SystemTime by host EventCode
| xyseries host EventCode SystemTime
| rename "6005" as LastLogon "6006" as LastReboot
| eval NbDaysLogon=round((now() - LastLogon)/(3600*24), 0)
| eval NbDaysReboot=round((now() - LastReboot )/(3600*24), 0)
| eval LastLogon=strftime(LastLogon, "%y-%m-%d %H:%M")
| eval LastReboot=strftime(LastReboot, "%y-%m-%d %H:%M")
| lookup test.csv HOSTNAME as host output SITE
| stats values(LastReboot) as "Last reboot date" values(NbDaysReboot) as "Days without reboot" values(LastLogon) as "Last logon date" values(NbDaysLogon) as "Days without logon" by host SITE
| rename host as Hostname, SITE as Site
| sort -"Days without reboot" -"Days without logon"
From this search, I have created an alert which is a litthe different because I match the date with a new index
Thats the reason why I use a join command
[|`tutu` earliest=-30d latest=now
| lookup toto.csv NAME as AP_NAME OUTPUT Building
| stats last(AP_NAME) as "Access point", last(Building) as "Geo building" by host
| join host type=outer
[|`LastLogonBoot` earliest=-30d latest=now
| eval SystemTime=strptime(SystemTime, "'%Y-%m-%dT%H:%M:%S.%9Q%Z'")
| stats latest(SystemTime) as SystemTime by host EventCode
| xyseries host EventCode SystemTime
| rename "6005" as LastLogon "6006" as LastReboot
| eval NbDaysReboot=round((now() - LastReboot )/(3600*24), 0)
| eval LastReboot=strftime(LastReboot, "%y-%m-%d %H:%M")
| lookup test.csv HOSTNAME as host output SITE BUILDING_CODE DESCRIPTION_MODEL ROOM STATUS
| stats last(LastReboot) as "Last reboot date", last(NbDaysReboot) as "Days without reboot", last(DESCRIPTION_MODEL) as Model, last(SITE) as Site, last(AP_NAME) as "Access point", last(BUILDING_CODE) as Building, last(ROOM) as Room, last(STATUS) as Status by host ]
| search Site = titi
| rename host as Hostname
| table Hostname Model Status "Days without reboot" "Last reboot date" Site Building Room "Access point" "Geo building"
| sort -"Days without reboot"
My question is the following :
When I execute the search, I have some events that doesnt exists in my alert even if they sholud exist
How to explain that? Is it due to the join command?
hi,
Let me explain the working of join
command.
Let's call, search query before join
as main-search and the other as sub-search.
As your query is missing some events, the possibilities are,
- In case of outer join, the result will include all the events from main-search and only matching events from sub-search. Now if the field used with join command is not available in sub-search, those events will not be available.
- There limit on results returned by sub-search. While using join
command, the sub-search will only return 50,000 events. In you case, you are searching for 30 days span, so may be the sub-search event out is greater than 50,000.
For more details you can refer to - https://docs.splunk.com/Documentation/SplunkCloud/latest/SearchReference/Join
accept and up-vote the answer if it helps.
hi,
Let me explain the working of join
command.
Let's call, search query before join
as main-search and the other as sub-search.
As your query is missing some events, the possibilities are,
- In case of outer join, the result will include all the events from main-search and only matching events from sub-search. Now if the field used with join command is not available in sub-search, those events will not be available.
- There limit on results returned by sub-search. While using join
command, the sub-search will only return 50,000 events. In you case, you are searching for 30 days span, so may be the sub-search event out is greater than 50,000.
For more details you can refer to - https://docs.splunk.com/Documentation/SplunkCloud/latest/SearchReference/Join
accept and up-vote the answer if it helps.
Hi thanks for this information, is there another command allowing to return more than 50000 events?
The limit is global for sub-searches. You can change the limit from limits.conf
[join]
subsearch_maxout = 50000
[searchresults]
maxresultrows = 50000
(but it strongly not recommended, as it highly affects the Splunk performance)
Another workaround is, in single search you can include all your data source (index, sourcetypes) ans use stats
to combine the results for desired output.
You can check the following thread for more info,
https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo...
https://answers.splunk.com/answers/146633/how-to-join-large-tables-with-more-than-50-000-rows-in-spl...
ok thanks to you