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
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.
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.
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.
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 ?
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.
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.
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