Thanks @kamlesh_vaghela i accepted the answer. However I noticed that I am not able to apply color codes on the output, I can see the option in the column to format based on the colour codes but when I apply it does nothing. Is there a way I can color the cells of "elapseJobTime" column based on the high and low values appearing in this column... I was able to do it with the normal table values but it seem getting these values in the list format is causing this issue.. Anything if you can suggest that will be great.. Thanks Rajat ... View more

This worked - Perfect !! Thank you Rajat ... View more

Hello @dpeukert This didn't worked. I am getting "Error in 'stats' command: The argument 'limit=3' is invalid." Thanks Rajat ... View more

The output is coming as below along with the list of all the values in the fields tJOBID tJOBNAME telapseJobTime Multivalue Multivalue Multivalue From the previous query with mvrange the output was desc JOBID JOBNAME elapseJobTime desc1 desc2 ... View more

Thanks @kamlesh_vaghela , Just tried it now. I am only getting the "desc" values and rest of the fields are coming blank. ... View more

Hello @diogofgm , I was able to figure out that basically the JOBNAME is multivalue filed which contained both the global job XX along with actual jobs AB or DE so i just picked the actual job from the JOBNAME field using mvindex() function and then ran the stats on the actual job . This finally gave me the expected results. Thank you very much !! ... View more

Thanks @diogofgm , your SPL works like a charm, Thank you very much. But somewhere in my actual data I have a global job which is associated with every actual job and it shares the same JOBID . Because of this i am always getting this global JOB as XX with every instance of actual JOB AB or DE for this example. Since this global job shares the same JOBID as the actual jobs so I issued | dedup JOBID before the last stats command in your SPL. And i am getting the results as AB 2 XX 2 DE 1 XX 2 I also tried to give | where NOT JOB == "XX" but then it gives me no results. is there any way i can suppress/hide XX for the results?? Thank you very much ... View more

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

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

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). ... View more

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