Database query to show all actions and times for a specific agent

(imported topic written by BenKus)

One of our customers was recently asked to show all the actions (including exact times) that the BigFix Agents were taking for a specific set of computers. The reason for the request was that some computers were experiencing some strange behaviors and they wanted to verify that the BigFix Agent wasn’t related to the issues (the customer later confirmed the behavior wasn’t related to the BigFix Agent).

Getting a list of actions for a BigFix Agent is pretty easy (just double-click on the computer in the Console or click on the computer in Web Reports), but to get the exact start/end time of each action, you would need to click on each action and see the additional action details.

To make this task a little easier, here is a database query (for BigFix 7.0) that each action name, action creation time, agent start time, agent end time for a specific computer. (Make sure to change “COMPUTERNAME” to the name of the computer you wish to query:

select Q.ResultsText as ‘ComputerName’ , Cast(AD.Version as integer) as ‘Version’, A.ActionID, AD.Name as ‘Action Name’, AD.CreationTime as ‘Action Creation Time’, A.StartTime, A.EndTime
from ACTIONRESULTS A, QUESTIONRESULTS Q, LOCAL_OBJECT_DEFS L, ACTION_DEFS AD where
A.ComputerID=Q.ComputerID AND
L.Name=‘Computer Name’ AND
L.ID = Q.AnalysisID AND
AD.ID = A.ActionID AND
Q.ResultsText LIKE ‘COMPUTERNAME_’

order by A.StartTime

Ben

(imported comment written by BenKus)

And for 6.0 users, you can try this one:

select Q.ResultsText as ‘ComputerName’ ,
A.ActionID, AD.Name as ‘Action Name’, AD.CreationTime as ‘Action Creation Time’, A.StartTime, A.EndTime
from ACTIONRESULTS A, QUESTIONRESULTS Q, PROPERTIES P, PROPERTIES AD, VERSIONS V, VERSIONS V2 where
A.ComputerID=Q.ComputerID AND
P.Name=‘Computer Name’ AND
P.ID = Q.ID AND
V.Sitename = ‘Actionsite’ AND V2.Sitename = ‘Actionsite’ AND
P.Sitename = ‘Actionsite’ AND AD.Sitename = ‘Actionsite’ AND
V.ID = P.ID AND V2.ID = AD.ID AND
V.IsFixlet = P.IsFixlet AND V2.IsFixlet = AD.IsFixlet AND
V.LatestVersion = P.Version AND V2.LatestVersion = AD.Version AND
AD.ID = A.ActionID AND
AD.Name not Like ‘%subscribe%’ AND

Q.ResultsText LIKE ‘COMPUTERNAME_’

order by A.StartTime

(imported comment written by StacyLee)

Ben Kus

One of our customers was recently asked to show all the actions (including exact times) that the BigFix Agents were taking for a specific set of computers. The reason for the request was that some computers were experiencing some strange behaviors and they wanted to verify that the BigFix Agent wasn’t related to the issues (the customer later confirmed the behavior wasn’t related to the BigFix Agent).

Getting a list of actions for a BigFix Agent is pretty easy (just double-click on the computer in the Console or click on the computer in Web Reports), but to get the exact start/end time of each action, you would need to click on each action and see the additional action details.

To make this task a little easier, here is a database query (for BigFix 7.0) that each action name, action creation time, agent start time, agent end time for a specific computer. (Make sure to change “COMPUTERNAME” to the name of the computer you wish to query:

select Q.ResultsText as ‘ComputerName’ , Cast(AD.Version as integer) as ‘Version’, A.ActionID, AD.Name as ‘Action Name’, AD.CreationTime as ‘Action Creation Time’, A.StartTime, A.EndTime
from ACTIONRESULTS A, QUESTIONRESULTS Q, LOCAL_OBJECT_DEFS L, ACTION_DEFS AD where
A.ComputerID=Q.ComputerID AND
L.Name=‘Computer Name’ AND
L.ID = Q.AnalysisID AND
AD.ID = A.ActionID AND
Q.ResultsText LIKE ‘COMPUTERNAME_’

order by A.StartTime

Ben

Ben,

This is almost exactly what I need. Can this be done from web reports, but targeted at a automatic group then filter by a paticular fixlet/task ? I have had a request by our ISO to know when a certain fixlet has run and how many times is ran and when.

thanks

(imported comment written by BenKus)

Hey Stacy,

Unfortunately, I don’t think so… I don’t believe the time an action result ran is available with session relevance queries (only the time the action was taken)… but we can probably get you the other data in the query.

Ben

(imported comment written by BenKus)

And if you wanted to filter the results to only computers that were reporting “Evaluating”, “Running”, “Fixed”, “Failed”, then use:

select Q.ResultsText as ‘ComputerName’ , Cast(AD.Version as integer) as ‘Version’, ASS.ActionStateString, A.ActionID, AD.Name as ‘Action Name’, AD.CreationTime as ‘Action Creation Time’, A.StartTime, A.EndTime
from ACTIONRESULTS A, QUESTIONRESULTS Q, LOCAL_OBJECT_DEFS L, ACTION_DEFS AD, ACTIONSTATESTRINGS ASS where
A.ComputerID=Q.ComputerID AND
L.Name=‘Computer Name’ AND
L.ID = Q.AnalysisID AND
AD.ID = A.ActionID AND
A.State = ASS.ActionState AND
(A.State > 0 AND A.State < 5) AND
Q.ResultsText LIKE ‘COMPUTERNAME_’

order by A.StartTime

(imported comment written by SystemAdmin)

hi,

this is very nice query,

Can you also show the operator who start the distribution of the fixlet in each row in the output.

Thanks

Andi

(imported comment written by okole91)

Ben,

I am trying to run this query but it always returns 0 rows.

What am I doing wrong?

(imported comment written by BenKus)

Make sure you substitute the COMPUTERNAME and make sure it ends with “_”.

Ben

(imported comment written by SystemAdmin)

hi BenKus,

I use following SQL query with perl:

my $query = "select ‘BigFix’ ,ASS.ActionStateString,Q.ResultsText as ‘ComputerName’ , Cast(AD.Version as integer) as ‘Version’, A.ActionID, AD.Name as ‘Action Name’, AD.CreationTime as ‘Action Creation Time’, A.StartTime, A.EndTime

from ACTIONRESULTS A, QUESTIONRESULTS Q, LOCAL_OBJECT_DEFS L, ACTION_DEFS AD, ACTIONSTATESTRINGS ASS where

A.ComputerID=Q.ComputerID AND

L.Name=‘Computer Name’ AND

Q.AnalysisID = L.ID AND

AD.ID = A.ActionID AND

A.State = ASS.ActionState AND

(A.State > 0 AND A.State < 5)

AND Q.siteid < 0

AND convert(char,A.starttime,104) = convert(char,getdate(),104)

order by A.StartTime";

We use LDAP User.

Can you help me to list the username, for all actions?

Please

Thanks Andi

(imported comment written by okole91)

aaahhhhaaaa!!!

I replaced all of the "computername"s…I only needed to replace the last one that was in caps

(imported comment written by SystemAdmin)

hi,

this is very nice query,

Can I you also show the operator who start the distribution of the fixlet in each row in the output.

Thanks

Andi

(imported comment written by renehasp)

Hi, I am looking for the same thing Andi is looking for. Did anyone get it working to show AD users in the results?