Is there a way via the UI to find jobs/workflows that are not active or haven't been active since a period of time? And a way to find jobs that are not part of a workflow?
I don't believe I've ever found a way to get this from the UI. I think all the UI is going to show you are things that do have statistics for a certain period of time. You would end up having to do quite a bit of comparing to get down to your list of things that have not run. I ended up writing a query to pull what I wanted from the database.
you can use the GUI (UserInterface) to search for statistic entries. One of the search filters can be used to define a time period. So you can look for activated / started / ended objects of any type and export this list as CSV. Using Excel you can prepare any custom list.
If you need a custom report - for example to find every Jobs object which is not used in e.g. Workflows you will need a Java tool using our Java API.
Generally ... for all those custom reports I recommend to create a custom Java tool using our Java API. You are able to define any report you want. You can reuse this tool with any AE version using the right API. You can run this report periodically (for example as a batch and send the result per e-mail).
I hope that helps.
Jim, would you be willing to share your query with the group?
Sure! I have this setup to run with in an OSQL command in each of our clients. This will look for any object that doesn't have an end time within the last 6 mos. I have the extra select in there and casts because I have it being written to a CSV file for us to use.
'CLIENT' + CHAR (44) +
'JOBNAME' + CHAR (44) +
'RUNTIME' + CHAR (44) +
CAST (AH.AH_CLIENT AS VARCHAR (30)) + CHAR (44) +
CAST (OH.OH_NAME AS VARCHAR (30)) + CHAR (44) +
CAST (max (AH.AH_TIMESTAMP4) AS VARCHAR (30))
on ah.ah_oh_idnr = oh.oh_idnr
where ah.ah_timestamp4 is not null --needs to have completed to be considered
group by ah.ah_client, ah.ah_oh_idnr, oh.oh_name
having max(ah.ah_timestamp4) < dateadd(mm, -6, getDate())
For jobs that have never run:
WHERE OH_Client != '0' AND
OH_Client < '5000' AND
OH_LastDate is NULL AND
--CAST(OH_LastRuns as varchar(max)) LIKE '' AND
OH_LastRuns=CAST('' AS VARBINARY) AND
OH_RefIdnr = '0' AND
OH_OType IN ('JOBP', 'JOBS') AND
OH_Idnr NOT IN (
SELECT OFS_OH_IDNR_O FROM OFS WHERE OFS_OH_Idnr_F IN
SELECT OH_Idnr FROM OH WHERE OH_Name LIKE '%_TEMPLATES'
ORDER BY OH_NAME
Retrieving data ...