Open Actions only

Reporting
1

(imported topic written by RosaMartin)

We need assistance in pulling only OPEN actions using the views on BigFix version 6 and version 7

The following is the SQL script we are using.

SELECT BES_ACTIONS.ActionID, BES_ACTIONS.ComputerID,

BES_ACTIONS.FixletID, BES_ACTIONS.Name,

BES_ACTIONS.Username, BES_ACTIONS.StartTime, ACTIONRESULTS.EndTime As ExpirationDate,

BES_ACTIONS.Sitename, BES_ACTIONS.ActionStatus

FROM BFEnterprise.dbo.ACTIONRESULTS INNER JOIN BFEnterprise.dbo.BES_ACTIONS ON ACTIONRESULTS.ActionID = BES_ACTIONS.ActionID

We would really appreciate help with this.

Thanks

Rosa.

2

(imported comment written by BenKus)

This is tricky because you want to exclude actions that are expired, but the database doesn’t have a field to determine expiration (the console figures that out based on the local time zone of the console)…

Note that this is much easier with session relevance with the SOAP API (you can use the excel connector to execute this query at http://support.bigfix.com/labs/excelconnect.html):

(name of it, id of it, time issued of it) of bes actions whose (state of it = “Open”)

This might help for 6.0:

http://forum.bigfix.com/viewtopic.php?id=320

For 7.0+, it is hard… see if this works:

select id, parentid, creationtime, username, name, ((DATEADD(s, cast( dbo.fn_extractfield(‘EndTime’,0, Fields)as integer) , ‘1970-01-01’)))as 'endtime’
from BES_ACTION_DEFS
where (dbo.fn_extractfield(‘EndTime’,0, Fields) is NULL OR
((DATEADD(s, cast( dbo.fn_extractfield(‘EndTime’,0, Fields)as integer) , ‘1970-01-01’))) > getdate()) AND
parentid != 1

Ben

3

(imported comment written by RosaMartin)

This is what you gave us to get open actions in BigFix 6.0 back in 11/10/06. This same script will not work in 7.0 because some of the tables have changed. Can you tell me how to make it work in 7.0.

After we upgrade we’ll look into SOAP API to get this data, but for now we need to use SQL -thanks

SELECT P.ID as ‘ActionID’,

ActionResult.ComputerID,

CONVERT(int, CONVERT(char(11), FixletIdRow.FieldContents)) AS ‘FixletID’,

P.Name,

P.Username,

P.CreationTime as ‘StartTime’,

DATEADD(second, CAST(CONVERT(varchar(20),

ExpirationTimeRow.FieldContents) as INTEGER), ‘01/01/1970 00:00:00.000’) AS ExpirationDate,

CONVERT(varchar(128), SourceSiteRow.FieldContents) AS ‘Sitename’,

ActionStateString.ActionStateString AS ‘ActionStatus’

FROM BFEnterprise.dbo.PROPERTIES Parent,

BFEnterprise.dbo.VERSIONS V, BFEnterprise.dbo.VERSIONS V2,

BFEnterprise.dbo.ACTIONRESULTS ActionResult,

BFEnterprise.dbo.ACTIONS Actions,

BFEnterprise.dbo.ACTIONSTATESTRINGS ActionStateString,

BFEnterprise.dbo.COMPUTERS C,

BFEnterprise.dbo.PROPERTIES P

LEFT OUTER JOIN

BFEnterprise.dbo.TEXTFIELDS ExpirationTimeRow on

(

ExpirationTimeRow.Sitename = ‘ActionSite’

AND ExpirationTimeRow.ID = P.ID

AND ExpirationTimeRow.IsFixlet = P.IsFixlet

AND ExpirationTimeRow.Version = P.Version

AND ExpirationTimeRow.FieldName = ‘EndTime’

AND ExpirationTimeRow.FieldNumber = 0 )

LEFT OUTER JOIN

BFEnterprise.dbo.TEXTFIELDS FixletIdRow on

(

FixletIdRow.Sitename = ‘ActionSite’

AND FixletIdRow.ID = P.ID

AND FixletIdRow.IsFixlet = P.IsFixlet

AND FixletIdRow.Version = P.Version

AND FixletIdRow.FieldName = ‘SourceFixletId’

AND FixletIdRow.FieldNumber = 0 )

LEFT OUTER JOIN

BFEnterprise.dbo.TEXTFIELDS SourceSiteRow on

(

SourceSiteRow.Sitename = ‘ActionSite’

AND SourceSiteRow.ID = P.ID

AND SourceSiteRow.IsFixlet = P.IsFixlet

AND SourceSiteRow.Version = P.Version

AND SourceSiteRow.FieldName = ‘SourceSitename’

AND SourceSiteRow.FieldNumber = 0

)

WHERE P.ParentId 1

AND P.Sitename = ‘ActionSite’

AND V.Sitename = ‘ActionSite’

AND P.ID = V.ID

AND P.IsFixlet = V.IsFixlet

AND P.Version = V.LatestVersion

AND Parent.ID = P.ParentId

AND Parent.IsFixlet = 0

AND Parent.Sitename = P.Sitename

AND Parent.ID = V2.ID

AND Parent.IsFixlet = V2.IsFixlet

AND Parent.Version = V2.LatestVersion

AND Parent.Sitename = V2.Sitename

AND Parent.Name NOT LIKE ‘Subscription_’

AND P.Name NOT LIKE ‘Start Action’

AND P.Name NOT LIKE ‘End Action’

AND ActionResult.ActionID = P.ID

AND ( ExpirationTimeRow.FieldContents IS NULL

OR ( DATEADD(second, CAST(CONVERT(varchar(20),

ExpirationTimeRow.FieldContents) as INTEGER), ‘01/01/1970 00:00:00.000’)

= getDate() ) )

AND ActionStateString.ActionState = ActionResult.State

AND C.ComputerID = ActionResult.ComputerID

AND C.IsDeleted = 0

AND SourceSiteRow.FieldContents like ‘%Enterprise%’

AND P.UserName ‘sa’

AND ActionResult.ActionID = Actions.ActionID

AND Actions.IsDeleted = 0

4

(imported comment written by BenKus)

Hmmm… I appear to have been much better at SQL in 2006 than I am today…

Try this one (slightly modified from the one I previously sent) for 7.x:

select id, parentid, creationtime, username, name, ((DATEADD(s, cast( dbo.fn_extractfield(‘EndTime’,0, Fields)as integer) , ‘1970-01-01’)))as 'endtime’
from BES_ACTION_DEFS
where (dbo.fn_extractfield(‘EndTime’,0, Fields) is NULL OR
((DATEADD(s, cast( dbo.fn_extractfield(‘EndTime’,0, Fields)as integer) , ‘1970-01-01’))) > getdate()) AND
parentid != 1

Ben

5

(imported comment written by RosaMartin)

that worked better but now I need to get some other field information into this query - this is what I have

but now I don’t see the “non expiring” open actions:

SELECT

BES_ACTIONS.ActionID, BES_ACTIONS.ComputerID, BES_ACTIONS.Name, BES_ACTIONS.Username, BES_ACTIONS.StartTime, BES_ACTIONS.FixletID, BES_ACTIONS.Sitename, BES_ACTIONS.ActionStatus, DATEADD(s, CAST(dbo.fn_ExtractField(‘EndTime’, 0, BES_ACTION_DEFS.Fields) AS integer), ‘1970-01-01’) AS ‘EXPIRATION_TIME’

FROM

BES_ACTION_DEFS RIGHT OUTER JOIN

BES_ACTIONS ON BES_ACTION_DEFS.ID = BES_ACTIONS.ActionID

WHERE

(dbo.fn_extractfield(‘EndTime’,0, Fields) = NULL OR ((DATEADD(s, cast( dbo.fn_extractfield(‘EndTime’,0, Fields)as integer) , ‘1970-01-01’))) > getdate()) AND (BES_ACTION_DEFS.ParentID != 1)

  • I only get 1 record whe it should be 9 records (2 with expiration dates and the rest will none)

I’ve tried every combination I can think of

6

(imported comment written by BenKus)

Try “… is NULL …” rather than “… = NULL …”

Ben