Look up the details of the job that wrote a particular log file

Discussion created by Michael_Lowry on Mar 10, 2016
Latest reply on Aug 26, 2017 by Michael_Lowry

We occasionally discover that the /var file system on a server is filling up due to large UC4 job log files in the agent/out directory. I wrote an Oracle SQL statement to list the details of the original job that wrote a log, and the last user who edited this job.

with orig_job as
select AH_OH_Idnr as Job_Run_ID, AH_Alias as Job_Alias, OH_Name as Job_Name,
from AH,OH
where AH_OH_Idnr = OH_Idnr
and AH_Idnr =
select AH_ParentAct from AH
where AH_OType = 'REPORT'
and AH_HostSrc = ?
and AH_FileNameSrc like ?

user_details as
select OH_Idnr, OH_Name as User_ID, USR_FirstName, USR_LastName,
from USR,OH
where USR_OH_IDNr = OH_Idnr

select Job_Run_ID, Job_Alias, Job_Name, User_ID, USR_FirstName, USR_LastName,
from orig_job,user_details
where orig_job.OH_ModUserIDNr = user_details.OH_Idnr

The bind parameters are:

  1. Agent name, e.g.,CHRVL360
  2. Job log file with leading SQL wildcard, e.g.,%OADWUPSA.TXT

I put this into an SQLI, and called it from a UC4 script that parses and prints the output. I then put the script into a workflow with a prompt set that prompts for the two input parameters. Our support team can now use this workflow to quickly identify both the job that created a given log file, and the job’s likely owner.


Here is an example of the output of this script:

Job that wrote report file OADWUPSA.TXT:
Run ID       : 2323174
Task alias   : UC0.MAL.TEST1#1.JOBS_UNIX
Object name  : UC0.MAL.TEST1#1.JOBS_UNIX

User who last edited this job:
User ID      : XYZ789/CORP
User name    : John Smith
Email addr.  : John_Smith@mycompany123.com
LDAP DN      : CN=XYZ789,OU=XYZ,OU=EMEA,OU=Staff,DC=corp,DC=mycompany123,DC=com
Last session : 2016-03-10 06:13:33.000

I’ve attached an XML export of this workflow and its constituent objects.