Returning unique rows

Reporting
1

Hello:

I am trying to create a custom web report in v9.1.1233.0

I’ve been asked to create a report of all actions that are starting “today or later”.

Unfortunately, I’m trying to narrow the results to be one row per action ID, instead of one row per computer.

I used this report as a base: http://www.leewei.com/bigfix/prod/forum/action_start_stop_time.beswrpt

Here is what I have so far for the relevance:

	concatenations of trs of 
(
	td of html 
	(
		if
			(
				exists id of action of it 
			)
		then
			(
				id of action of it as string 
			)
		else
			(
				"ID Missing" 
			)
	)
	& td of html 
	(
		"<A name=%22" & 
		(
			if
				(
					exists name of action of it 
				)
			then
				(
					name of action of it 
				)
			else
				(
					"Action Name Missing" 
				)
		)
		& "%22 href=%22" & 
		(
			if
				(
					exists link href of action of it 
				)
			then
				(
					link href of action of it 
				)
			else
				(
					"" 
				)
		)
		& "%22 target=%22_blank%22>" & 
		(
			if
				(
					exists name of action of it 
				)
			then
				(
					name of action of it 
				)
			else
				(
					"Action Name Missing" 
				)
		)
		& "</A>" 
	)
	& td of 
	(
		if
			(
				exists state of action of it 
			)
		then
			(
				state of action of it 
			)
		else
			(
				"Action State Missing" 
			)
	)
	& td of 
	(
		if
			(
				exists time issued of action of it 
			)
		then
			(
				(
					(
						year of it as string & "-" & month of it as two digits & "-" & day_of_month of it as two digits 
					)
					of date 
					(
						local time zone 
					)
					of it & " " & 
					(
						two digit hour of it as string & ":" & two digit minute of it as string & ":" & two digit second of it as string 
					)
					of time 
					(
						local time zone 
					)
					of it 
				)
				of time issued of action of it 
			)
		else
			(
				"Issued Time Missing" 
			)
	)
	& td of 
	(
		if
			(
				exists start date of action of it 
			and
				exists start time_of_day of action of it 
			)
		then
			(
				(
					(
						year of it as string & "-" & month of it as two digits & "-" & day_of_month of it as two digits 
					)
					of date 
					(
						local time zone 
					)
					of it & " " & 
					(
						two digit hour of it as string & ":" & two digit minute of it as string & ":" & two digit second of it as string 
					)
					of time 
					(
						local time zone 
					)
					of it 
				)
				of 
				(
					(
						start date of it as string & " " & start time_of_day of it as string 
					)
					as local time 
				)
				of action of it 
			)
		else
			(
				if
					(
						exists time issued of action of it 
					)
				then
					(
						(
							(
								(
									year of it as string & "-" & month of it as two digits & "-" & day_of_month of it as two digits 
								)
								of date 
								(
									local time zone 
								)
								of it & " " & 
								(
									two digit hour of it as string & ":" & two digit minute of it as string & ":" & two digit second of it as string 
								)
								of time 
								(
									local time zone 
								)
								of it 
							)
							of time issued of action of it 
						)
					)
				else
					(
						"No Start Date" 
					)
			)
		)
		& td of 
		(
			if
				(
					exists end date of action of it 
				and
					exists end time_of_day of action of it 
				)
			then
				(
					(
						(
							year of it as string & "-" & month of it as two digits & "-" & day_of_month of it as two digits 
						)
						of date 
						(
							local time zone 
						)
						of it & " " & 
						(
							two digit hour of it as string & ":" & two digit minute of it as string & ":" & two digit second of it as string 
						)
						of time 
						(
							local time zone 
						)
						of it 
					)
					of 
					(
						(
							end date of it as string & " " & end time_of_day of it as string 
						)
						as local time 
					)
					of action of it 
				)
			else
				(
					"No End Date" 
				)
		)
		& td of 
		(
			if
				(
					exists issuer of action of it 
				and
					exists name of issuer of action of it 
				)
			then
				(
					name of issuer of action of it 
				)
			else
				(
					"Issued By Missing" 
				)
		)
	)
	of results of bes actions 
	whose
	(
		(
			if
				(
					exists start date of it 
				)
			then
				(
					(
						start date of it as string & " " & start time_of_day of it as string 
					)
					as local time 
				)
			else
				(
					time issued of it 
				)
		)
		> 
		(
			now - 1 * day 
		)
	)

This gives me results similar to the following:

17757,Custom Action 1,Open,2015-03-23 11:01:44,2015-04-01 11:00:42,2015-04-04 11:00:42,first.last@example.com
17757,Custom Action 1,Open,2015-03-23 11:01:44,2015-04-01 11:00:42,2015-04-04 11:00:42,first.last@example.com
17758,Custom Action 2,Open,2015-03-23 11:01:44,2015-04-01 11:00:42,2015-04-04 11:00:42,first.last@example.com
17758,Custom Action 2,Open,2015-03-23 11:01:44,2015-04-01 11:00:42,2015-04-04 11:00:42,first.last@example.com

I would like to merge all the rows that are the same…so, essentially the unique rows.

2

I’d refactor that part to be
of bes actions whose (exists results whose ( time filters ) )

That way you’re getting one value for each Action, not one value for each Result of Action. That will also have the effect effect of changing your returned properties. “exists id of action of it” becomes “exists id of it” for example.

1 Like
3

Thanks, Jason.

Working with a colleague, the relevance for the report has now changed to the following:

	concatenation of trs of 
(
	html 
	(
		"<td>" & item 0 of it as string & "</td><td><a name=%22" & item 1 of it as string & "%22 href=%22" & item 7 of it as string & "%22 target=%22_blank%22>" & item 1 of it as string & "</a></td><td>" & item 2 of it as string & "</td><td>" & multiplicity of item 2 of it as string & "</td><td>" & item 3 of it as string & "</td><td>" & item 4 of it as string & "</td><td>" & item 5 of it as string & "</td><td>" & item 6 of it as string & "</td>" 
	)
)
of 
(
	(
		id of it , name of it , unique values of 
		(
			if
				exists results of it 
			then
				statuses of results of it as string 
			else
				" " 
		)
		, 
		(
			if
				exists start date of it 
			then
				(
					(
						year of it as string & "-" & month of it as two digits & "-" & day_of_month of it as two digits 
					)
					of start date of it & " " & 
					(
						two digit hour of it as string & ":" & two digit minute of it as string & ":" & two digit second of it as string 
					)
					of start time_of_day of it 
				)
			else
				" " 
		)
		, 
		(
			(
				year of it as string & "-" & month of it as two digits & "-" & day_of_month of it as two digits 
			)
			of date 
			(
				local time zone 
			)
			of it & " " & 
			(
				two digit hour of it as string & ":" & two digit minute of it as string & ":" & two digit second of it as string 
			)
			of time 
			(
				local time zone 
			)
			of it 
		)
		of time issued of it , name of issuer of it , state of it , link href of it 
	)
	of bes actions 
	whose
	(
		Group Member Flag of it = False 
	and
		(
			if
				(
					exists start date of it 
				)
			then
				(
					(
						start date of it as string & " " & start time_of_day of it as string 
					)
					as local time 
				)
			else
				(
					time issued of it 
				)
		)
		> 
		(
			now - 1 * day 
		)
	)
)

What we’re struggling with now, is that multiplicity “count” - we want to only return rows where that count is more than 50, but we’re having difficulty coming up with a “whose” or other clause to achieve this.