→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jpatrick1
Engaged Sweeper
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.
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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.

View solution in original post

2 REPLIES 2
RCorbeil
Honored Sweeper II
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
Engaged Sweeper
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