topic Re: Find event in one search, get related events by time in another search in Splunk Search

Find an error in 1st system and then find errors close in time in a 2nd system

GEB — Thu, 27 Jun 2024 12:51:12 GMT

"Find event in one search, get related events by time in another search"
Found some related questions but could not formulate a working solution from them.... Of course this doesn't work, but maybe it will make clear what is wanted, values in 2nd search events within milliseconds (2000 shown) of first search's event....

Re: Find event in one search, get related events by time in another search

bowesmana — Mon, 24 Jun 2024 23:26:30 GMT

First question - is the output a single row or are there multiple rows expected, in which case, what is the entity that separates the rows - is it REFERENCE_VAL and if so, how does one correlate REFERENCE_VAL to RELATED_VAL?

This is the ONE row solution

index=someIndex searchString OR someSearchString | rex field=_raw "stuff(?<REFERENCE_VAL>)$" | rex field=_raw "stuff(?<RELATED_VAL>)$" | stats min(eval(if(isnotnull(REFERENCE_VAL), _time, null()))) as EVENT_TIME min(eval(if(isnotnull(RELATED_VAL), _time, null()))) as RELATED_TIME | eval timeBand=RELATED_TIME-EVENT_TIME | where abs(timeBand)<2000

which will only give a result if the time range is less than 2 seconds, but I suspect you are expecting more than one row...

Re: Find event in one search, get related events by time in another search

yuanliu — Tue, 25 Jun 2024 06:17:31 GMT

I think you are looking for map.

index=someIndex searchString | rex field=_raw "stuff(?<REFERENCE_VAL>somestuff)$" | rename _time as EVENT_TIME | eval start = EVENT_TIME - 1, end = EVENT_TIME + 1 | map maxsearches=1000 search="index=anIndex someSearchString earliest=$start$ latest=$end$ | rex field=_raw "stuff(?<RELATED_VAL>otherstuff)$" | rename _time as RELATED_TIME | fields RELATED_*" | table EVENT_TIME REFERENCE_VAL RELATED_TIME RELATED_VAL

Caveats:

When there are many events in main search, it can be very, very expensive.
You need to give a number to maxsearches; it cannot be 0. (See documentation for more limitations.)
If you are using [-1000ms, + 1000ms], chances are strong that all these start-end pairs will overlap badly, rendering your question itself rather meaningless. You can develop algorithms to merge these overlaps to make map command more efficient (by reducing intervals). But you need to ask yourself (or your boss) seriously: Is this a well-posed question?

Re: Find event in one search, get related events by time in another search

GEB — Tue, 25 Jun 2024 12:30:50 GMT

Based on the data, I expect 2-4 rows per single REFERENCE_VAL.

Re: Find event in one search, get related events by time in another search

GEB — Thu, 27 Jun 2024 13:23:24 GMT

Thanks for the response - I expect about 5 results for each reference result -> so I set maxsearches=5. However, nothing I have tried produces any results. Boss? You mean team SME? Don't actually have one of those, we are in a help yourself environment.
Everything I've done with the above query results an a msg that says "unable to run query", specifying the query after the map.

Re: Find event in one search, get related events by time in another search

bowesmana — Wed, 26 Jun 2024 01:20:34 GMT

So how are you expecting to correlate the 2 data sets? How do you find events with RELATED_VAL that are related to the row containing REFERENCE_VAL

i.e. if the data is

reference_val_1
related_val_1
reference_val_2
related_val_2
related_val_3
reference_val_3
related_val_4

how do you expect to correlate related_val_3 with any of the 3 reference vals is it simply time proximity and if so, can you have interleaved reference_vals that may be in the same time window?

Can you give an example of data - otherwise the requirements are too vague

Re: Find event in one search, get related events by time in another search

GEB — Thu, 27 Jun 2024 13:19:08 GMT

Perhaps a better title would be: "Find an error in one system and then find errors close in time in a 2nd system". In my case, both search strings include the word 'Error' and the values are text to indicate what the errors are about.

Two Searches:

index=first_index sourcetype=first_source error 500 | rex field=_raw "string(?<REF_VAL>\d+)" | table _time REF_VAL

Output:
_time REF_VAL
2024-06-2024 10:48:04.003 Avalue

index=second_index soucetype=second_souce error somestring | rex field=_raw "ERROR - (?<ERR_MTHD>\S+)" | table _time ERR_MTHD

Output:
_time ERR_MTHD
2024-06-24 10:48:51.174 Method1text
2024-06-24 10:48:51:158 Method2text

Output that I would like:
EVENT_TIME REFERENCE_VAL RELATED_TIME RELATED_VAL
2024-06-2024 10:48:04.003 Avalue 2024-06-24 10:48:51.174 Method1text
2024-06-2024 10:48:04.003 Avalue 2024-06-24 10:48:51:158 Method2text