I’ve finally found some time to look at this again. I understand SQL views are the supported method to retrieve data from the DB and the structure of these views shouldn’t change with new versions. However, the views do not give me enough detail.
I’m looking to create a query to provide action history against a given computer. I need the following information:
Issuer of Action
Actual Start time
Actual End time
I don’t see the actual start time or end time in the DB views for actions. There is a STARTTIME column in the BES_ACTIONS view. But this seems to actually match the issued time, unless the action was stopped, then STARTTIME matches the stopped time (very strange)…
Anyway, here is a query I was able to create that only retrieves data from the BES_ACTIONS and BES_COLUMN_HEADINGS views (this doesn’t give actual start or end times):
select A.name as "Action Name", ACTIONID, USERNAME, ACTIONSTATUS, STARTTIME as "STARTTIME (unreliable)", FIXLETID, A.COMPUTERID, C.VALUE as "Computer Name"
from dbo.BES_ACTIONS A, dbo.BES_COLUMN_HEADINGS C
where (C.NAME = 'Computer Name' AND C.VALUE LIKE 'ENTER_PCNAME_HERE%' AND C.COMPUTERID = A.COMPUTERID)
I am able to get the actual start and stop times if I query the ACTIONRESULTS table. Here is that query:
select A.name as "Action Name", A.ACTIONID, USERNAME, ACTIONSTATUS, A.STARTTIME as "STARTTIME (unreliable)", FIXLETID, R.STARTTIME, ENDTIME, TRYCOUNT, RETRYCOUNT, LINENUMBER, A.COMPUTERID, C.VALUE as "Computer Name"
from dbo.BES_ACTIONS A, dbo.BES_COLUMN_HEADINGS C, dbo.ACTIONRESULTS R
where (A.ACTIONID = R.ACTIONID and C.NAME = 'Computer Name' AND C.VALUE LIKE 'ENTER_PCNAME_HERE%' AND C.COMPUTERID = A.COMPUTERID AND C.COMPUTERID = R.COMPUTERID)
Of course since I’m querying a TABLE, my query could break if IBM decides to change it.