Last active
March 1, 2024 15:27
-
-
Save Colby-PDQ/7bd46ce363f4b37ae90dd4c18ac31bed to your computer and use it in GitHub Desktop.
PDQ Inventory SQL Report to find computers whose Current User is a local admin
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?xml version="1.0" encoding="utf-8"?> | |
<AdminArsenal.Export Code="PDQInventory" Name="PDQ Inventory" Version="17.1.0.0" MinimumVersion="3.1"> | |
<Report> | |
<ReportDefinition name="Definition"> | |
<Sql>-- Created by Colby Bouma for: https://old.reddit.com/r/pdq/comments/bty5po/request_wmi_query_check_if_user_is_local/ | |
SELECT | |
Computers.Name AS "Computer Name" | |
, Computers.CurrentUser AS "Current User" | |
, LocalGroupMembers.UserName AS "Local Group Member Username" | |
FROM | |
Computers | |
INNER JOIN | |
LocalGroupMembers USING (ComputerId) | |
INNER JOIN | |
LocalGroups USING (LocalGroupId) | |
WHERE | |
-- Enables filtering by Collection | |
<ComputerFilter> | |
AND | |
-- Find computers whose Current User contains one of the entries from its Local Group Members table | |
-- Source for LIKE trick: https://stackoverflow.com/a/29766435 | |
Computers.CurrentUser LIKE '%' || LocalGroupMembers.UserName || '%' | |
AND | |
-- Only look at the Administrators group, using the SID | |
LocalGroups.SID = 'S-1-5-32-544' | |
AND | |
-- Username blacklist | |
LocalGroupMembers.UserName NOT IN ( | |
'Administrator' | |
, 'The.Boss' | |
) | |
ORDER BY | |
Computers.Name COLLATE NOCASE</Sql> | |
<ReportDefinitionTypeName>SqlReportDefinition</ReportDefinitionTypeName> | |
</ReportDefinition> | |
<Created>2019-07-10T16:39:17.0000000-06:00</Created> | |
<Description></Description> | |
<IsNew value="false" /> | |
<Modified>2019-07-11T11:36:37.0000000-06:00</Modified> | |
<Name>Current User is a local admin</Name> | |
<Path>Reports\Current User is a local admin</Path> | |
<ReportFolderId value="1" /> | |
<TypeName>SqlReport</TypeName> | |
<ReportType>SqlReport</ReportType> | |
</Report> | |
</AdminArsenal.Export> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Rev 2: Use the SID for the Administrators group instead of the name.