Get information of the job status success, fail, pending, ...

[PhatTran612036]

Hello,

Can somebody tell me how do we can get the job status of automic UC4? I have the access to its database and I found this document:
https://docs.automic.com/documentation/webhelp/english/ALL/components/AE/11.1/DB%20Schema/db/_structure/HTML/index.htm#ECC/ECC_General/ecc_login.htm%3FTocPath%3DAbout%2520the%2520Enterprise%2520Control%2520Center%7C_____2

But I don't know exactly the table where the database stores the status of UC4 jobs.

If it stores the job status in the database, how many status do we have, such as success, fail, pending, long running, ....

Regards,
Phat

[Anon Anon]

What do you want to do with the status information?

Here is a list of possible Stati of jobs
https://docs.automic.com/documentation/WEBHELP/English/AWA/11.2/AE/11.2/All%20Guides/help.htm#ucaaiy.htm?Highlight=return%20codes

There are different ways of getting a job status via Automic script:

https://docs.automic.com/documentation/WEBHELP/English/AWA/11.2/AE/11.2/All%20Guides/help.htm#ucaafo.htm%3FTocPath%3DAutomation%2520Engine%2520Script%2520Guide%7COrdered%2520by%2520Function%7CActivation%2520Data%7C_____4

https://docs.automic.com/documentation/WEBHELP/English/AWA/11.2/AE/11.2/All%20Guides/help.htm#ucaces.htm%3FTocPath%3DAutomation%2520Engine%2520Script%2520Guide%7COrdered%2520by%2520Function%7CRead%2520or%2520Modify%2520Objects%7C_____26

Querying the DB "just for fun" and without need is never recommended.

[PhatTran612036]

Hi,

I need to get the status of job in the UC4, then I can use Zabbix to monitor it. With your link above, no sql query. We need to use automic script.

[Anon Anon]

Yes, with both script commands you will get the status of a Job.

[PhatTran612036]

Can we have the sql query to get the status of it?

[Shravya Devadiga]

Is there any Automic script to get the alert notification of the jobs ending with not ok, failing, aborted?

[Marilyn Singley]

EH is for Current
AH is for recent
OH is for Old 

I use several different ways to get the Status of a job. Here's an example of an easy way to do it. 

One is to create a SQLI query.. then process the query with a job to send an email:

Here's an example of a query named "VARA.SQLI.REPORTING_JOB_ABENDED" for currently "ended not ok" jobs. 

select EH_NAME, EH_EndTime from EH where EH_Status = 1800
and EH_name like '%JOBS%'
Order by EH_EndTime

See the below for a list of Status Codes, just change the Status from 1800 to what you are looking for from the list of status codes:
System Return Codes of Executable Objects (automic.com)

Second part 
If you want to send an email -- Create a Job object, Prep Process the SQLI vara you created, Print Process the Columns you want to send in an email, and use Send_Mail to send it to an email address. 

See below example in the Process tab of a JOB Object you would create: 

:SET &NL# = UC_CRLF
:SET &HND#=PREP_PROCESS_VAR(VARA.SQLI.REPORTING_JOB_ABENDED)
:PROCESS &HND#
! get first column value of every line
: SET &RES# = GET_PROCESS_LINE(&HND#, 2)
! get second column value of every line
: SET &COL1# = GET_PROCESS_LINE(&HND#, 3)
: SET &SCRIPTVAR# = "&SCRIPTVAR# &NL# &RES# &COL1# "
:ENDPROCESS
:CLOSE_PROCESS &HND#
:PRINT &SCRIPTVAR#

: SET &RET# = SEND_MAIL("[e-mail]", , "Abended Prod Jobs", "Abended Prod Jobs &SCRIPTVAR#")

You can search the documentation on how to use all the above. 
PREP_PROCESS_VAR (automic.com)
SEND_MAIL (automic.com)
SQLI VARA Objects (automic.com)

Once you have begun creating and familiarizing yourself with SQLI Vara objects and jobs, you can search this community for specifics. There are a lot of good examples and advice here. 

[Mike Halliday]

Just a clarification on the SQL tables:
OH is Object Header and contains an entry for every Object in the system - this is the most important table in the database.
EH is Executive Header and contains all the current executions (Activities)
AH is Archive Header and contains the completed (Archived) executions.
RH is Report Header and contains all the report header data - this is related to the AH table
RT is Report Content and contains the output from the execution - this is related to the AH table

DB Schema can be found here

[Ashmitha Jayaram]

Hi Marilyn,

As per your suggestion I run below SQL query,

select SYSDATE as SYSTEMDATE, EH_NAME, EH_EndTime from UC4.EH where EH_Status = 1800
and EH_name like '%JOBS%' Order by 1

But I received the output of complete one-day executions of JOBS (including ended ok jobs).
I need only the report of ended not ok jobs.

How can I get it? Will you please help me to fetch the report of ended not ok Jobs?

Regards,
Ashmitha.

[B Bharath Kumar Reddy]

Hi Ashmitha,

You should also consider the deactivated jobs. If you want the list of failed jobs for the last 24 hours, you should also probably look in the AH table which contains the deactivated tasks.

Also, you can try using EH_Otype='JOBS' or AH_Otype='JOBS' which will fetch only the Job type tasks.

------------------------------
Bharath B.
------------------------------

[Markus Embacher]

Hi,
as Mike pointed out selecting from EH will just return executions still visible in the Process Monitoring. If you would like to select all executions of a day then please query from AH table. Make sure you include the AH_Client in your query and also to limit the result based on a timestamp (AH_TimeStamp1 is the activation time).
The documentation of the table can be found here:
https://docs.automic.com/documentation/webhelp/english/AA/12.3/AE/12.3/DB%20Schema/db/_structure/HTML/AH.html
https://docs.automic.com/documentation/webhelp/english/AA/21.0/AE/21.0/DB%20Schema/db/_structure/HTML/AH.html

For ENDED_NOT_OK query AH_Status=1800
For the full list refer to:
https://docs.automic.com/documentation/WEBHELP/English/all/components/DOCU/21.0.4/Automic%20Automation%20Guides/Content/AWA/Executions/ReturnCodesExec.htm

For monitoring you can also define an SLA object to send a trigger to your monitoring application anytime a job fails.

Regards, Markus