I am trying to join in some status information in real-time against a static list of data, but getting an error when setting my subsearch to real time:
invalid value "rt-1h" for time term 'earliest' What's going wrong here?
(reason: I have a static list of apps in one index, and need the status from a log line in another. The status might not be there - in that case I have to assume the app is down - so I need the static list of apps to join against.)
Search: (all time, as app listing could be quite old)
index=applisting | table app | join type=outer app [search index=appstatus earliest=rt-1h latest=rt | dedup app | table app status]
From this answer it looks like they're not meant to be used. So how does one kick off a real-time search from the search bar?
If you do it the other way its much more reliable : You dont need your app list to be in real-time
index=appstatus earliest=rt-1h latest=rt | dedup app | table app| join type=outer app [search statusindex=applisting earliest=-1y latest=now| table app ]
Unless they have fixed type=outer to mean full outer join (not left outer join) then you'll still run into problems where an app is in the listing but has no status (perhaps because it's down) - there will be no line for it.
I found a way to do it that works but is obviously not ideal.
index=applisting | table app | join type=outer app [search index=appstatus earliest=-1h latest=+2y | dedup app | table app status]
As new data arrives it updates the primary search. Not sure how a 2 year forward looking subsearch will go with performance hit as the months go by though.
Well, joins or appends work when it's not a realtime search. The whole point was a realtime status dsahboard though.
I just tested
index=_internal | stats count by host | append [inputlookup allHosts] | stats max(count) as count by host and found that it works properly (showing hosts as blank if they are in the lookup but not in the events) if run non-RT, but only shows hosts in the events if changed to RT.
Could you flip the search around and use a lookup instead?
Maybe you could perform the rt search and then use a lookup to pull more detailed app data. I think that would mean a lookup file instead of an index for applisting
The result of a subsearch is used as a parameter for the main search, and is therefore run first. Real-time searches do not finish, hence cannot be used as a subsearch. You get a hint from splunk when you select a real-time window from the time picker and run a search with a subsearch:
[subsearch]: Subsearches of a real-time search run over all-time unless explicit time bounds are specified within the subsearch.
Nor are appends supported in realtime, though they don't error out. Consider this search:
index=_internal | stats count by host | append [inputlookup allHosts] | stats max(count) as count by host
Non-RT, it shows all hosts in allHosts, even if they don't have any events. Changed to RT, it becomes a shorter list of only hosts that have events.
Hi Jason, did you see my comment above to use a lookup instead?
You could perform your realtime search first, then use a lookup of the static data to fill out the results with whatever you want from the static list of app info.
Last I checked the join command doesn't have a right join (meaning, always include all members of the subsearch, whether or not they exist in the main search). I think I tried outer join but it would not include any items from the static list where they did not exist in the main list, so outer join is really a left join, not a full outer join.
Maybe I should try with an append and a stats rather than a join.
Disappointing. Then how does one join up a real time data stream, that may or may not contain the status of the app, with a static list of apps so the report looks the same (doesn't have holes for nulls)?