Individual run times for database jobs

[fisuser1]

I’m trying to find individual run times for specific jobs in our database. Each ‘job’ consists of two
‘sub-jobs’ that define start and end time for each. An example of this below would be start-time job PZEXINI991, with an end-time job of TTXPOSR1. I can get the total time between jobs with the search below (as ugly as it may be), however I still need to incorporate another job into this search that uses a different format, XFED_ORG_1. This specific job uses seconds (|1487818842000| and |1487818854000| from the example below) inline to define it’s start-time and end-times. Lost yet? My problem is I can create a search that shows the start-time and end-times of the ‘subjobs’, but the ‘transaction’ command I am using will not allow for the XFED_ORG_1 job to render/display in the report. Apologies since I know this is very complicated, but my database developers aren’t giving much to work with here.

Database Output:

            PP3100|mm/dd/year|#############|mm/dd/year|#############|3100BACHR1|PZEXINI991
            PP3100|mm/dd/year|#############|mm/dd/year|#############|3100BACHR1|TTXPOSR1

            PP3100|02/21/2017|1487687660000|02/21/2017|1487687668000|3100BACHR2|PZEXINI992
            PP3100|02/21/2017|1487691967000|02/21/2017|1487691968000|3100BACHR2|TTXPOSR2

            PP3100|mm/dd/year|#############|mm/dd/year|#############|3100BACHR3|PZEXINI993
            PP3100|mm/dd/year|#############|mm/dd/year|#############|3100BACHR3|TTXPOSR3

            PP3100|02/21/2017|1487707339000|02/21/2017|1487707347000|3100BACHR4|PZEXINI994
            PP3100|02/21/2017|1487715367000|02/21/2017|1487715368000|3100BACHR4|TTXPOSR4

            PP3100|mm/dd/year|#############|mm/dd/year|#############|3100BAR5|PZEXINI_R5
            PP3100|mm/dd/year|#############|mm/dd/year|#############|3100BAR5|TTXPOS_R5

            PP3100|mm/dd/year|#############|mm/dd/year|#############|3100BAR6|PZEXINI_R6
            PP3100|mm/dd/year|#############|mm/dd/year|#############|3100BAR6|TTXPOS_R6

    PP3100|02/22/2017|1487818842000|02/22/2017|1487818854000|3100BACHTR|XFED_ORG_1

Current Search:

sourcetype=PROFILE_DAYEND_STATS Client=PP3100 PZEXINI992 OR TTXPOSR2
| eval StartTime=if(UPROC="PZEXINI992",StartTime,null)  
| eval EndTime=if(UPROC="TTXPOSR2",EndTime,null)  
| eval Start=StartDate." ".strftime(StartTime/1000,"%H:%M:%S")  
| eval End=EndDate." ".strftime(EndTime/1000,"%H:%M:%S")
| append [search sourcetype=PROFILE_DAYEND_STATS Client=PP3100 PZEXINI994 OR TTXPOSR4
| eval StartTime=if(UPROC="PZEXINI994",StartTime,null)  
| eval EndTime=if(UPROC="TTXPOSR4",EndTime,null)  
| eval Start=StartDate." ".strftime(StartTime/1000,"%H:%M:%S")  
| eval End=EndDate." ".strftime(EndTime/1000,"%H:%M:%S")]
| append [search sourcetype=PROFILE_DAYEND_STATS Client=PP3100 PZEXINI991 OR TTXPOSR1
| eval StartTime=if(UPROC="PZEXINI991",StartTime,null)  
| eval EndTime=if(UPROC="TTXPOSR1",EndTime,null)  
| eval Start=StartDate." ".strftime(StartTime/1000,"%H:%M:%S")  
| eval End=EndDate." ".strftime(EndTime/1000,"%H:%M:%S")]
| append [search sourcetype=PROFILE_DAYEND_STATS Client=PP3100 PZEXINI993 OR TTXPOSR3
| eval StartTime=if(UPROC="PZEXINI993",StartTime,null)  
| eval EndTime=if(UPROC="TTXPOSR3",EndTime,null)  
| eval Start=StartDate." ".strftime(StartTime/1000,"%H:%M:%S")  
| eval End=EndDate." ".strftime(EndTime/1000,"%H:%M:%S")]
| append [search sourcetype=PROFILE_DAYEND_STATS Client=PP3100 PZEXINI_R5 OR TTXPOS_R5
| eval StartTime=if(UPROC="PZEXINI_R6",StartTime,null)  
| eval EndTime=if(UPROC="TTXPOS_R5",EndTime,null)  
| eval Start=StartDate." ".strftime(StartTime/1000,"%H:%M:%S")  
| eval End=EndDate." ".strftime(EndTime/1000,"%H:%M:%S")]
| append [search sourcetype=PROFILE_DAYEND_STATS Client=PP3100 PZEXINI_R6 OR TTXPOS_R6
| eval StartTime=if(UPROC="PZEXINI_R6",StartTime,null)  
| eval EndTime=if(UPROC="TTXPOS_R6",EndTime,null)  
| eval Start=StartDate." ".strftime(StartTime/1000,"%H:%M:%S")  
| eval End=EndDate." ".strftime(EndTime/1000,"%H:%M:%S")]
| append [search sourcetype=PROFILE_DAYEND_STATS Client=PP3100 XFED_ORG_1
| eval Start=StartDate." ".strftime(StartTime/1000,"%H:%M:%S")  
| eval End=EndDate." ".strftime(EndTime/1000,"%H:%M:%S")]
| transaction startswith="UPROC=PZEXINI*" endswith="UPROC=TTXPOS*"
| eval seconds=(EndTime-StartTime)/1000 
| eval mins=(seconds/60) 
| eval Process=Session 
| eval Tasks=UPROC 
| table ClientName, StartDate, Start, End, Process, Tasks, mins, seconds
| sort StartDate

