Community FAQ
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
ahmedalhoqani1
Engaged Sweeper

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

2.14.0.0
2.14.0.0
1 ACCEPTED SOLUTION
Mister_Nobody
Honored Sweeper II

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

 

 

View solution in original post

2 REPLIES 2
Mister_Nobody
Honored Sweeper II

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

 

 

thank you so much. 

that work perfectly with me. 

Product Discussions

Share feedback, exchange ideas and find answers to Lansweeper product questions.

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now