Report: Need Last User login from specific Security Group

[ahmedalhoqani1]

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

[Mister_Nobody · Accepted solution]

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

[ahmedalhoqani1]

thank you so much. 

that work perfectly with me.