Anyone using 'ActionHistory.db'

(imported topic written by zpt8mjs)

Greeting,

In order to integrate IEM with several of our existing processes, I am looking for a way to monitor the status of actions from the client side. I was poking around a bit and found that there is a SQLite file in the client directory “.\__BESData\__Global\ActionHistory.db.” A little more poking around and I was able to find a reference to the db in the executable for the client. So, if you run the following SQL query against the database:

select AH.ActionID, TotalActiveCount, ActiveCount, State, IssuerName, IssuedTime, ChangeTime, ActionName, HadUserInterface, Trivial from ACTION_HISTORY as AH, ACTION_STATE_HISTORY as ASH where AH.ActionID = ASH.ActionID;

it gives you a list of all the actions that were run on the client. An example of the output looks like this:

846384|1|1|846384: :pending=1:id=846384:pt=1413494069:constrained=1:constraint=8:fac=1:ac=1:al=4:ast=1413554758:lal=4:lat=1413554790:lct=1413554792:wfd=0:act=0:s=Waiting:pr=1:pl=0:ec=0:notexpired=True:relevant=True:|nyc2kxc|1413494015|1413554974|2014-10-15 MS Patch Policy MUI - MS14-059: Vulnerability in ASP.NET MVC Could Allow Security Feature Bypass - ASP.NET MVC 2.0 - KB2993939 (All Languages)|0|0

Much of this appears straight forward. However, the most interesting is probably the ‘State’ column. Can someone help me decipher what this all means? There are a lot of abbreviations in there, and I could guess at what they are, but it would be better if I don’t have to guess.

Thanks

zpt8mjs

(imported comment written by dominic.otoole)

Hi,

Is there any reason why you cannot use the IEM REST API ( asusming 9.0 or greater ) to retrieve this information ? This is the public facing API and would shield you from any future database changes.

Get all actions:

https://yourserver:52311/api/actions

Find details of a specific action:

https://yourserver:52311/api/action/3261

(imported comment written by zpt8mjs)

Hi,

Thanks for the suggestion. We are currently leveraging use of the REST API for integration of several of our automation/integration initiatives. This is only available from the Core BES Server not from the client side.

Our current process requires that we know when all the content in a specific custom site has been processed so that processing outside of IEM can continue on the client side. We achieve this currently using a combination of control fixlets and a Web Service that executes SOAP requests against our Web Reports Server. This process worked rather well for a while however due to scalability issues, our Web Reports Server is unable to keep up with the requests against it.

The Client Compliance API is useful for showing me what is still relevant in the custom site, however, there is no client relevance that helps me see the action history. So, by using a combination of the Client Compliance API and the information in the ActionHistory.db I can determine what has applied successfully, what has applied and failed and what has yet to be applied.

Unfortunately, in order to do this effectively, I need a better description of what is in this DB.

zpt8mjs

While I don’t have the full details about the fields in the database, based on some quick analysis, it seems like the ACTION_STATE_HISTORY table contains all the necessary data. As you suggested, in particular the ‘State’ field is of interest as it appears to contain an action status (in your example above :s=Waiting). Version 9 introduced native sqlite inspectors, and so the following relevance appears to provide some useful information:

(“ActionID”, “Last Action Time”, “Status”);((preceding text of first “:” of following text of first “:id=” of (it as string) as string, (preceding text of first “:” of following text of first “:lat=” of (it as string) as integer * second + (“01 Jan 1970 00:00:00” as local time)) as string, preceding text of first “:” of following text of first “:s=” of (it as string) as string) of (rows of statement “select yt.ActionID, yt.TotalActiveCount, yt.State from ACTION_STATE_HISTORY yt inner join (select ActionID, max(TotalActiveCount) TotalActiveCount from ACTION_STATE_HISTORY group by ActionID) ss on yt.ActionID = SS.ActionID and yt.TotalActiveCount = SS.TotalActiveCount” of sqlite database of file “ActionHistory.db” of folder “__Global” of data folder of client))

Can you give this a try and let us know if returns the data of interest?

1 Like

A better way to do this type of thing on the endpoint would be to use the client compliance API and just query on the state of an action.

See: https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/Tivoli%20Endpoint%20Manager/page/Client%20Compliance%20API for some information on the API and see if it would work for you.

The information is in the ActionHistory.db file yes, but the form of it can change between releases and to be honest explaining that wonderful status string (which is the same as a report generates) is pretty tough.

2 Likes