topic Re: help on lost event with join command in Splunk Search

help on lost event with join command

jip31 — Thu, 23 Jul 2020 23:32:33 GMT

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?

Re: help on lost event with join command

gaurav_maniar — Wed, 04 Mar 2020 09:39:20 GMT

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.

Re: help on lost event with join command

jip31 — Wed, 04 Mar 2020 10:14:23 GMT

Hi thanks for this information, is there another command allowing to return more than 50000 events?

Re: help on lost event with join command

gaurav_maniar — Wed, 04 Mar 2020 11:12:49 GMT

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-join-append-or-use-of-subsearches.html
https://answers.splunk.com/answers/146633/how-to-join-large-tables-with-more-than-50-000-rows-in-splunk.html

Re: help on lost event with join command

jip31 — Wed, 04 Mar 2020 12:12:50 GMT

ok thanks to you