Sometimes as a harvest administrator there may be a need to generate a custom report
that provides a list of all UDPs that are in the SCM repository.
Following is a script by Tom Cameron that generates such a report.
Attached is a screenshot of running the report on DBrowse against an Oracle test
scm database.
You can get the DBrowse install zip file that is freeware from CA from here: DBrowse
DBrowse is a database browser utility that allows a user to login and connect
to the Harvest database. For Oracle, you will need to have the Oracle
client installed and an odbc connection object created by the ODBC Manager
utility (c:\windows\syswow64\odbcad32.exe). For SQL Server, you must have the
SQL Server client installed and the ODBC connection object created through
the same odbcad32.exe.
Here is the SQL Script:
select
E.environmentname, S.statename, U.processname, '<Standalone>' as LINKEDTO,
' ' as PRELINK, U.udptype, U.programname, U.inputparm as INPUT from harudp
U, harenvironment E, harstate S
where U.stateobjid = S.stateobjid and S.envobjid = E.envobjid
union select
E.environmentname, S.statename, U.processname, P.processname as LINKEDTO,
L.processprelink as PRELINK, U.udptype, U.programname, U.inputparm as INPUT
from harudp U, harstateprocess P, harlinkedprocess L, harenvironment E,
harstate S
where U.parentprocobjid = P.processobjid and L.processobjid =
U.processobjid and P.stateobjid = S.stateobjid and S.envobjid = E.envobjid
order by 1,2,3,4,5,6;