Joining two searches based on a common field

[rajatsinghbagga]

Hello Everyone,

I have two search queries which are working as expected but when I trying to join both these queries it not giving the expected results.

First Query :- Getting the current runtime of all the active processes

index=process_log (MSGNUM="START-PROCESS") AND JOBID="JOB*" 
| transaction JOBID JOBNAME keepevicted=1 keeporphans=1 
| where eventcount==1 
| where searchmatch("START-PROCESS") 
| eval Runtime = now() - _time
| eval Runtime = round(Runtime,2)
| table  _time JOBNAME  Runtime

Second Query :- Getting the average runtime of all the active processes from now to past one week

index=process_log (MSGNUM="START-PROCESS" OR MSGNUM="END-PROCESS") AND JOBID="JOB*"  earliest=-1w latest=now()
| transaction JOBID JOBNAME keepevicted=1 startswith=START-PROCESS endswith=END-PROCESS
| stats avg(duration) as AverageRuntime by JOBNAME
| eval AverageRuntime = round(AverageRuntime,2)

Join Query based on JOBNAME

index=process_log  (MSGNUM="START-PROCESS" OR MSGNUM="END-PROCESS") AND JOBID="JOB*" 
| transaction JOBID JOBNAME keepevicted=1 keeporphans=1 
| where eventcount==1 
| where searchmatch("START-PROCESS") 
| eval Runtime = now() - _time 
| eval Runtime = round(Runtime,2)
| join type=left max=0 JOBNAME [ search index=process_log  START-PROCESS OR END-PROCESS earliest=-1w latest=now() 
  | transaction JOBID JOBNAME keepevicted=1 startswith=START-PROCESS endswith=END-PROCESS
  | stats avg(duration) as AverageRuntime by JOBNAME] 
| table JOBNAME Runtime AverageRuntime

No values are coming for AverageRuntime, Runtime is getting displayed as per JOBNAME. I not sure why no values are coming for AverageRuntime

Please assist me , Thank you

[woodcock]

Like this:

| makeresults 
| eval raw="13-08-2019 23:46:30    JOBNAME:JOB1 JOBID:JOB1234  MSGNUM:START-PROCESS:::13-08-2019 23:16:00    JOBNAME:JOB1 JOBID:JOB1234  MSGNUM:END-PROCESS:::13-08-2019 22:45:30    JOBNAME:JOB1 JOBID:JOB4567  MSGNUM:START-PROCESS:::13-08-2019 21:55:00    JOBNAME:JOB1 JOBID:JOB4567  MSGNUM:END-PROCESS:::13-08-2019 21:24:30    JOBNAME:JOB1 JOBID:JOB5678  MSGNUM:START-PROCESS:::13-08-2019 20:44:00    JOBNAME:JOB1 JOBID:JOB5678  MSGNUM:END-PROCESS:::13-08-2019 20:13:30    JOBNAME:JOB1 JOBID:JOB6789  MSGNUM:START-PROCESS:::13-08-2019 19:43:00    JOBNAME:JOB2 JOBID:JOB7891  MSGNUM:START-PROCESS:::13-08-2019 19:02:20    JOBNAME:JOB2 JOBID:JOB7891  MSGNUM:END-PROCESS:::13-08-2019 18:22:00    JOBNAME:JOB3 JOBID:JOB8912  MSGNUM:START-PROCESS:::13-08-2019 17:51:30    JOBNAME:JOB3 JOBID:JOB8912  MSGNUM:END-PROCESS:::13-08-2019 17:18:10    JOBNAME:JOB3 JOBID:JOB8913  MSGNUM:START-PROCESS:::13-08-2019 16:07:40    JOBNAME:JOB3 JOBID:JOB8913  MSGNUM:END-PROCESS:::13-08-2019 15:43:20    JOBNAME:JOB3 JOBID:JOB8914  MSGNUM:START-PROCESS:::13-08-2019 15:09:00    JOBNAME:JOB3 JOBID:JOB8914  MSGNUM:END-PROCESS:::13-08-2019 14:38:30    JOBNAME:JOB3 JOBID:JOB8915  MSGNUM:START-PROCESS:::12-08-2019 22:21:10    JOBNAME:JOB3 JOBID:JOB8916  MSGNUM:START-PROCESS:::12-08-2019 21:57:30    JOBNAME:JOB3 JOBID:JOB8916  MSGNUM:END-PROCESS:::11-08-2019 20:49:00    JOBNAME:JOB3 JOBID:JOB8917  MSGNUM:START-PROCESS:::11-08-2019 20:14:50    JOBNAME:JOB3 JOBID:JOB8917  MSGNUM:END-PROCESS:::11-08-2019 17:42:20    JOBNAME:JOB1 JOBID:JOB1435  MSGNUM:START-PROCESS:::11-08-2019 17:11:50    JOBNAME:JOB1 JOBID:JOB1435  MSGNUM:END-PROCESS:::10-08-2019 21:22:20    JOBNAME:JOB3 JOBID:JOB8918  MSGNUM:START-PROCESS:::10-08-2019 20:52:10    JOBNAME:JOB3 JOBID:JOB8918  MSGNUM:END-PROCESS:::09-08-2019 14:52:40    JOBNAME:JOB1 JOBID:JOB8919  MSGNUM:START-PROCESS:::09-08-2019 14:12:30    JOBNAME:JOB1 JOBID:JOB8919  MSGNUM:END-PROCESS:::08-08-2019 14:19:00    JOBNAME:JOB3 JOBID:JOB8999  MSGNUM:START-PROCESS:::08-08-2019 13:28:50    JOBNAME:JOB3 JOBID:JOB8999  MSGNUM:END-PROCESS:::08-08-2019 15:19:00    JOBNAME:JOB2 JOBID:JOB1999  MSGNUM:START-PROCESS:::08-08-2019 15:28:50    JOBNAME:JOB2 JOBID:JOB1999  MSGNUM:END-PROCESS"
| makemv delim=":::" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<_time>.*?)\s+JOBNAME:(?<JOBNAME>\S+)\s+JOBID:(?<JOBID>\S+)\s+MSGNUM:(?<which>\S+?)\-"
| eval _time = strptime(_time, "%d-%m-%Y %H:%M:%S")
| sort 0 - _time

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution."

