Splunk Search

help on lost event with join command

jip31
Motivator

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?

Labels (2)
Tags (1)
0 Karma
1 Solution

gaurav_maniar
Builder

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.

View solution in original post

0 Karma

gaurav_maniar
Builder

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.

0 Karma

jip31
Motivator

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

0 Karma

gaurav_maniar
Builder

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

0 Karma

jip31
Motivator

ok thanks to you

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...