Query KACE for users who have more than 1 laptop?
Hi,
Looking for help on designing a query for KACE, to help us find out what users have more than one laptop in their possession.
Something like:
1) KACE counts number of devices assigned to each user
2) KACE reports back any user with a device count greater than 1
Has anyone done this?
Thanks,
D
Answers (3)
it really depends what you _REALLY_ need.
You can create a report using the wizard which you can improve with a count()-statement to count all nessesary numbers.
Try this report, it simply uses the ASSET table to link to USER, so you can see the name and MACHINE to show the chassis type, it will only work if your Device Names are the same as their associated asset names.
SELECT USER.FULL_NAME, ASSET.NAME, MACHINE.CHASSIS_TYPE
FROM (ASSET ASSET
INNER JOIN MACHINE MACHINE ON (ASSET.NAME = MACHINE.NAME))
INNER JOIN USER USER ON (ASSET.OWNER_ID = USER.ID)
If you put this into your SMA as an SQL report and choose to Break Columns on FULL_NAME it will be obvious which users have multiple devices assigned to them
Personally I prefer to use just the Machine table because I get to see people are using multiple machines that might not have been intended for them when I assigned them as assets. I exclude the Administrator account and anything when the last user has not been registered:
Select
MACHINE.USER_FULLNAME,
Count(MACHINE.NAME) As Count_NAME,
Machines.NAME
From
MACHINE Left Join
(Select
*
From
MACHINE) Machines On Machines.USER_FULLNAME = MACHINE.USER_FULLNAME
Where
MACHINE.USER_FULLNAME <> "" And
MACHINE.USER_FULLNAME <> "Administrator"
Group By
MACHINE.USER_FULLNAME,
Machines.NAME
Having
Count(MACHINE.NAME) > 1