| stats min(_time) AS _time values(JOBNAME) AS JOBNAME range(_time) AS runtime values(which) AS whiches count BY JOBID
| eval runtime = if(runtime==0.0, now() - _time, runtime)

| rename COMMENT AS "INSERT YOUR LOGIC HERE, e.g."
| where ((count == 1) AND (whiches == "START"))

| stats avg(runtime) BY JOBNAME

[woodcock]

DO NOT USE the transaction command; try this:

index=process_log AND
((MSGNUM="START-PROCESS" OR MSGNUM="END-PROCESS") AND JOBID="JOB*")) OR
(START-PROCESS OR END-PROCESS earliest=-1w latest=now)
| stats min(_time) AS _time range(_time) AS duration list(_raw) AS events BY JOBID JOBNAME
| eval duration = if(duration==0, now() - _time, duration)
| eventstats avg(duration) as AverageRuntime by JOBNAME
| table _time JOBNAME JOBID Runtime AverageRuntime

[rajatsinghbagga]

Thanks Woodcock, I am not sure from where are you getting the value for Runtime in the above query. Runtime is the spanned time of a currently running process(active).

[woodcock]

Does it work or not? Duration is the distance between all events, unless there is only 1 event, then it is the distance between that event and now().

[woodcock]

Keep in mind that if you fail to give us sample data, then we have NO WAY to ensure that our answers are complete. Even so, our answers are always useful to show you the gist of what we mean and you should be able to learn the approach and modify the answers to more fully suit your data/situation.

[rajatsinghbagga]

Thanks for the advice woodcock, Please refer to the sample data below

13-08-2019 23:46:30 JOBNAME:JOB1 JOBID:JOB1234  MSGNUM:START-PROCESS
13-08-2019 23:16:00 JOBNAME:JOB1 JOBID:JOB1234  MSGNUM:END-PROCESS
13-08-2019 22:45:30 JOBNAME:JOB1 JOBID:JOB4567  MSGNUM:START-PROCESS
13-08-2019 21:55:00 JOBNAME:JOB1 JOBID:JOB4567  MSGNUM:END-PROCESS
13-08-2019 21:24:30 JOBNAME:JOB1 JOBID:JOB5678  MSGNUM:START-PROCESS
13-08-2019 20:44:00 JOBNAME:JOB1 JOBID:JOB5678  MSGNUM:END-PROCESS
13-08-2019 20:13:30 JOBNAME:JOB1 JOBID:JOB6789  MSGNUM:START-PROCESS
13-08-2019 19:43:00 JOBNAME:JOB2 JOBID:JOB7891  MSGNUM:START-PROCESS
13-08-2019 19:02:20 JOBNAME:JOB2 JOBID:JOB7891  MSGNUM:END-PROCESS
13-08-2019 18:22:00 JOBNAME:JOB3 JOBID:JOB8912  MSGNUM:START-PROCESS
13-08-2019 17:51:30 JOBNAME:JOB3 JOBID:JOB8912  MSGNUM:END-PROCESS
13-08-2019 17:18:10 JOBNAME:JOB3 JOBID:JOB8913  MSGNUM:START-PROCESS
13-08-2019 16:07:40 JOBNAME:JOB3 JOBID:JOB8913  MSGNUM:END-PROCESS
13-08-2019 15:43:20 JOBNAME:JOB3 JOBID:JOB8914  MSGNUM:START-PROCESS
13-08-2019 15:09:00 JOBNAME:JOB3 JOBID:JOB8914  MSGNUM:END-PROCESS
13-08-2019 14:38:30 JOBNAME:JOB3 JOBID:JOB8915  MSGNUM:START-PROCESS
12-08-2019 22:21:10 JOBNAME:JOB3 JOBID:JOB8916  MSGNUM:START-PROCESS
12-08-2019 21:57:30 JOBNAME:JOB3 JOBID:JOB8916  MSGNUM:END-PROCESS
11-08-2019 20:49:00 JOBNAME:JOB3 JOBID:JOB8917  MSGNUM:START-PROCESS
11-08-2019 20:14:50 JOBNAME:JOB3 JOBID:JOB8917  MSGNUM:END-PROCESS
11-08-2019 17:42:20 JOBNAME:JOB1 JOBID:JOB1435  MSGNUM:START-PROCESS
11-08-2019 17:11:50 JOBNAME:JOB1 JOBID:JOB1435  MSGNUM:END-PROCESS
10-08-2019 21:22:20 JOBNAME:JOB3 JOBID:JOB8918  MSGNUM:START-PROCESS
10-08-2019 20:52:10 JOBNAME:JOB3 JOBID:JOB8918  MSGNUM:END-PROCESS
09-08-2019 14:52:40 JOBNAME:JOB1 JOBID:JOB8919  MSGNUM:START-PROCESS
09-08-2019 14:12:30 JOBNAME:JOB1 JOBID:JOB8919  MSGNUM:END-PROCESS
08-08-2019 14:19:00 JOBNAME:JOB3 JOBID:JOB8999  MSGNUM:START-PROCESS
08-08-2019 13:28:50 JOBNAME:JOB3 JOBID:JOB8999  MSGNUM:END-PROCESS
08-08-2019 15:19:00 JOBNAME:JOB2 JOBID:JOB1999  MSGNUM:START-PROCESS
08-08-2019 15:28:50 JOBNAME:JOB2 JOBID:JOB1999  MSGNUM:END-PROCESS

