Question on session relevance

Content Authoring
1

Hi,
We got in our environment a number of Policy actions “Open Ended” to fix certain issues and as part of clean-up effort would like to have report that would show for each policy action last time it has run on a client so that way if we create Policy action a year ago and last time it run was 3 months ago we can safely assume it’s not needed anymore.
Would like to create this a scheduled web report so that the operator who created the policy action can also go ahead and stop/delete the action if not needed anymore.

I’ve got below session relevance that returns data for specific action and last end time is has run for each computer, what I would like to see if actionID, ActionName, lasttime it has run simple as that

(id of item 0 of it, name of item 0 of it, maximum of (end time of item 1 of it)) of (it, results of it) of bes actions whose (id of it = 5219212)

Output
5219212, PC1-POLICY - JOB - Push Job for App1, ( Mon, 18 Nov 2024 16:09:31 +0000 )
5219212, PC1-POLICY - JOB - Push Job for App1, ( Mon, 18 Nov 2024 14:30:09 +0000 )
5219212, PC1-POLICY - JOB - Push Job for App1, ( Mon, 18 Nov 2024 13:35:53 +0000 )
5219212, PC1-POLICY - JOB - Push Job for App1, ( Mon, 18 Nov 2024 13:43:53 +0000 )
5219212, PC1-POLICY - JOB - Push Job for App1, ( Tue, 19 Nov 2024 11:58:23 +0000 )

Output I would like to have is
5219212, PC1-POLICY - JOB - Push Job for App1, ( Tue, 19 Nov 2024 11:58:23 +0000 )

Session relevance will be updated to only report back on actions that have “policy” in the name ideally would get list of actions and corresponding ids and last report times

I need to do some grouping that’s how I would do it in SQL

any advice/help would be appreciated

2

The trick here is that because you’ve already split off (it, results of it) into a tuple, you are getting a separate item for every result of the action. Think of that as being split out into
(521912, Computer1_Result)
(521912, Computer2_Result)
(521912, Computer3_Result)

Then when you check for the maximum of (end time of item 1 of it), that’s only considering the maximum of one single computer’s result – and since the computer only had one end time, that single end time is the maximum.

The other issue is that because you’re building off the plural ‘results’, any action that doesn’t have any results at all are getting dropped out of the list - and those specifically are the actions you really want to see!

So the first thing you have to do is build your list of actions, and then inside the parentheses handle the maximum of (all the results of this action).

Noting that ‘maximum’ is a singular, it will throw an error if there are no results for the action. We can use that to our advantage by using the pipe operator | to replace the error with a value of our choosing. Since the pipe operator substitution must be the same data type, we also have to cast our time measure ‘as string’ so we can use the string <none> as our error handler replacement.

For filtering the Actions, I’ve just chosen every Policy action in my deployment - every action that is still Open and has no expiration date. Feel free to add any Name or ID filters or whatever you like, just keep that over in the ‘bes actions’ whose clause.

Give this a try -

q: (id of it, name of it, name of issuer of it, time issued of it, maximum of start times of results of it as string | "<never>") of bes actions whose (state of it = "Open" and not end flag of it)

A: 2279, Lab Application Deployment, mo, ( Thu, 02 Feb 2023 11:47:33 -0600 ), ( Mon, 18 Sep 2023 12:00:48 -0600 )
A: 2280, Deploy: Configuration 1-Google Chrome, mo, ( Thu, 02 Feb 2023 11:47:33 -0600 ), ( Mon, 18 Sep 2023 12:00:48 -0600 )
A: 2301, Schedule VM Manager Tool Scan Results Upload (10.0.11.0), bfi_service, ( Thu, 02 Feb 2023 12:53:43 -0600 ), ( Thu, 16 Feb 2023 10:22:22 -0600 )
A: 3112, Lab Network Configuration, mo, ( Sun, 26 Feb 2023 13:29:42 -0600 ), ( Sun, 26 Feb 2023 13:29:53 -0600 )
A: 3113, Configure Windows Firewall: Allow Inbound ICMP Ping, mo, ( Sun, 26 Feb 2023 13:29:42 -0600 ), <never>
A: 3114, Configure Windows Firewall: Allow inbound UDP on BigFix Port, mo, ( Sun, 26 Feb 2023 13:29:42 -0600 ), <never>
A: 3115, Configure Windows Firewall: Allow Remote Desktop Services, mo, ( Sun, 26 Feb 2023 13:29:42 -0600 ), <never>
A: 3116, Configure Windows Firewall: Allow File and Printer Sharing group., mo, ( Sun, 26 Feb 2023 13:29:42 -0600 ), <never>
3 Likes
3

Jason,
thx again for your help and detailed explenation, much appreciated!!!

4

Just one follow-up question (cosmetic). I’m trying to make the report a bit more readable and have the ouput in a table.
When trying to run the relevance I’m getting below error

Action Info: The operator “td” is not defined.

<id=“future2”>Action Info:

<?relevance concatenation of trs of (td of item 0 of it & td of item 1 of it & td of item 2 of it & td of item 3 of it) of (name of it, name of issuer of it, time issued of it, maximum of start times of results of it as string | "") of bes actions whose (id of it as string is contained by set of ("810191";"810192";"810193";"810195";"810195";"810196";"810197";"810198";"810199"))?>
Action NameBigfix OperatorAction Start timeAction Last Run

I’ve tried various combinations but not succeeding, I do have other tables working fine but this one I’m not able to figure out.

5

The ‘time issued of it’ is a ‘time’ type, and to make a ‘td’ from it you have to cast it as string first. Try

concatenation of trs of (td of item 0 of it & td of item 1 of it & td of item 2 of it & td of item 3 of it) of (name of it, name of issuer of it, time issued of it as string, maximum of start times of results of it as string | "") of bes actions whose (id of it as string is contained by set of ("810191";"810192";"810193";"810195";"810195";"810196";"810197";"810198";"810199"))

1 Like
6

Jason,
thx that did the trick!

again appreciate all the help

1 Like