Query DB for Action History

Content Authoring
1

I want to create a SQL query to retrieve detailed action history for a given computer name.

The reason I want to do this is to be compliant with our retention policy (we need to be able to provide action history for all time for all servers). I don’t want to keep endless action history in the console view, or DB, for performance reasons. I’m thinking my best solution will be to take a full DB backup, then do cleanup (delete old actions and run audit trail cleaner). I would query the db backup if I ever need to provide action history for an audit.

I was able to create a query (below), however this doesn’t seem to give all the action history. This is DB2 format, remove the dbo. in front of the table names and this should work for MS SQL as well.

select A.name as "Action Name", A.ID, U.USERNAME, A.CreationTime, C.VALUE  as "Computer Name", R.COMPUTERID, STATE, STARTTIME, ENDTIME, TRYCOUNT, RETRYCOUNT, LINENUMBER
from dbo.ACTION_DEFS A, dbo.ACTION_FLAGS F, dbo.ACTIONRESULTS R, dbo.BES_COLUMN_HEADINGS C, dbo.USERINFO U
where (A.ID = R.ACTIONID and A.ID = F.ACTIONID AND C.NAME = 'Computer Name' and C.VALUE LIKE 'ENTER_PCNAME_HERE%' and c.COMPUTERID = R.COMPUTERID and U.MASTHEADUSERNAME = A.USERNAME)

The problem is that not all actions in the ACTION_DEFS table have a value for USERNAME, even though the user shows up in the console.

Does any one else do this? If so I would like to see your queries.

1 Like
2

This is a good idea and something many organizations have needs for.

When I was thinking about this problem, I wondered about the option of restoring the audit trail and most recent backup of the current database into a new database just for doing queries against like you are mentioning, but then hook that up to an Eval Root Server that has 0 clients, but would have the DB records for all of the clients from the real root server. Then you could run webreports or session relevance reports against the data and see the data in the console.

I’m not certain from looking at the query, but are you using the Database API?

http://www-01.ibm.com/support/docview.wss?uid=swg21505950

The documentation is for SQL, but I don’t know if applies to DB2 as well.

The idea is that there are views in SQL that will be consistent from version to version you can query without worrying about them breaking.

3

The database views are a supported API to the database and as such are expected to remain consistent. That’s not the case for the tables. If you’re doing this using the tables it may stop working at any time in the future.

1 Like
4

Is it possible to get all the columns of the Action history section for a fixlet/baseline via relevance query ? if yes, can anyone share the query. I’m new to it, so not able to get it.

5

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:

ActionName
Computer Name
Issuer of Action
Actual Start time
Actual End time
Result

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. :angry:

6

Sorry to bring this back guys, but I am looking to use Splunk to retain my history of all actions, the start/stop times, the targets, and the person who ran them if possible. I want to run this prob once every 24 hours. Does anyone have any rest api goodness to share on this matter? :slight_smile: We barely use the REST API as it stands today, and it is just for server automation. Any help would be appreciated, thank you in advance!

7

This is very interesting, probably if we can output the results of the member actions of multigroup actions then this will be extremely awesome!

8

I don’t know if I understand you correctly
but -you can se my answer here - maybe is gonna help you.