Notification

Icon
Error

Multiple Computers per User

Posted: Wednesday, July 29, 2020 7:13:28 PM(UTC)
brodiemac-too

brodiemac-too

Member Original PosterPosts: 22
0
Like
I our environment, everyone has one computer. However, there are some people who have been issued two. In this case, both computers are listed as Windows Computers Logged Into under the user profile. How can I generate a report that lists users with more than one computer and list the computers?
RC62N
#1RC62N Member Posts: 455  
posted: 7/29/2020 7:58:09 PM(UTC)
Give this a try:
Code:
Select Top 1000000
  tblAssets.Userdomain,
  tblAssets.Username,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblComputerSystem ON tblComputerSystem.AssetID = tblAssets.AssetID
  Inner Join (SELECT
                tblAssets.Userdomain,
                tblAssets.Username,
                Count(*) AS UserCount
              FROM
                tblAssets
                Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
				Inner Join tblComputerSystem ON tblComputerSystem.AssetID = tblAssets.AssetID
              WHERE
                tblAssetCustom.State = 1
				AND tblComputerSystem.DomainRole < 2  -- non-servers
                AND tblAssets.Userdomain IS NOT NULL
                AND tblAssets.Username IS NOT NULL
              GROUP BY
                tblAssets.Userdomain,
                tblAssets.Username) AS UserCount ON UserCount.UserDomain=tblAssets.UserDomain
                                                    AND UserCount.UserName=tblAssets.UserName
                                                    AND UserCount.UserCount > 1  -- only those logged into more than 1 machine
Where
  tblAssetCustom.State = 1
  And tblComputerSystem.DomainRole < 2  -- non-servers
Order By
  tblAssets.Userdomain,
  tblAssets.Username,
  tblAssets.AssetName

The sub-SELECT makes a list of domain/users last logged on to active non-servers.

The JOIN limits the list to only those logged on to more than one machine, and the fact that it's an INNER join limits the main selection to only members of that list.
brodiemac-too
#2brodiemac-too Member Original PosterPosts: 22  
posted: 7/30/2020 12:43:38 PM(UTC)
Spot on, thank you!

Originally Posted by: RC62N Go to Quoted Post
Give this a try:
Code:
Select Top 1000000
  tblAssets.Userdomain,
  tblAssets.Username,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblComputerSystem ON tblComputerSystem.AssetID = tblAssets.AssetID
  Inner Join (SELECT
                tblAssets.Userdomain,
                tblAssets.Username,
                Count(*) AS UserCount
              FROM
                tblAssets
                Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
				Inner Join tblComputerSystem ON tblComputerSystem.AssetID = tblAssets.AssetID
              WHERE
                tblAssetCustom.State = 1
				AND tblComputerSystem.DomainRole < 2  -- non-servers
                AND tblAssets.Userdomain IS NOT NULL
                AND tblAssets.Username IS NOT NULL
              GROUP BY
                tblAssets.Userdomain,
                tblAssets.Username) AS UserCount ON UserCount.UserDomain=tblAssets.UserDomain
                                                    AND UserCount.UserName=tblAssets.UserName
                                                    AND UserCount.UserCount > 1  -- only those logged into more than 1 machine
Where
  tblAssetCustom.State = 1
  And tblComputerSystem.DomainRole < 2  -- non-servers
Order By
  tblAssets.Userdomain,
  tblAssets.Username,
  tblAssets.AssetName

The sub-SELECT makes a list of domain/users last logged on to active non-servers.

The JOIN limits the list to only those logged on to more than one machine, and the fact that it's an INNER join limits the main selection to only members of that list.


Active Discussions

Lansweeper Email to close ticket
by  wgonzalez_hope   Go to last post Go to first unread
Last post: Yesterday at 3:54:01 PM(UTC)
Lansweeper Mass update assets through csv
by  B.L.  
Go to last post Go to first unread
Last post: Yesterday at 3:30:21 PM(UTC)
Lansweeper Restrict the access to the knowledgebase
by  ABEAL   Go to last post Go to first unread
Last post: Yesterday at 1:38:25 PM(UTC)
Lansweeper Wake on Lan Issues
by  Christophe  
Go to last post Go to first unread
Last post: Yesterday at 1:09:12 PM(UTC)
Lansweeper Reason: No email address found for the following user
by  Juha Otava   Go to last post Go to first unread
Last post: Yesterday at 9:59:41 AM(UTC)
Lansweeper Windows 10 Upgrade to 2004
by  CyberCitizen  
Go to last post Go to first unread
Last post: Yesterday at 8:47:16 AM(UTC)
Lansweeper Scanning despite exclusion
by  pskup   Go to last post Go to first unread
Last post: Yesterday at 7:27:08 AM(UTC)
Lansweeper Export ticket to CSV
by  KeithBecker  
Go to last post Go to first unread
Last post: 8/13/2020 5:56:10 PM(UTC)