Returning unique combinations of properties

(imported topic written by rad.ricka91)

Hi there,

how am I able to return all unique combinations of two properties in a database? I’m aware of the “unique values” operator but somehow I’m unable to sensibly join up the two properties.

To make it less abstract let’s say I have City and Country as two defined properties and would like a list of Country, City & Client count. This is easy to do with filters but that’s somewhat less applicable for a ‘dashboard’ view. I think it’s safe to assume that there will always be tree relationship between the properties, i.e. one City can’t be in multiple countries.

Same logic will apply when following the Infrastructure, Capability, Logical Asset model of ITIL, so it would be great to get this one for custom reporting.

Thanks,

R.

(imported comment written by brolly3391)

Concatenate the 2 fields and then use the Unique Values operator on the concatenated result?

(imported comment written by jessewk)

How about something like this:

(it & ", " & multiplicity of it as string) of unique values of (value of result from (bes property whose (name of it = “OS”)) of it & ", " & value of result from (bes property whose (name of it = “CPU”)) of it) of bes computers

Gives you “OS, CPU, number of computers with this OS and CPU”:

Win2000 5.0.2195, 1400 MHz Pentium 4, 1

Win2000 5.0.2195, 2200 MHz AMD Athlon™ 64 X2 Dual Core Processor 4400+, 1

Win2003 5.2.3790, 2800 MHz Pentium 4, 2

WinXP 5.1.2600, 1600 MHz Pentium M, 1

WinXP 5.1.2600, 3000 MHz Pentium 4, 2

(imported comment written by rad.ricka91)

Jesse, this seems to work well; however it breaks with "Singular expression refers to nonexistent object as soon as I try substituting one of the properties with a custom one.

For example, we’ve defined:

Country

as

value of setting “Country” of client

I wouldn’t expect the underlying query to change (?)

R.

(imported comment written by jessewk)

Rad,

Chances are that some computer has not reported the property or does not have a value for it. You should try to wrap everything with error checking. Try something like this:

( it & 
", " & multiplicity of it as string ) of unique values of ( ( 

if ( ( exist bes property whose ( name of it = 
"OS" ) AND exists result from ( bes property whose ( name of it = 
"OS" ) ) of it AND exists value of result from ( bes property whose ( name of it = 
"OS" ) ) of it ) ) then ( value of result from ( bes property whose ( name of it = 
"OS" ) ) of it ) 

else ( 
"N/A" ) ) & 
", " & ( 

if ( ( exist bes property whose ( name of it = 
"CPU" ) AND exists result from ( bes property whose ( name of it = 
"CPU" ) ) of it AND exists value of result from ( bes property whose ( name of it = 
"CPU" ) ) of it ) ) then ( value of result from ( bes property whose ( name of it = 
"CPU" ) ) of it ) 

else ( 
"N/A" ) ) ) of bes computers

(imported comment written by rad.ricka91)

Thanks Jesse,

that was the one I was looking for, unfortunately at this stage it might be loads easier to do this with standard SQL query(?). I mean the code has become real dog’s dinner…

R.

(imported comment written by jessewk)

How about a much shorter, faster, nicer version?

(it & ", " & multiplicity of it as string) of unique values of ((if (exist result (bes property “OS” , it)) then (value of result (bes property “OS” , it)) else (“N/A”)) & ", " & (if (exist result (bes property “CPU” , it)) then (value of result (bes property “CPU” , it)) else (“N/A”)) of it) of bes computers