‎05-11-2017 05:10 PM
With LaptopsPerUser (UsernameL, Total) As (
Select Top 1000000 tblAssets.Username,
Count(tblAssets.Username) As total
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblUsers On tblAssets.AssetID = tblUsers.AssetID
Where tblAssets.AssetName Like '%-lt-%' And tblAssets.AssetName Like '%-lt-%'
And tsysAssetTypes.AssetTypename Like '%windows%'
Group By tblAssets.Username
),
OtherData (AssetName) As (
Select Top 1000000 tblAssets.AssetName, tblAssets.Username
From tblAssets
Where tblAssets.AssetName Like '%-lt-%' And tblAssets.AssetName Like '%-lt-%'
And tsysAssetTypes.AssetTypename Like '%windows%'
)
Select * from LaptopsPerUser
Union
Select * from OtherData
Solved! Go to Solution.
‎05-19-2017 05:12 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
UserCount.Total As UserLastLogonCount,
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 tblState On tblState.State = tblAssetCustom.State
Inner Join (Select Top 1000000 tblAssets.Username,
Count(tblAssets.Username) As Total
From tblAssets
Inner Join tblPortableBattery
On tblAssets.AssetID = tblPortableBattery.AssetID
Group By tblAssets.Username
Having (tblAssets.Username != '') Or
(tblAssets.Username Is Not Null)) As UserCount On tblAssets.Username =
UserCount.Username
Inner Join tblPortableBattery
On tblAssets.AssetID = tblPortableBattery.AssetID
Where UserCount.Total > 1 And tblState.Statename = 'Active'
‎05-19-2017 05:12 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
UserCount.Total As UserLastLogonCount,
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 tblState On tblState.State = tblAssetCustom.State
Inner Join (Select Top 1000000 tblAssets.Username,
Count(tblAssets.Username) As Total
From tblAssets
Inner Join tblPortableBattery
On tblAssets.AssetID = tblPortableBattery.AssetID
Group By tblAssets.Username
Having (tblAssets.Username != '') Or
(tblAssets.Username Is Not Null)) As UserCount On tblAssets.Username =
UserCount.Username
Inner Join tblPortableBattery
On tblAssets.AssetID = tblPortableBattery.AssetID
Where UserCount.Total > 1 And tblState.Statename = 'Active'
‎05-16-2017 11:50 AM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now