+ account's domain and name
+ user account's status (disable, enable)
Select Top 1000000 tblassets.AssetID,
tblassets.Domain,
tblADcomputers.OU,
tblassets.AssetName,
tblADcomputers.Location,
tblADcomputers.Description,
ADOCOMP.sAMAccountName As ManagerName,
ADOCOMP.domain As ManagerDomain,
tblUsersInGroup.Groupname + '->' + tblUsersInGroup.Domainname + '\' +
tblUsersInGroup.Username + Case
When tblADObjects6.sAMAccountName Is Not Null Then
'->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName + '->' +
tblADObjects2.domain + '\' + tblADObjects2.sAMAccountName + '->' +
tblADObjects3.domain + '\' + tblADObjects3.sAMAccountName + '->' +
tblADObjects4.domain + '\' + tblADObjects4.sAMAccountName + '->' +
tblADObjects5.domain + '\' + tblADObjects5.sAMAccountName + '->' +
tblADObjects6.domain + '\' + tblADObjects6.sAMAccountName
When tblADObjects5.sAMAccountName Is Not Null Then
'->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName + '->' +
tblADObjects2.domain + '\' + tblADObjects2.sAMAccountName + '->' +
tblADObjects3.domain + '\' + tblADObjects3.sAMAccountName + '->' +
tblADObjects4.domain + '\' + tblADObjects4.sAMAccountName + '->' +
tblADObjects5.domain + '\' + tblADObjects5.sAMAccountName
When tblADObjects4.sAMAccountName Is Not Null Then
'->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName + '->' +
tblADObjects2.domain + '\' + tblADObjects2.sAMAccountName + '->' +
tblADObjects3.domain + '\' + tblADObjects3.sAMAccountName + '->' +
tblADObjects4.domain + '\' + tblADObjects4.sAMAccountName
When tblADObjects3.sAMAccountName Is Not Null Then
'->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName + '->' +
tblADObjects2.domain + '\' + tblADObjects2.sAMAccountName + '->' +
tblADObjects3.domain + '\' + tblADObjects3.sAMAccountName
When tblADObjects2.sAMAccountName Is Not Null Then
'->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName + '->' +
tblADObjects2.domain + '\' + tblADObjects2.sAMAccountName
When tblADObjects1.sAMAccountName Is Not Null Then
'->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName
Else ''
End object_path,
Case (Select tblusers.Disabled From tblusers
Where tblUsersInGroup.Domainname = tblassets.AssetName And
tblUsersInGroup.Username = tblusers.Name And tblassets.AssetID =
tblusers.AssetID And tblassets.AssetID = tblUsersInGroup.AssetID Union
Select IIF(tblADusers.IsEnabled = 1, 0, 1) From tblADusers
Where Coalesce(tblADObjects6.domain, tblADObjects5.domain,
tblADObjects4.domain, tblADObjects3.domain, tblADObjects2.domain,
tblADObjects1.domain, tblUsersInGroup.Domainname) =
tblADusers.Userdomain And Coalesce(tblADObjects6.sAMAccountName,
tblADObjects5.sAMAccountName, tblADObjects4.sAMAccountName,
tblADObjects3.sAMAccountName, tblADObjects2.sAMAccountName,
tblADObjects1.sAMAccountName, tblUsersInGroup.Username) =
tblADusers.Username)
When 0 Then 'Enabled'
When 1 Then 'Disabled'
Else 'Unknown'
End As AccountStatus,
Coalesce(tblADObjects6.domain, tblADObjects5.domain, tblADObjects4.domain,
tblADObjects3.domain, tblADObjects2.domain, tblADObjects1.domain,
tblUsersInGroup.Domainname) As AccountDomain,
Coalesce(tblADObjects6.sAMAccountName, tblADObjects5.sAMAccountName,
tblADObjects4.sAMAccountName, tblADObjects3.sAMAccountName,
tblADObjects2.sAMAccountName, tblADObjects1.sAMAccountName,
tblUsersInGroup.Username) As AccountUsername,
cpli.min_logontime,
cpli.max_logontime
From tblassets
Inner Join tblADcomputers On tblassets.AssetID = tblADcomputers.AssetID
Left Join tblADObjects As ADOCOMP On ADOCOMP.ADObjectID =
tblADcomputers.ManagerADObjectId
Inner Join tblUsersInGroup On tblassets.AssetID = tblUsersInGroup.AssetID And
tblUsersInGroup.admingroup = 1 And Not Exists(Select tblAssets.AssetName
As Domain,
tblUsers.Name As Username
From tblAssets Inner Join tblUsers On tblAssets.AssetID =
tblUsers.AssetID
Where tblUsers.BuildInAdmin = 1 And tblUsersInGroup.Domainname =
tblAssets.AssetName And tblUsersInGroup.Username = tblUsers.Name) And
Not Exists(Select tsysadmins.Domain,
tsysadmins.AdminName As username From tsysadmins
Where tblUsersInGroup.Domainname Like tsysadmins.Domain And
tblUsersInGroup.Username Like tsysadmins.AdminName)
Left Join tblADObjects On tblUsersInGroup.Domainname = tblADObjects.domain And
tblUsersInGroup.Username = tblADObjects.sAMAccountName
Left Join tblADMembership On tblADObjects.ADObjectID =
tblADMembership.ParentAdObjectID
Left Join tblADObjects tblADObjects1 On tblADObjects1.ADObjectID =
tblADMembership.ChildAdObjectID
Left Join tblADMembership tblADMembership1 On tblADObjects1.ADObjectID =
tblADMembership1.ParentAdObjectID
Left Join tblADObjects tblADObjects2 On tblADMembership1.ChildAdObjectID =
tblADObjects2.ADObjectID
Left Join tblADMembership tblADMembership2 On tblADObjects2.ADObjectID =
tblADMembership2.ParentAdObjectID
Left Join tblADObjects tblADObjects3 On tblADMembership2.ChildAdObjectID =
tblADObjects3.ADObjectID
Left Join tblADMembership tblADMembership3 On tblADObjects3.ADObjectID =
tblADMembership3.ParentAdObjectID
Left Join tblADObjects tblADObjects4 On tblADMembership3.ChildAdObjectID =
tblADObjects4.ADObjectID
Left Join tblADMembership tblADMembership4 On tblADObjects4.ADObjectID =
tblADMembership4.ParentAdObjectID
Left Join tblADObjects tblADObjects5 On tblADMembership4.ChildAdObjectID =
tblADObjects5.ADObjectID
Left Join tblADMembership tblADMembership5 On tblADObjects5.ADObjectID =
tblADMembership5.ParentAdObjectID
Left Join tblADObjects tblADObjects6 On tblADMembership5.ChildAdObjectID =
tblADObjects6.ADObjectID
Left Join (Select i.assetid,
i.domain,
i.username,
Min(i.logontime) min_logontime,
Max(i.logontime) max_logontime
From tblcplogoninfo As i
Group By i.assetid,
i.domain,
i.username) cpli On tblassets.assetid = cpli.assetid And
Coalesce(tblADObjects6.domain, tblADObjects5.domain, tblADObjects4.domain,
tblADObjects3.domain, tblADObjects2.domain, tblADObjects1.domain,
tblUsersInGroup.Domainname) = cpli.domain And
Coalesce(tblADObjects6.sAMAccountName, tblADObjects5.sAMAccountName,
tblADObjects4.sAMAccountName, tblADObjects3.sAMAccountName,
tblADObjects2.sAMAccountName, tblADObjects1.sAMAccountName,
tblUsersInGroup.Username) = cpli.username
Where tblassets.oscode Like '%s' And Not Exists(Select tsysadmins.Domain,
tsysadmins.AdminName As username From tsysadmins
Where Coalesce(tblADObjects6.domain, tblADObjects5.domain,
tblADObjects4.domain, tblADObjects3.domain, tblADObjects2.domain,
tblADObjects1.domain, tblUsersInGroup.Domainname) Like tsysadmins.Domain
And Coalesce(tblADObjects6.sAMAccountName, tblADObjects5.sAMAccountName,
tblADObjects4.sAMAccountName, tblADObjects3.sAMAccountName,
tblADObjects2.sAMAccountName, tblADObjects1.sAMAccountName,
tblUsersInGroup.Username) Like tsysadmins.AdminName)