How to extract daily execution and historical daily executions from AWI 12.2.0

[Gokul Krishnan]

Hello everyone,

We are in the process of taking a count of daily executions of objects in Automic which includes Jobs, Workflows, Events, etc. Can someone help on how to extract those information from Automic AWI. There may be jobs which runs multiple times in a day, we would like to extract the total number of objects executing in a day.

Automic version 12.2.0

Please help. Thanks.

Regards,
Gokul Krishnan

[Pete Wirfs]

If you want to build your own extractor, the database table that contains run history is "AH".  It joins to the object header table "OH" based upon column AH_OH_Idnr.  The OH table provides you with the object name and the object type.

This reporting utility has the ability to write the selected run history into csv format;
https://docs.automic.com/documentation/webhelp/english/ALL/components/DOCU/12.3/Automic%20Automation%20Guides/help.htm#Utilities/admin_AEDBReportingTool_About.htm?TocPath=Reference%257CUtilities%257C_____6




------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
------------------------------

[Gokul Krishnan]

Hi Pete,
I am not really sure how to do it. I was going through the link you provided, is the query mentioned can be configured on any objects on the Automic GUI?

What type of job needs to be created in Automic to query the results?

Please note, I don't have access to the Automic servers and Database so am looking for a way to extract these information directly from the Automic GUI for audit purpose. Putting the filters is a manual task as I cannot see executions beyond 500(Don't have the option to change the limit in settings).

Thanks,
Gokul

[Pete Wirfs]

If your only experience with the product is as a user of the AWI, then this is a very difficult task.  (You can increase the 500 restriction yourself under personal settings, but it won't allow more than 999.)

The way I would approach this would be to run my own SQL query statement against the database.  This can also be done from a VARA.SEC_SQLI object within the AWI.  If you think you would be comfortable doing so, I could post some SQL for you to try using that method?

------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
------------------------------

[Pete Wirfs]

Here is a SQL to get things started.  SQL can also be used to compute summary counts.  But you should familiarize yourself with the results from this SQL first to get a feeling for what sort of data is available.

select oh_client as Client
, oh_otype as ObjectType
, oh_name as ObjectName
, ah_status as FinalStatus
, ah_timestamp1 as ActivationTime
, ah_timestamp2 as StartTime
, ah_timestamp4 as EndTime
from oh, ah
where oh_idnr = ah_oh_idnr
and oh_client = ah_client
and ah_timestamp2 > '2021-02-22'

------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
------------------------------

[Gokul Krishnan]

Hi Pete,

Thanks for your quick response.

Hope you're referring to VARA.SEC_SQL from Automic AWI? Attaching screenshot. I am not sure what login should we select to get the results. Can we select default login?

[Pete Wirfs]

Nope, wrong type of object.  There is a VARA.SEC_SQLI that you need to use.  The trailing "I" is important.  It takes you into a similar looking object that automatically connects to the AE database. I just remembered however that your administrators may have decided to restrict access to the AE database, and therefore the SQLI objects may not be available to you personally. So you may be running into site-defined restrictions, and you may need to ask for more access.

------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
------------------------------

[Gokul Krishnan]

Hi Pete,

I found the VARA.SEC_SQLI object. I just need to enter the query statements in the SQL server statement column and click preview to get the appropriate results?



Thanks,
Gokul

[Pete Wirfs]

Yes, the preview button will return your data query results to you.

------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
------------------------------

[Pete Wirfs]

don't panic when you see that the timestamps don't seem to make sense.  The timestamps that are returned are all stored in the database as greenwich time, and have to be adjusted to your timezone for them to make sense.

------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
------------------------------

[Gokul Krishnan]

Hi Pete,

Appreciate your valuable helps.

I got some results on the screen after clicking the preview button, please see the screenshot below. How do we export this data? I don't see an option to export it. Our primary goal is to calculate the average daily executions (JOBS, JOBP, JSCH, EVNT, CALL) and historical daily executions.

We are also trying to pull the active executable objects count from the Automic.


Thanks,
Gokul

[Gokul Krishnan]

I have used the following sql query as @Pete Wirfs has suggested and we received the no of executions on a particular day. I am not sure how to export this data, I can copy paste it but as it's huge it doesn't allow all the lines to be copy pasted at once and it's more of a manual task.

Can you help me with how to extract the list of jobs & jobp that are active in prod? Not the jobs that are currently running/active.

select oh_client as Client
, oh_otype as ObjectType
, oh_name as ObjectName
, ah_status as FinalStatus
, ah_timestamp1 as ActivationTime
, ah_timestamp2 as StartTime
, ah_timestamp4 as EndTime
from oh, ah
where oh_idnr = ah_oh_idnr
and oh_client = ah_client
and ah_timestamp2 >= '2021-02-14 00:00:00'
and ah_timestamp4 <= '2021-02-14 23:59:59'
and ah_otype in ('JOBS','JOBP','EVNT','CALL')​

Thanks,
Gokul

[Pete Wirfs]

To transfer a large SQL resultset from a VARA to a CSV, create a new SCRI object to run this code, and modify it to match your requirements;

! CAPTURE THE NAME OF THE UC4 HOST WE ARE RUNNING ON
:set &HOST# = get_att(host)

! RUN THE DATABASE QUERY INTO A RESULT SET
:SET &HND# = PREP_PROCESS_VAR(EXAMPLE.SQL.TO.CSV.QUERY)

! WRITE THE RESULT SET OUT TO A FLAT FILE
:SET &RET# = WRITE_PROCESS(&HND#,"\\targetpath\example_sql_to_csv.csv",&host#,myloginobject,OVERWRITE)

------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
------------------------------

[Pete Wirfs]

The "AH" table only holds history of completed tasks.
Change "AH" to "EH" to query currently active tasks.

Well written SQL statements can also return summary counts instead of long data lists. But doing this effectively depends a lot upon your knowledge of the data and a clear picture of what you want to learn from it.  For instance do you want your counts to be summarized by task type?  By day? by week? by month? by user? by type of completion status?  By how long the task ran? By what agent it ran against? Frequently I find myself having to summarize data on several of these data points at the same time.

------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
------------------------------

[Gokul Krishnan]

Hi Pete,

Our immediate requirement is to fetch the number of jobs and workflows that are active in Prod. We got a request to verify how many jobs and workflows that are scheduled to run in Prod, no matter if it's a daily job, weekly or monthly.

[Pete Wirfs]

If your shop uses JSCH schedule objects for scheduling tasks, then this SQL will produce a list of what is scheduled to run, and when.

select oh_client as client
, oh_name as SCHEDULE
, jpp_object as OBJECT
, JPP_ErlstStTime as STARTTIME
, jppc_calekeyname as CALENDAR
from oh
inner join jpp
on oh_idnr = jpp_oh_idnr
left outer join jppc
on jppc_jpp_lnr = jpp_lnr and jppc_oh_idnr = oh_idnr
where oh_otype = 'JSCH' and oh_deleteflag = 0 and oh_client = ###
order by 4,3,5;

​

------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
------------------------------