→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Malbanese
Engaged Sweeper III
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
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
SQL doesn't allow for this. Each table record is displayed as a separate line, not as a separate column.

View solution in original post

4 REPLIES 4
dsnyrs
Engaged Sweeper
Thank you!!
dsnyrs
Engaged Sweeper
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
Lansweeper Alumni
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
Lansweeper Alumni
SQL doesn't allow for this. Each table record is displayed as a separate line, not as a separate column.