I have multiple sourcetypes in my index. Lets call them st1, st2, st3, st4 & st5.
I have a query that end with | table user _time << This is from st1
What I am trying to do is,
Fetch the "_time" field and "user" filed (from the above query) and using them as a search parameter to look for associated events in all remaining sourcetypes (i.e. st2,st3,st4,st5).
which means all the events in other sourcetypes should contain that user name OR should have the same time stamp OR in a five min time duration before and after that event .
The goal here is to figure out what was happening in the system (Across all the source types) during a certain time frame. and able to visualize it at one place like a dashboard.
I think of may be using subseach or localize.....But I am confused....how to begin.
Subsearch is a good starting point here. You can start with your first query as the subsearch, returning user
and the time range in which you want to search. According to your requirements, the 10-minute-timeframe before and after the event should cover the exact time stamp as well, so I assume a earliest
and latest
search might be sufficient.
Please start with this and see if it helps:
<your_search_for_st2,st3,st4,st5> [|search <your_search_returning_user_and_time> | eval earliest=relative_time(_time,"-5m") | eval latest=relative_time(_time,"+5m") | return earliest latest user]
EDIT:
Refined the search as per comment below
Subsearch is a good starting point here. You can start with your first query as the subsearch, returning user
and the time range in which you want to search. According to your requirements, the 10-minute-timeframe before and after the event should cover the exact time stamp as well, so I assume a earliest
and latest
search might be sufficient.
Please start with this and see if it helps:
<your_search_for_st2,st3,st4,st5> [|search <your_search_returning_user_and_time> | eval earliest=relative_time(_time,"-5m") | eval latest=relative_time(_time,"+5m") | return earliest latest user]
EDIT:
Refined the search as per comment below
Thank you.
When I test this. I should see events in a 5 min time range. But I see way past events.
The search result should contain all the events before and after 5 min of the time fetched from this ([|search ) query....
The search event outputs from all the sourcetypes should also match the fetched username.
So the constraint is time AND username? Can you please confirm this? Because in that case the search string might be much easier...
yes, the constrain it time and the username.
Okay, that changes some requirements. I have updated the query above, please see if this works for you!
Here is the final query I am using
index=baysian host=the_viral_host* [| search sourcetype="baysian:greece" host=the_viral_host*
| eval bla bla bla
| eval bla bla bla
| table host user _time serv_time | sort - serv_time | head 1 | eval earliest=relative_time(_time,"-5m") | eval latest=relative_time(_time,"+5m") | return earliest latest user]
But this gives me no result !!! 😞
Could you please just run the subsearch part and see what the return value is (so everything after [|search
yes. subsearch part gives me result.
How does the result look like? It should be a table with a search column containing something like earliest="1234567890.00000" latest="1234567890.0000" user="someusername"
What if you add this string manually to your base search, does that show any results?
@DMohn
How about if we use the earliest and latest command in the main query by the returned latest and earliest values from the subquery ? That way the search results will be limited to the time range we want.
Is it even possible to return and catch multiple values in subsearches like that?
Any suggestion !!
Yes, that might be a good way to go for it! Anyway, check my last comment, and try using the last mentioned return statement. You can basically return as many fields as you want from the subsearch, as long as the fieldnames are correct. You can even return multiple results, which might not be necessary in your case anyway.
@DMohn I appreciate you so patiently sticking to the issue and helping me out. The solution now works. Thank you & have a good rest of the day.
That's what the community is here for! Have a good day as well.
Yes. subsearch result looks exactly like you have given.
And I was trying to feed this result value directly into the main query it doesn't work. (except when I add user name only)
I think we might have missed $ , like return $ user]
And when add that it works....
But making return $earliest $latest $username] doesn't work.
Because in the main query it takes the returned time (1234567890.0000) as a search criteria...instead of considering that value as a 'time range' ...
The main query becomes like
index=baysian host=the_viral_host* 1234567890.00000 1234567890.0000 someusername
And there is no such keyword in my logs such as 1234567890.00000 AND 1234567890.0000 . hence the output is null. If we can only make the earliest and latest to be considered by the main query as a time range instead of a search keyword, that might help.
You are on the right way ... If you make it a return $fieldname
it just returns the value of that field, a return fieldname
returns fieldname=value.
Knowing that, I assume the field user
in your main query does not exist, or is not extracted as such, because if you have a free-text search (what you have when you do a return $user
) you get results.
So, either check if your field user
in the main query is extracted correctly (and named correctly), rename the return field (eg return user=username
) or make it a full text search ( return $user
)
Either way, leave the earliest and latest as stated, so you will have the time range interpreted correctly. So, going for return earliest latest $user
might be a good start!