→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎01-22-2024 01:49 PM - edited ‎01-22-2024 01:53 PM
Dears need to combine the following 2 report (To generate a report with the last user login from specific security group):
I have the last login report:
Select Top 1000000 UserInfo.Username,
UserInfo.Domain As Userdomain,
UserInfo.LastLogon As UserLastLogon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Inner Join tblAssetCustom On tblAssetCustom.AssetID =
tblCPlogoninfo.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblState.Statename = 'Active'
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) As UserInfo On tblCPlogoninfo.Domain =
UserInfo.Domain And tblCPlogoninfo.logontime = UserInfo.LastLogon And
tblCPlogoninfo.Username = UserInfo.Username
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblState.Statename = 'Active'
Order By Userdomain,
UserInfo.Username
Security Group report:
Select Top 1000000 tblADusers.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Name,
tblADGroups.Name As ADGroupName,
tblADGroups.Description As ADGroupDescription,
tblADusers.Displayname,
Case tblADGroups.GroupType
When -2147483646 Then 'Security - Global'
When -2147483644 Then 'Security - Local'
When -2147483643 Then 'Built-in'
When -2147483640 Then 'Security - Universal'
When 2 Then 'Distribution - Global'
When 4 Then 'Distribution - Local'
When 8 Then 'Distribution - Universal'
End As ADGroupType
From tblADusers
Left Join tblADMembership On tblADMembership.ChildAdObjectID =
tblADusers.ADObjectID
Left Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADGroups.Name = 'SecurityGroupName'
Order By tblADusers.Username,
ADGroupName
thank you
Solved! Go to Solution.
‎01-23-2024 05:11 AM - edited ‎01-23-2024 05:12 AM
I have combined queries but don't check logics:
Select Top 1000000 UserInfo.Username,
UserInfo.Domain As Userdomain,
UserInfo.LastLogon As UserLastLogon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Inner Join tblAssetCustom On tblAssetCustom.AssetID =
tblCPlogoninfo.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblState.Statename = 'Active'
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) As UserInfo On tblCPlogoninfo.Domain =
UserInfo.Domain And tblCPlogoninfo.logontime = UserInfo.LastLogon And
tblCPlogoninfo.Username = UserInfo.Username
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADusers On UserInfo.Username = tblADusers.username And
UserInfo.Domain = tblADusers.userdomain
Inner Join tblADMembership On tblADMembership.ChildAdObjectID =
tblADusers.ADObjectID
Inner Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADGroups.Name = 'domain users' And tblState.Statename = 'Active'
Order By Userdomain,
UserInfo.Username
‎01-23-2024 05:11 AM - edited ‎01-23-2024 05:12 AM
I have combined queries but don't check logics:
Select Top 1000000 UserInfo.Username,
UserInfo.Domain As Userdomain,
UserInfo.LastLogon As UserLastLogon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Inner Join tblAssetCustom On tblAssetCustom.AssetID =
tblCPlogoninfo.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblState.Statename = 'Active'
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) As UserInfo On tblCPlogoninfo.Domain =
UserInfo.Domain And tblCPlogoninfo.logontime = UserInfo.LastLogon And
tblCPlogoninfo.Username = UserInfo.Username
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADusers On UserInfo.Username = tblADusers.username And
UserInfo.Domain = tblADusers.userdomain
Inner Join tblADMembership On tblADMembership.ChildAdObjectID =
tblADusers.ADObjectID
Inner Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADGroups.Name = 'domain users' And tblState.Statename = 'Active'
Order By Userdomain,
UserInfo.Username
‎02-11-2024 04:24 AM
thank you so much.
that work perfectly with me.
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now