I need some hints to get the local client relevance of actions. We have some scripts that need to key off of action success and retries of the same source baselines. Sudo query something like:
(name of it, id of it, state of it) of actions of client whose( execution time of it < now - 10*hours)
The client doesn’t really have a lot of this available but the ActionHistory.db in __BESData/__Global might have the info you need though I don’t know how you could use this for targeting etc as you would need to know the action ID possibly.
If the DB has what you need in it then you can examine it with the SQLite inspectors
I poked around a little bit into ActionHistory.db in the past – here is something to get you started: Actual action start and end time
I’d recommend also using sqlite explorer 
Bill
It’s actually for a process outside of BES. I’m going to use eval.exe.
Be aware that eval.exe isn’t provided with the 9.5 and later releases. QnA can do its functionality though but then neither QnA or Eval was ever able to look at the states of the action logic in the client (though that might be changing)
yea, I’m going to have to match the version of the qna tools with the client. But overall, I need to check the state of some actions outside of the bes processes. eval.exe can be passed a question, and spit out an answer file. I’m planning on reading that output and do some actions on my own.
So… thought on getting action states?
The link I provided should get you started with relevance you can use with QnA. You can use sqlite explorer to determine what columns you are interested in and modify the query from there.
QnA is always with every client now (as of a version of 9.2) so you won’t have a problem there. It can also write files and read a file for input.
You could also look at the client compliance functionality which will use the client itself to get you answers.
But the information you are looking for is probably only in the ActionHistory.db as the client doesn’t really care about an action after its completed.
very cool. Thank’s guys. Now I just need to get my SQLLT on!. I’ll let you know what I find.
Better yet, found the Client Compliance API examples. Why didn’t you say that before!?! 
OK… I lied. We don’t have Compliance… yet.
Been digging into this and I’m starting some basics, but my sql isn’t coming back right in the QA tool, but works fine in the sqlt browser query tool:
SQL:
SELECT
A.ActionID
,A.IssuedTime
,A.ActionName
, B.State
FROM ACTION_HISTORY A
INNER JOIN ACTION_STATE_HISTORY B
ON A.ActionID = B.ActionID
WHERE A.IssuerName = ‘ede_rest_admin’
AND (A.ActionName LIKE ‘%_I%’ ESCAPE '' OR A.ActionName LIKE ‘%_U%’ ESCAPE '')
AND A.Script = ‘’
and (B.State LIKE ‘%:s=Failed:%’ AND B.State LIKE ‘%:relevant=True:%’)
order by A.IssuedTime
QA tools:
q: ( column "A.ActionID" of it, column "A.IssuedTime" of it, column "A.ActionName" of it, column "B.State" of it) of rows of statement "SELECT A.ActionID ,A.IssuedTime ,A.ActionName , B.State FROM ACTION_HISTORY A INNER JOIN ACTION_STATE_HISTORY B ON A.ActionID = B.ActionID WHERE A.IssuerName = 'ede_rest_admin' AND A.ActionName LIKE '%\_I%' ESCAPE '\' AND A.Script = '' and (B.State LIKE '%:s=Failed:%' AND B.State LIKE '%:relevant=True:%')" of sqlite database of file "ActionHistory.db" of folder "__Global" of data folder of client E: A string constant had an improper %-sequence.
Not sure why I’m getting that “%-sequence” error.
% is an escape character indicating an impending percent encoded octet – if you want to use the actual % symbol you need to do %25
Well, we are 99%25 there. My SQL expert helped me write this up all in one big fat nested ball of awesome… but qna chokes on it.
q: rows of statement "WITH CTE AS ( SELECT A.IssuedTime as 'IssuedTime' , CASE WHEN INSTR(A.[ActionName], '- retry') = 0 THEN A.[ActionName] ELSE SUBSTR(A.[ActionName], 0, INSTR([ActionName], '- retry')) END AS 'SoftwareName', SUBSTR(B.State, INSTR(B.State, ':s=') + 3, INSTR(B.State, ':pr=')-INSTR(B.State, ':s=')-3) as 'Status' , REPLACE(SUBSTR(B.State, INSTR(B.State, ':relevant=') + 10), ':', '') 'Relevant' FROM ACTION_HISTORY A INNER JOIN ACTION_STATE_HISTORY B ON A.ActionID = B.ActionID WHERE A.IssuerName = 'ede_rest_admin' AND (A.ActionName LIKE '%25\_I%25' ESCAPE '\' OR A.ActionName LIKE '%25\_U%25' ESCAPE '\') AND A.Script = '' ) SELECT MAX(A.IssuedTime) 'IssuedTime' , A.SoftwareName 'SoftwareName' FROM CTE A INNER JOIN (SELECT SoftwareName FROM CTE GROUP BY SoftwareName HAVING COUNT(1) > 1) B ON A.SoftwareName = B.SoftwareName GROUP BY A.SoftwareName" of sqlite database of file "ActionHistory.db" of folder "__Global" of data folder of client
E: This expression has a very long string.
Is there a limit to the size of the query? The same query works fine when in a normal SQLite query tool.
QNA is complaining the quoted string is too long (so by deduction you are probably using 9.2)
The quoted string length is over 800 characters here, and if memory serves its only 512 on 9.2 so not sure how it will let you get away with this. You might be able to “assemble” a string. Any ideas @jgstew @strawgate ?
Can you just do this?
Q: rows of statement ("WITH CTE AS ( SELECT A.IssuedTime as 'IssuedTime' , CASE WHEN INSTR(A.[ActionName], '- retry') = 0 THEN A.[ActionName] ELSE SUBSTR(A.[ActionName], 0, INSTR([ActionName], '- retry')) END AS 'SoftwareName', SUBSTR(B.State, INSTR(B.State, ':s=') + 3, INSTR(B.State, ':pr=')-INSTR(B.State, ':s=')-3)" & " as 'Status' , REPLACE(SUBSTR(B.State, INSTR(B.State, ':relevant=') + 10), ':', '') 'Relevant' FROM ACTION_HISTORY A INNER JOIN ACTION_STATE_HISTORY B ON A.ActionID = B.ActionID WHERE A.IssuerName = 'ede_rest_admin' AND (A.ActionName LIKE '%25_I%25' ESCAPE '\' " & "OR A.ActionName LIKE '%25_U%25' ESCAPE '\') AND A.Script = '' ) SELECT MAX(A.IssuedTime) 'IssuedTime' , A.SoftwareName 'SoftwareName' FROM CTE A INNER JOIN (SELECT SoftwareName FROM CTE GROUP BY SoftwareName HAVING COUNT(1) > 1) B ON A.SoftwareName = B.SoftwareName GROUP BY A.SoftwareName") of sqlite database of file "ActionHistory.db" of folder "__Global" of data folder of client
Assembling the string with &. This doesn’t return the long string error for me just a non-existent object error. Probably because I dont have any actions that match your query.
I think @AlanM is right that this is a string length issue that can be solved by concatenation like @strawgate 's example.
What are you using external to bigfix to do this work or what will be consuming the answers?
It is likely that whatever you are using could probably do the SQLite query itself.
Nice try @strawgate, I get the return “E: The expression could not be evaluated: near “WITH”: syntax error (SQLite error 0-1)” I’m guessing the using the " & " isn’t quite working on my client. To the next topic…
@AlanM, yes our clients are still running 9.2.7.53, but the server is 9.5.3 (for us, 2 upgraded behind). Desktop team is still holding back on upgrading the clients…
@jgstew the engine of this hack job is going to be VBScript. I would love to see it go away, but this is sort of a patch job until IBM gets interactive installs working natively in the client (we have a PMR/RFE and IBM is working on this). We have to watch for failed jobs issued from a service account and if we see any, we crank up the client CPU time and reporting intervals so our process on the root can catch them and resend the job. PSExec isn’t being as reliable as it should. There is some sort of race condition the happens and resending the job normally fixes the issue. If you can think of a way to run SQLite queries w/o installing additional software, let me know.
I may just have the guy creating the vbs work with a smaller query and he can sift through the data himself.
Might be able to set to reapply on failure.
That is a good point. I wasn’t sure what you were working with and if it might be able to handle it. Not sure if Powershell has native SQLite capabilities.
I did some more experimenting with the assembling strings and that didn’t appear to work in general. Would love to know how you got that to run at all. Even this comes out different:
q: length of "this " & " and that"
E: The operator "concatenate" is not defined.
q: preceding text of first ":" of "domain.com:52311/bfmirror/downloads"
A: domain.com
q: preceding text of first ":" of "domain.com:52311/ " & "bfmirror/downloads"
A: domain.combfmirror/downloads
On a side note, thought this was really funny:
q: length of "WITH CTE AS ( SELECT A.IssuedTime as 'IssuedTime' , CASE WHEN INSTR(A.[ActionName], '- retry') = 0 THEN A.[ActionName] ELSE SUBSTR(A.[ActionName], 0, INSTR([ActionName], '- retry')) END AS 'SoftwareName', SUBSTR(B.State, INSTR(B.State, ':s=') + 3, INSTR(B.State, ':pr=')-INSTR(B.State, ':s=')-3)" & " as 'Status' , REPLACE(SUBSTR(B.State, INSTR(B.State, ':relevant=') + 10), ':', '') 'Relevant' FROM ACTION_HISTORY A INNER JOIN ACTION_STATE_HISTORY B ON A.ActionID = B.ActionID WHERE A.IssuerName = 'ede_rest_admin' AND (A.ActionName LIKE '%25_I%25' ESCAPE '\' OR A.ActionName LIKE '%25_U%25' ESCAPE '\') AND A.Script = '' ) SELECT MAX(A.IssuedTime) 'IssuedTime' , A.SoftwareName 'SoftwareName' FROM CTE A INNER JOIN (SELECT SoftwareName FROM CTE GROUP BY SoftwareName HAVING COUNT(1) > 1) B ON A.SoftwareName = B.SoftwareName GROUP BY A.SoftwareName"
E: This expression has a very long string.
So that back’s up @AlanM’s note. It’s 845 characters long.
You need to wrap your string concatenations in parentheses otherwise order of operations tries to append the string to the result of the left hand side.
Are you sure your query works with bigfix? BigFix 9.5 has longer string lengths so you may want to download the 9.5 fixlet debugger and make sure your query works.
The first one here is a normal long string. The second is the same thing with a random & inserted. The third one is with the correct order of operations:
Q: length of "Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test "
E: This expression has a very long string.
Q: length of "Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test" & " Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test "
E: The operator "concatenate" is not defined.
Q: length of ("Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test" & " Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test ")
A: 940