Single User Multiple Windows Assets

[s_subramanian]

Hi,

I'm trying to find a list of users who has been assigned multiple windows assets.

The below query which i have written is giving me zero results and i know something is not right in the report and can't able to figure that out

Any help would be helpful

Select Top 1000000 tblAssets.AssetID,
tblAssets.UserName As [Scanned GPID],
tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblADusers.Username As [AD.GPID],
tblADusers.Name As [AD.Username],
tblAssets.IPAddress,
tsysIPLocations.IPLocation As [IP location],
tblAssetCustom.Custom6 As [Assigned Location],
tblAssetCustom.Custom7 As [Sub Location],
tblAssetCustom.Custom4 As [FPR Unit]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Left Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Left Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblAssetCustom.State = 1 And tsysAssetTypes.AssetTypename = 'Windows'
Group By tblAssets.AssetID,
tblAssets.UserName,
tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblADusers.Username,
tblADusers.Name,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Custom6,
tblAssetCustom.Custom7,
tblAssetCustom.Custom4
Having Count(tblAssets.UserName) > 1
Order By tblAssets.UserName

[Susan_A · Accepted solution]

TblAssets.Username only stores the last user to log into each computer. You are counting the number of tblAssets.Username entries per computer, which will always be 1 and never more than 1. For a complete list of logon events, you need to use tblCPlogoninfo instead. If your goal is to list users that have logged into more than one computer, have a look at the sample query below.

I would recommend making use of the database dictionary when building reports. It explains in great detail what each table and field stores.

Select Top 1000000 SubQuery2.Username,
  SubQuery2.Userdomain,
  tblADusers.Name,
  SubQuery2.ComputersLoggedInto,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssetCustom.Serialnumber,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Custom6 As AssignedLocation,
  tblAssetCustom.Custom7 As SubLocation,
  tblAssetCustom.Custom4 As FPRunit,
  Max(tblCPlogoninfo.logontime) As MaxLogon
From (Select Top 1000000 SubQuery1.Username,
    SubQuery1.Domain As Userdomain,
    Count(SubQuery1.AssetID) As ComputersLoggedInto
  From (Select Distinct Top 1000000 tblCPlogoninfo.Username,
      tblCPlogoninfo.Domain,
      tblCPlogoninfo.AssetID
    From tblCPlogoninfo
      Inner Join tblAssetCustom On tblAssetCustom.AssetID =
        tblCPlogoninfo.AssetID
    Where tblAssetCustom.State = 1) SubQuery1
  Group By SubQuery1.Username,
    SubQuery1.Domain) SubQuery2
  Left Join tblADusers On tblADusers.Username = SubQuery2.Username And
    tblADusers.Userdomain = SubQuery2.Userdomain
  Inner Join tblCPlogoninfo On tblCPlogoninfo.Username = SubQuery2.Username And
    tblCPlogoninfo.Domain = SubQuery2.Userdomain
  Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
    And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where tblAssetCustom.State = 1 And SubQuery2.ComputersLoggedInto > 1
Group By SubQuery2.Username,
  SubQuery2.Userdomain,
  tblADusers.Name,
  SubQuery2.ComputersLoggedInto,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssetCustom.Serialnumber,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Custom6,
  tblAssetCustom.Custom7,
  tblAssetCustom.Custom4
Order By SubQuery2.Userdomain,
  SubQuery2.Username