cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
brodiemac2
Champion Sweeper
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?
2 REPLIES 2
RCorbeil
Honored Sweeper II
Give this a try:
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.
Spot on, thank you!

RC62N wrote:
Give this a try:
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.