Expected output for the current day 13-08-2019

Active-Job      Runtime                                          Average-runtime-over-last-week
JOB1                now() - 13-08-2019 20:13:30        00:38:26 
JOB3                now() - 13-08-2019 14:38:30        00:39:04

Note JOB2 should not appear in the output as it is presently not active.

Regards Rajat

[woodcock]

Great, see my new answer.

[gcusello · Accepted solution]

Hi rajatsinghbagga,
at first you have to check how many results you have in the second query because there's a limit of 50,000 results in subqueries, so maybe this is the problem.
In addition, transaction and join aren't performant commands, so it's better to replace with stats command, somethimes like this:
First Search:

index=process_log (MSGNUM="START-PROCESS") AND JOBID="JOB*" 
| stats earliest(_time) AS _time values(MSGNUM) AS MSGNUM count BY JOBID JOBNAME 
| search count=1  
| eval Runtime = now() - _time
| eval Runtime = round(Runtime,2)
| table  _time JOBNAME  Runtime

Second Search:

index=process_log (MSGNUM="START-PROCESS" OR MSGNUM="END-PROCESS") AND JOBID="JOB*"  earliest=-1w latest=now()
| stats earliest(_time) AS earliest latest(_time) AS latest values(MSGNUM) AS MSGNUM count BY JOBID JOBNAME
| eval duration=latest-earliest
| stats avg(duration) as AverageRuntime by JOBNAME
| eval AverageRuntime = round(AverageRuntime,2)

Full search:

index=process_log (MSGNUM="START-PROCESS") AND JOBID="JOB*" 
| stats earliest(_time) AS earliest latest(_time) AS latest values(MSGNUM) AS MSGNUM count BY JOBID JOBNAME 
| eval Runtime = now() - earliest
| eval Runtime = round(Runtime,2)
| eval duration=latest-earliest
| stats avg(duration) as AverageRuntime values(earliest) AS _time values(Runtime) AS Runtime by JOBNAME
| eval AverageRuntime = round(AverageRuntime,2), _time=strftime(_time,"%m/%d/%Y %H:%M:%S")
| table  _time JOBNAME Runtime AverageRuntime

I cannot test it but it should run!

Bye.
Giuseppe

[rajatsinghbagga]

Thank you gcusello,

First query -- All Good , Second query -- All Good , However in the Third query which is the combination of First and Second query I wanted to view all the active processes, the current runtime of this active process from now, average runtime of the same process(inactive) by looking back for one week . I am not getting it from your full search. In order to calculate the duration of a inactive process (process which is already completed) i was looking at MSGNUM="END-PROCESS" which you haven't used in your full search also i am not sure if you are actually looking for a last weeks (-1w) data to calculate the average of an already completed process. I guess that's where we need to have a Join..?

The first two queries are working as expected with the stats logic you showed. Thank you for sharing that.

[gcusello]

Good,
I suggest to modify my search using your rules.
This approach is much faster than the previous (using Job Inspector).
Bye.
Giuseppe

[rajatsinghbagga]

Thank you Giuseppe , you are a genius 🙂 without even asking for the sample data you were able to provide these queries .. amazing!!. I will use join to combine the first two queries as suggested by you and achieve the required output. Just for your reference, I have provided the sample data in response to the comment from woodcock below. Regards Rajat

[gcusello]

Hi rajatsinghbagga,
too good!
if this answer solves your problems, please, accept and/ot upvote it.
See next time.
Bye.
Giuseppe