Merge reports

[tamer]

Hi all,

I'm having trouble combining 2 reports into 1.

I have two reports i run separately. I've tried and failed to combine them.

Please can someone assist and improve if they see fit! 🙂


Here are my two reports.

1st one

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblUsersInGroup.Domainname,
tblUsersInGroup.Username,
tblUsersInGroup.Lastchanged,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblUsersInGroup
Inner Join tblAssets On tblUsersInGroup.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where tblAssets.AssetName Not Like '%VM%'
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) And
tblUsersInGroup.Admingroup = 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName



2nd one
Select Top 1000000 tblADusers.Username,
tblADusers.Displayname,
tblADusers.email,
tblADusers.EmployeeID,
Replace(Replace(Replace(Replace(tblADusers.OU, 'OU=Users,', ''),
',DC=domain,', ''), 'DC=com', ''), 'OU=', '') As Department,
tblADGroups.Name
From tblADusers
Inner Join tblADMembership On
tblADusers.ADObjectID = tblADMembership.ChildAdObjectID
Inner Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADGroups.Name Not Like 'rol_%'


Goal is to see Unauthorized administrators but if a user is member of an ADgroup which is called like 'rol_%', the user should not appear in the report.

[pryan67]

xch5834 wrote:
Hi all,

I'm having trouble combining 2 reports into 1.

I have two reports i run separately. I've tried and failed to combine them.

Please can someone assist and improve if they see fit! 🙂


Here are my two reports.

1st one

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblUsersInGroup.Domainname,
tblUsersInGroup.Username,
tblUsersInGroup.Lastchanged,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblUsersInGroup
Inner Join tblAssets On tblUsersInGroup.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where tblAssets.AssetName Not Like '%VM%'
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) And
tblUsersInGroup.Admingroup = 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName



2nd one
Select Top 1000000 tblADusers.Username,
tblADusers.Displayname,
tblADusers.email,
tblADusers.EmployeeID,
Replace(Replace(Replace(Replace(tblADusers.OU, 'OU=Users,', ''),
',DC=domain,', ''), 'DC=com', ''), 'OU=', '') As Department,
tblADGroups.Name
From tblADusers
Inner Join tblADMembership On
tblADusers.ADObjectID = tblADMembership.ChildAdObjectID
Inner Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADGroups.Name Not Like 'rol_%'


Goal is to see Unauthorized administrators but if a user is member of an ADgroup which is called like 'rol_%', the user should not appear in the report.

Can't you just add

AND tblADGroups.Name Not Like 'rol_%'

to the first report?

[tamer]

No one there who can help?