Report to summarize current active jobs?

Discussion created by eric.felker on Oct 23, 2015
Latest reply on Aug 16, 2017 by Carsten_Schmitz
This question has come up in various forms before, but I've never gotten a satisfactory answer. Here is a recent one, and here is another. How can a user easily get a report of the jobs scheduled/expected to run, in their client, over the next 24 hours? 72 hours? Many users are asking how they can get a report of this information, and I don't have a great answer for them. With 50+ clients, managed by individual app teams, the solution needs to be easy and safe.

Things we'd like to see in a report:
  • Active jobs (i.e., jobs that are scheduled to run, either via C_PERIOD, SCHE, EVNT, etc)
  • Alias, if set
  • Frequency (Time, days of week, Calendar exceptions)
  • Next expected run date/time
  • Estimated Run Time (ERT)
  • other...

Right now the options I'm aware of are:
  • Auto Forecast - Requires a VARA to be set per client, so some manual set up. Also requires privilege be set, suggesting this function is dangerous. Indeed, I've actually crashed production more than once playing with this. Not a feature I want to open up to end users.
  • DB Reporting Tool - A separate set of binaries requiring direct access to the database. Not a route I want to open up to end users. Also very cumbersome and inflexible (create report file, hardcoded with client, columns of interest, time range, etc, then must configure a JOB wrapper to execute). If you want to change anything, you need to create a different report file, then adjust your wrapper job to execute. Moreover, while we've been able to set up Statistic reports with this utility, Forecast reports have been empty so far. Not sure where I am going wrong, still working on it, may open ticket with support.
  • Manually looking at activities window, right-click -> Properties to see C_PERIOD or EVNT objects. Monitor for SCHE objects. Manual is bad.
  • SQL query that can produce this output. This is not the answer for end users, but seems to be the common last resort. Obviously this would require direct DB access, but we are now considering Tableau to expose all active (not ended) activities across an entire system, then implementing filters so users can look at only their clients. The Automic admin team could publish these for all to see. This seems like the best answer at the moment, but need the perfect query. Easier said than done!

Out of the box reporting is sorely lacking with this tool at the moment, and deserves some focus. One of the links above asks about a User Report. I didn't get into that at all, but it, too, would be extremely valuable. We shouldn't need to write custom SQL to get this information.

I hesitate to ask, because this is not the right answer, but as reports are not readily available, is there an SQL repo somewhere on the community where we can collectively gather useful queries? As I typed that, I'm feeling deja vu, like this has been suggested before.

Thx for reading, and appreciate any feedback!