DB Query to See Status of Action (OPEN/Expired/Closed)

Usage and Config Platform
1

(imported topic written by MP6591)

Ok BigFix Gurus… I’m not sure if this is the right place to post the questoin but here goes:

Is there a (simple) way to query the BFEnterprise db to check the status (open/expired/closed) of an action, given its action ID?

Thanks,

Mike

2

(imported comment written by SystemAdmin)

Hi Mike,

Actually, this is not easy to do and you’ll likely need a script to do it. Open/Stopped actions are easy but ‘expired’ actions are actually not recorded as expired in the database. You BES Console compares the current time to the action end date to tell if it is ‘expired’ or not. It is more complicated though because your actions run on client local time which could be up to 24 hours different the time on the BES Console. So, the BES Console doesn’t mark the action as expired for 24 hours after the action end time!

Here’s some query examples to help:

All stopped actions (not deleted):

select p.id, p.name from properties p, versions v, actions a where p.contenttype=7 and p.parentid=1 and p.version = v.latestversion and p.id = v.id and p.sitename = v.sitename and p.isfixlet = v.isfixlet and p.isfixlet=0 and p.sitename = 
'actionsite' and a.Actionid = p.id and a.IsDeleted = 0

All expired actions with end date less then a value (not deleted):

select p.id, p.name from properties p, versions v, textfields t, actions a where p.contenttype=7 and p.parentid  1 and p.version = v.latestversion and p.id = v.id and p.sitename = v.sitename and p.isfixlet = v.isfixlet and p.isfixlet=0 and p.sitename = 
'actionsite' and p.id = t.id and p.sitename = t.sitename and p.isfixlet = t.isfixlet and p.version = t.version and p.id = a.actionid and a.isdeleted = 0 and (t.fieldname=
'ExpirationTime' or t.fieldname=
'EndTime') and convert(int, convert(varchar(20), t.FieldContents) ) < 
'

Notice that we have to look for both ‘ExpirationTime’ and ‘EndTime’ in the textfields.fieldname for reverse compatibility. We switched the text field value in 5.1 for various reasons.

The would be in seconds since 1970, also known as the beginning of time for computer scientists born after 1970 :slight_smile:

3

(imported comment written by MP6591)

Thanks Tyler!

That did the trick. I didn’t realize the ParentID changes (0 to 1) when the action is stopped.