‎12-23-2015 06:19 AM
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain As Userdomain,
tblCPlogoninfo.logontime
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblCPlogoninfo.logontime > GetDate() - 7 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblCPlogoninfo.logontime Desc
Solved! Go to Solution.
‎01-13-2016 08:57 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
SubQuery2.UserCount,
SubQuery3.[User]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join (Select Top 1000000 SubQuery1.AssetID,
Count(SubQuery1.[User]) As UserCount
From (Select Distinct Top 1000000 tblCPlogoninfo.AssetID,
tblCPlogoninfo.Domain + '\' + tblCPlogoninfo.Username As [User]
From tblCPlogoninfo
Where tblCPlogoninfo.logontime > GetDate() - 30) SubQuery1
Group By SubQuery1.AssetID) SubQuery2 On SubQuery2.AssetID = tblAssets.AssetID
Inner Join (Select Distinct Top 1000000 tblCPlogoninfo.AssetID,
tblCPlogoninfo.Domain + '\' + tblCPlogoninfo.Username As [User]
From tblCPlogoninfo
Where tblCPlogoninfo.logontime > GetDate() - 30) SubQuery3
On SubQuery3.AssetID = tblAssets.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where SubQuery2.UserCount > 1 And tblAssetCustom.State = 1 And
tblComputersystem.Domainrole < 2 And tblComputersystem.PartOfDomain = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
SubQuery3.[User]
‎01-18-2016 07:23 AM
‎01-18-2016 08:09 PM
apurvs wrote:
We would like to have asset & it's all the user's login duration for the period of last 30 days. So that we can analyze and conclude the user with highest login duration as the owner of that asset. Is such kind of report possible?
‎01-14-2016 01:13 AM
‎01-13-2016 08:57 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
SubQuery2.UserCount,
SubQuery3.[User]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join (Select Top 1000000 SubQuery1.AssetID,
Count(SubQuery1.[User]) As UserCount
From (Select Distinct Top 1000000 tblCPlogoninfo.AssetID,
tblCPlogoninfo.Domain + '\' + tblCPlogoninfo.Username As [User]
From tblCPlogoninfo
Where tblCPlogoninfo.logontime > GetDate() - 30) SubQuery1
Group By SubQuery1.AssetID) SubQuery2 On SubQuery2.AssetID = tblAssets.AssetID
Inner Join (Select Distinct Top 1000000 tblCPlogoninfo.AssetID,
tblCPlogoninfo.Domain + '\' + tblCPlogoninfo.Username As [User]
From tblCPlogoninfo
Where tblCPlogoninfo.logontime > GetDate() - 30) SubQuery3
On SubQuery3.AssetID = tblAssets.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where SubQuery2.UserCount > 1 And tblAssetCustom.State = 1 And
tblComputersystem.Domainrole < 2 And tblComputersystem.PartOfDomain = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
SubQuery3.[User]
‎01-11-2016 12:03 AM
‎12-24-2015 12:26 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
SubQuery2.UserCount
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join (Select Top 1000000 SubQuery1.AssetID,
Count(SubQuery1.[User]) As UserCount
From (Select Distinct Top 1000000 tblCPlogoninfo.AssetID,
tblCPlogoninfo.Domain + '\' + tblCPlogoninfo.Username As [User]
From tblCPlogoninfo
Where tblCPlogoninfo.logontime > GetDate() - 30) SubQuery1
Group By SubQuery1.AssetID) SubQuery2 On SubQuery2.AssetID = tblAssets.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where SubQuery2.UserCount > 1 And tblAssetCustom.State = 1 And
tblComputersystem.Domainrole < 2 And tblComputersystem.PartOfDomain = 1
Order By tblAssets.Domain,
tblAssets.AssetName
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now