Maximum Last Report Time for computers

Hi,

So, I’m looking to create a report to show the computer name and the maximum (or minimum) Last Report Time for a bunch of computers. Ideally, for computers with the same name, that have more than one Last Report Time, but if you have a way to show it for computers regardless of there being duplicates with the same name, that would be great too. I have the following, which is a slightly modified version of a report showing number of clients reporting to each relay

<?relevance

(html "<table><tr> <th>Computer</th><th>Newest Last Report Time</th> </tr>" & it & html "</table>") of
concatenation of trs of (
    (   td of (it as string) &
        td of ((maximum of (last report time of it)) as string) 
    )  of unique values of names of bes computers
)


?>

Thanks,
Bob_K

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.

1 Like

Oops, forgot to limit it to only “real” computers. Each place we retrieve “BES Computers”, we’ll want to filter to

bes computers whose (agent type of it = "Native")

The final query, then, would be

(
/* Properties of the computer (item 0 of it) 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 0 of it) 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 whose (agent type of it = "Native"), 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 
    whose (agent type of it = "Native")
)

With that in place, my deployment only has one truly duplicated computer

49671, ENDPOINT-1, ( Tue, 05 May 2020 12:16:58 -0600 ), 1081605978, ENDPOINT-1, ( Wed, 13 Jan 2021 16:07:19 -0600 )

3 Likes

@JasonWalker,

Thank You for the description on how you worked this out and the code!!!

Bob_K

1 Like