Search Output:

ClientName  StartDate   Start               End                        Process            Tasks          mins             seconds
Joe's   2/17/2017   2/17/2017 15:02 2/17/2017 17:16 3100BACHR4  PZEXINI994  133.566667  8014
                    TTXPOSR4        
Joe's   2/21/2017   2/21/2017 15:02 2/21/2017 17:16 3100BACHR4  PZEXINI994  133.816667  8029
                    TTXPOSR4        
Joe's   2/21/2017   2/21/2017 9:34  2/21/2017 10:46 3100BACHR2  PZEXINI992  71.8    4308
                    TTXPOSR2        
Joe's   2/23/2017   2/23/2017 15:02 2/23/2017 17:16 3100BACHR4  PZEXINI994  133.85  8031
                    TTXPOSR4        
Joe's   2/23/2017   2/23/2017 11:32 2/23/2017 13:46 3100BACHR3  PZEXINI993  134.066667  8044
                    TTXPOSR3        
Joe's   2/23/2017   2/23/2017 9:34  2/23/2017 10:46 3100BACHR2  PZEXINI992  71.883333   4313
                    TTXPOSR2        

[somesoni2 · Accepted solution]

Give this a try

sourcetype=PROFILE_DAYEND_STATS Client=PP3100 UPROC="PZEXINI*" OR UPROC="TTXPOS*" OR UPROD="XFED_ORG_1"
| eval StartTime=if(like(UPROC,"PZEXINI%") OR UPROD="XFED_ORG_1",StartTime,null) 
| eval EndTime=if(like(UPROC,"TTXPOS%") OR UPROD="XFED_ORG_1",EndTime,null) 
| eval Start=StartDate." ".strftime(StartTime/1000,"%H:%M:%S") 
| eval End=EndDate." ".strftime(EndTime/1000,"%H:%M:%S")]
| transaction startswith="UPROC=PZEXINI*" endswith="UPROC=TTXPOS*" keeporphan=t
| eval seconds=(EndTime-StartTime)/1000 
| eval mins=(seconds/60) 
| eval Process=Session 
| eval Tasks=UPROC 
| table ClientName, StartDate, Start, End, Process, Tasks, mins, seconds
| sort StartDate

[DalJeanis]

hanging close brace on line 5, s missing from keeporphans on line 6

[fisuser1]

SO MUCH nicer than my search, thank you. I do get the same result though. This creates the output I am looking for, however still does not include the XFED_ORG_1 that I have been experiencing in my old ugly search.

[fisuser1]

This is my bad. I totally typo'ed the XFED_ORG_1 UPROC. I made the adjustments and it works perfectly. Thank you very much for cleaning up that query for me!

sourcetype=PROFILE_DAYEND_STATS Client=PP3100 UPROC="PZEXINI*" OR UPROC="TTXPOS*" OR UPROC="XFED_ORG_1"
| eval StartTime=if(like(UPROC,"PZEXINI%") OR UPROC="XFED_ORG_1",StartTime,null)
| eval EndTime=if(like(UPROC,"TTXPOS%") OR UPROC="XFED_ORG_1",EndTime,null)
| eval Start=StartDate." ".strftime(StartTime/1000,"%H:%M:%S")
| eval End=EndDate." ".strftime(EndTime/1000,"%H:%M:%S")
| transaction startswith="UPROC=PZEXINI*" endswith="UPROC=TTXPOS*" keeporphans=t
| eval seconds=(EndTime-StartTime)/1000
| eval mins=(seconds/60)
| eval Process=Session
| eval Tasks=UPROC
| table ClientName, StartDate, Start, End, Process, Tasks, mins, seconds
| sort StartDate