Automic Workload Automation

Hello Community,

we have been asked for the following Information by our dev manager:

- Create a ranking of all Workflows with their Jobs sorted by runtime (time consuming)
- It should be "top down" approach to identify the most time consuming objects first
- The top parent should be a specific one

Are there any SQL-Statements or other solutions for this?

Any help is appreciated!

André

The UC4ReportingTool includes the ability to extract a set of statistics for a select time period into a CSV file.  Once extracted to CSV, you could then use Excel to manipulate the data into the information you are looking for.

If you have never used the UC4ReportingTool before, there is a bit of a learning curve.  But I think it is worth it.  UC4 admins need to know how to extract this type of information.

On the other hand, I'm also curious to know if the ERT (estimated run time) value that is displayed on the Runtime tab might be stored in the database, and therefore would be easy to run a SQL query against it?

UPDATE: I found database attribute oh_ert exists in table OH.  It contains the number of seconds that the object is estimated to run.  I think this is what you need.

UPDATE #2: I found this SQL I've used in the past to find the longest running jobs during a time period:

select AH_Client, AH_OType, OH_Name, AH_TimeStamp2, AH_TimeStamp4, ah_runtime      from ah, oh where ah_client = 100 and      AH_OType = 'JOBS' and      AH_TimeStamp1 > '08/01/2014 00:00:00' AND             AH_TimeStamp1 < '08/31/2014 23:59:59' and      AH_OH_Idnr = OH_Idnr and      AH_RunTime > 2000 order by ah_timestamp2

For those with slightly deeper pockets you might look at the add-on Predictive Analytics product.

We don't have the product but I have been quite impressed with the presentations that I have seen at the Innovate conferences.

Thanks very much for the replies! I've seen the UC4ReportingTool before but didn't try it out (so far) for my issue. Basically we like to solve the issue with the "on board" features of the Automation Engine.

@Pete Wirfs: many thanks! the SQL looks good to me. I think writing a read only SQL against the Automic database could be the solution. Especially because the Database scheme is available.

Will let you know about my progress ...

@Andre Eymann , if you are interested in the actual resource consumption (CPU), you can incorporate the column ah_cputime into Pete's query as well (only relevant for JOBS not JOBP).

To group by top-level workflow, use ah_topnr (join with another ah table to get the workflow's name).

You can find the DB schema under most versions here: http://docs.automic.com/one-automation.php 

Thanks Jessica!

There's a white paper on using the db.reporting tool. Here's the link to the post. Creating Reports using the DB.Reporting Utility

Thanks Jennifer! We will take a deeper look into the Reporting Utility.