Need a Report of Users Assigned to Multiple Systems
I found someone posted a report of all users with multiple systems, but it doesn't go by assignee names. I need to find a list of any users that are actually assigned by assignee name to more than one system. Is that possible? Thanks in advance!.
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
4 years ago
SELECT OWNER_ID, USER.USER_NAME, COUNT(ASSET.ID) as Assets,
GROUP_CONCAT(M.NAME) as Computers
FROM ASSET
LEFT JOIN USER on USER.ID = ASSET.OWNER_ID
LEFT JOIN MACHINE M on M.ID = ASSET.MAPPED_ID
WHERE ASSET_TYPE_ID = 5
and OWNER_ID != 0
GROUP BY OWNER_ID
HAVING Assets > 1
ORDER BY Assets DESC
Comments:
-
Thanks for the answer. This seems to only give me:
Machine ID System Name IP Address Last Sync Machine Connected BIOS Serial Number
I would like to see
Assignee Name System Name(s)
Is that possible? Thanks. - woody1878 4 years ago-
Just assignee name and system names?
SELECT USER.FULL_NAME as 'Assignee Name',
GROUP_CONCAT(M.NAME) as 'System Names'
FROM ASSET
LEFT JOIN USER on USER.ID = ASSET.OWNER_ID
LEFT JOIN MACHINE M on M.ID = ASSET.MAPPED_ID
WHERE ASSET_TYPE_ID = 5
and OWNER_ID != 0
and USER.USER_NAME not like '%computer'
GROUP BY OWNER_ID
HAVING COUNT(ASSET.ID) > 1
ORDER BY USER.FULL_NAME DESC - chucksteel 4 years ago-
Yes assignee names and system names. But not for users that have more than 1 asset. Only for users that have more than 1 inventory system. We have KACE set to not delete assets when inventory devices are deleted so that we can preserve history of who owned what. So when I run this report and it uses asset, I have many users that show up on the report but only have 1 system listed. That's because they have multiple assets but only 1 inventory system. Is there a way to use the count of inventoried devices rather than count of assets? Thanks again! - woody1878 4 years ago
-
Try changing this line:
HAVING COUNT(ASSET.ID) > 1
To this:
HAVING COUNT(M.ID) > 1
The report is limited to device assets, so it sounds like your users have multiple device assets, even if they only have one active machine in the inventory. - chucksteel 4 years ago -
Yea that still only shows assets, so I have users showing up that only have one active machine in inventory. Is there a report that can be run that shows users with more than one active machine in inventory? Maybe I'm just starting with the wrong report. I definitely do not want assets. Thanks. - woody1878 4 years ago
