/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: chucksteel 4 years ago
Red Belt
0
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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