Splunk Search

Individual run times for database jobs

fisuser1
Contributor

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        
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

fisuser1
Contributor

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.

0 Karma

fisuser1
Contributor

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

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...