Report that would show Users that have multiple computers

[jpatrick1]

Some how looking at last login and match is the last login is the same on two or more computers so I could see if users have more then one computer they are using.

[RCorbeil · Accepted solution]

Try this as a starting point:

SELECT Distinct Top 1000000
  tblAssets.Userdomain,
  tblAssets.Username,
  LastUserOn.AssetCount
FROM
  tblAssets
  INNER JOIN tblAssetCustom    ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
  INNER JOIN tbldomainroles    ON tbldomainroles.Domainrole = tblComputersystem.Domainrole
  INNER JOIN tsysOS            ON tblAssets.OScode = tsysOS.OScode
  LEFT JOIN   (SELECT
                 a.UserDomain,
                 a.Username,
                 Count(*) AS AssetCount
               FROM
                 tblAssets AS a
                 INNER JOIN tblAssetCustom    AS c ON a.AssetID = c.AssetID
                 INNER JOIN tblComputersystem AS s ON a.AssetID = s.AssetID
                 INNER JOIN tbldomainroles    AS r ON r.Domainrole = s.Domainrole
                 INNER JOIN tsysOS            AS o ON a.OScode = o.OScode
               WHERE
                 c.State = 1                  -- active asset
                 AND s.Domainrole < 2         -- not server
                 AND o.OSname like 'Win%'     -- Windows
               GROUP BY
                 a.UserDomain,
                 a.Username
              ) AS LastUserOn ON LastUserOn.UserDomain = tblAssets.UserDomain AND LastUserOn.Username = tblAssets.Username
WHERE
  tblAssetCustom.State = 1
  AND tblComputersystem.Domainrole < 2
  AND LastUserOn.AssetCount > 1
ORDER BY
  tblAssets.Userdomain,
  tblAssets.Username

The main SELECT pulls a distinct list of users from active assets.

The sub-SELECT pulls a list of active assets running Windows (tweak if you want to see more than Windows machines; I don't, so I can't thoroughly test that) and counts those having the same user domain/name.

[jpatrick1]

I think I found my own solution from a old report not sure of its origin so credit is not mine. The bold 5 can be changed to 1 or higher if you want to see computers only if they have been logged into more than a certain thresh hold.


Select Top 1000000 tblAssets.Username,
tblAssets.Userdomain,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
SubQuery1.Count As LoginCount
From tblAssets
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain As Userdomain,
tblCPlogoninfo.AssetID,
Count(tblCPlogoninfo.ID) As Count
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
tblCPlogoninfo.AssetID
Having Count(tblCPlogoninfo.ID) >= 5) SubQuery1 On SubQuery1.Username =
tblAssets.Username And SubQuery1.Userdomain = tblAssets.Userdomain
And SubQuery1.AssetID = tblAssets.AssetID
Inner Join (Select Top 1000000 tblAssets.Username,
tblAssets.Userdomain,
Count(tblAssets.AssetID) As Count
From tblAssets
Group By tblAssets.Username,
tblAssets.Userdomain
Having Count(tblAssets.AssetID) > 1
Order By tblAssets.Userdomain,
tblAssets.Username) SubQuery2 On SubQuery2.Username = tblAssets.Username And
SubQuery2.Userdomain = tblAssets.Userdomain
Order By tblAssets.Userdomain,
tblAssets.Username,
tblAssets.Domain,
tblAssets.AssetName