Very interesting puzzle! This is probably not the only solution, but I think looping though sets of BES Computers should work pretty well for this case.
The first step is to find the computers that have duplicated names. I have a few in my lab, thanks to using Proxy Agents I see a definition for my “real” computer, as well as VMWare Extenders and OSD Bare Metal definitions for the same computers; at the end of this thread I’ll show how to filter out those proxy machines to avoid false-positives on duplicated computer names.
This first piece returns only those computer names that are duplicated:
(unique values whose (multiplicity of it > 1) of names of bes computers)
Results for me:
BFC
BFI
DC
ENDPOINT-1
OSD
SCCM
Next, we need to build a “set” of computers for each duplicated name. I’ll end up with 6 unique sets of bes computers, one for each duplicated hostname. I can see how many sets there are, and the sizes of each set, by retrieving the “sizes of” for the results:
sizes of
(set of items 0 of (bes computers, it) whose (name of item 0 of it = item 1 of it)) of
(unique values whose (multiplicity of it > 1) of names of bes computers)
Results: 6 sets, with 2 computers in each set
2
2
2
2
2
2
Next, we need to loop through each set, and find the one element from the set that matches the earliest “last report time” of all the members in the set; and repeat that, finding the one element that matches the latest “last report time”
(
/* Computer with the minimum last report time, among computers with this name */
items 0 of (elements of it, it)
whose (last report time of item 0 of it = minimum of last report times of elements of item 1 of it)
/* Computer with the maximum last report time, among computers with this name */
, items 0 of (elements of it, it)
whose (last report time of item 0 of it = maximum of last report times of elements of item 1 of it)
) of
(set of items 0 of (bes computers, it) whose (name of item 0 of it = item 1 of it)) of
(unique values whose (multiplicity of it > 1) of names of bes computers)
Now, “item 0” is the BES Computer with the minimum last report time among all computers of its name, and “item 1” is the BES Computer with the maximum last report time among all computers of its name.
We can pull out whatever Computer Properties we want:
(
/* Properties of the computer (item 0 ) with the minimum last report times */
id of item 0 of it, name of item 0 of it, last report time of item 0 of it,
/* Properties of the computer (item 1) with the maximum last report times */
id of item 1 of it, name of item 1 of it, last report time of item 1 of it
) of
(
/* Computer with the minimum last report time, among computers with this name */
items 0 of (elements of it, it)
whose (last report time of item 0 of it = minimum of last report times of elements of item 1 of it)
/* Computer with the maximum last report time, among computers with this name */
, items 0 of (elements of it, it)
whose (last report time of item 0 of it = maximum of last report times of elements of item 1 of it)
) of
(set of items 0 of (bes computers, it) whose (name of item 0 of it = item 1 of it)) of
(unique values whose (multiplicity of it > 1) of names of bes computers)
Results in
15546983, BFC, ( Wed, 13 Jan 2021 16:11:03 -0600 ), 1611831810, BFC, ( Wed, 13 Jan 2021 16:17:23 -0600 )
10896511, BFI, ( Wed, 13 Jan 2021 16:13:23 -0600 ), 1078737986, BFI, ( Wed, 13 Jan 2021 16:17:23 -0600 )
1076504943, DC, ( Wed, 13 Jan 2021 16:17:23 -0600 ), 1074881384, DC, ( Wed, 13 Jan 2021 16:19:06 -0600 )
49671, ENDPOINT-1, ( Tue, 05 May 2020 12:16:58 -0600 ), 1081605978, ENDPOINT-1, ( Wed, 13 Jan 2021 16:07:19 -0600 )
1625070345, OSD, ( Wed, 13 Jan 2021 16:15:58 -0600 ), 1626974442, OSD, ( Wed, 13 Jan 2021 16:17:23 -0600 )
10705970, SCCM, ( Wed, 13 Jan 2021 16:03:51 -0600 ), 1076999036, SCCM, ( Wed, 13 Jan 2021 16:17:23 -0600 )
Decide which properties you want to retrieve and add a little HTML formatting and I think this should work for you.
