Here is the SQLServer query that I use for listing all executions that were running within a time period;
select oh_name as Name
, ah_alias as Alias
, oh_otype as Type
--, convert(datetime, ah_timestamp1 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') as Acivation
, convert(datetime, ah_timestamp2 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') as Start_Time
, convert(datetime, ah_timestamp4 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') as end_time
, ah_runTime as Duration_Seconds
, ah_client as client
, ah_Hostdst as Host
, ah_queue as Queue
, ah_idnr as RunID
, ah_status Status
, (select Y.oh_name from uc4.dbo.oh Y where y.oh_idnr = b.ah_usr_idnr) as Uc4User
from uc4.dbo.oh A
, uc4.dbo.ah B
where oh_idnr = ah_oh_idnr
--and oh_name like 'objectname%' -- and ah_status <> 1930 and ah_status <> 1933 --and ah_status <> 1900
and not oh_otype in('JOBP', 'HOST', 'SYNC', 'JSCH', 'SERV', 'CLNT', 'QUEUE', 'USER')
and convert(datetime, ah_timestamp4 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') > '2021-05-01 11:00:00.000' --FROM
and convert(datetime, ah_timestamp2 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') < '2021-05-01 12:00:00.000' --TO
order by 4
------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
------------------------------
Original Message:
Sent: 06-16-2021 12:34 PM
From: Rahul
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
Hi @Pete Wirfs Can you help with a query to list all job executions for a particular agent within a time period?
Original Message:
Sent: 06-04-2021 07:01 PM
From: Pete Wirfs
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
Some agents, particularly SQL agents, will pick up their login information from the ServiceManager which is responsible for starting/stopping that agent. That seems to be the case here.
Could you run this from a different kind of agent, one that has a LOGIN object?
Since the script is doing all of the SQL work for you through a SQL variable, you don't really need to run this on a SQL agent.
------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
Original Message:
Sent: 06-04-2021 06:54 PM
From: Helena Xiao
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
@Pete Wirfs This is what I found under the Attribute tab of the JOBS.SQL object I created:
I am using a testing environment. However, even in our PROD environment, the Login doesn't have any object in it either.
Original Message:
Sent: 06-04-2021 06:50 PM
From: Pete Wirfs
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
The variable &$LOGIN# might do the trick...
------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
Original Message:
Sent: 06-04-2021 06:46 PM
From: Pete Wirfs
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
I think you should be able to use the same login object that you are using on your JOBS.SQL "attributes" page.
------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
Original Message:
Sent: 06-04-2021 06:29 PM
From: Helena Xiao
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
@Pete Wirfs Thank you! I created a JOBS.SQL object and I got the first two steps done!
For the 3rd step, where can I find the "myloginobject"? ! WRITE THE RESULT SET OUT TO A FLAT FILE
:SET &RET# = WRITE_PROCESS(&HND#,"\\targetpath\example_sql_to_csv.csv",&host#,myloginobject,OVERWRITE)
Original Message:
Sent: 06-03-2021 08:08 PM
From: Pete Wirfs
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
I'm sorry, my instructions were incorrect. you have two alternatives to consider;
(1) put this script inside of a JOBS object that runs on an agent/host (not a SCRI object)
(2) Remove this statement, and hard-code the desired agent/host into the WRITE_PROCESS() function.
------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
Original Message:
Sent: 06-03-2021 05:51 PM
From: Helena Xiao
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
@Pete Wirfs I have learned quite some from this thread. I created a SQLI VARA object as you suggested and was able to get some data by running a query. Now I am trying to download the output to a CSV file.
I created a SCRI object and ran the first line of code as you posted in previous message as below:
However, I got an error message:
Could you please advise where I did wrong?
Original Message:
Sent: 02-24-2021 11:23 AM
From: Pete Wirfs
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
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
Original Message:
Sent: 02-24-2021 11:05 AM
From: Gokul Krishnan
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
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
Original Message:
Sent: 02-23-2021 04:35 AM
From: Gokul Krishnan
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
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
Original Message:
Sent: 02-22-2021 07:34 PM
From: Pete Wirfs
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
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
Original Message:
Sent: 02-22-2021 07:30 PM
From: Pete Wirfs
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
Yes, the preview button will return your data query results to you.
------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
Original Message:
Sent: 02-22-2021 05:30 PM
From: Gokul Krishnan
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
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
Original Message:
Sent: 02-22-2021 05:09 PM
From: Pete Wirfs
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
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
Original Message:
Sent: 02-22-2021 02:41 PM
From: Gokul Krishnan
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
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?
Original Message:
Sent: 02-22-2021 11:36 AM
From: Pete Wirfs
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
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
Original Message:
Sent: 02-22-2021 11:18 AM
From: Pete Wirfs
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
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
Original Message:
Sent: 02-22-2021 10:42 AM
From: Gokul Krishnan
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
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
Original Message:
Sent: 02-19-2021 10:15 AM
From: Gokul Krishnan
Subject: How to extract daily execution and historical daily executions from AWI 12.2.0
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