Unauthorized Admins by Computer

[Malbanese]

I am looking for a report that will give me all of the unauthorized local admins for each computer. I know that there is a stock report available but it lists the users on a new row, duplicating the Computer ID.

I am looking for a report that will show the computer name and any unautorized admins in one line. Such as:

AssetName | IP Address | IP Location | Admin1 | Admin2 | Admin3 |

I looked at another post wich had a similar approach but the code is from a previous version and didn't work.

Thanks,

Michael

[dsnyrs]

Thank you!!

[dsnyrs]

I accidentally overwrote the Unauthorized Adminstrators report. Could Lansweeper support post the original sql for this so I can restore this report?

Thanks in advance

[Hemoco]

dsnyrs wrote:
I accidentally overwrote the Unauthorized Adminstrators report. Could Lansweeper support post the original sql for this so I can restore this report?

Please use the SQL query below to recreate this report.

Select Top 1000000 tblAssets.AssetUnique,
tblUsersInGroup.AssetID,
tblAssets.Domain,
tblAssets.Description,
tblUsersInGroup.Domainname,
tblUsersInGroup.Username,
tblUsersInGroup.Lastchanged,
tsysOS.Image As icon
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
Where Not Exists(Select Distinct * From (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 Like
tblAssets.AssetName And tblUsersInGroup.Username Like tblUsers.Name
Union
Select tsysadmins.Domain,
tsysadmins.AdminName As username
From tsysadmins
Where tblUsersInGroup.Domainname Like tsysadmins.Domain And
tblUsersInGroup.Username Like tsysadmins.AdminName) DERIVEDTBL) And
tblUsersInGroup.Admingroup = 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[Hemoco · Accepted solution]

SQL doesn't allow for this. Each table record is displayed as a separate line, not as a separate column.