Skip to content

Instantly share code, notes, and snippets.

@Colby-PDQ
Last active March 1, 2024 15:27
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Colby-PDQ/7bd46ce363f4b37ae90dd4c18ac31bed to your computer and use it in GitHub Desktop.
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
<?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
&lt;ComputerFilter&gt;
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>
@Colby-PDQ
Copy link
Author

Rev 2: Use the SID for the Administrators group instead of the name.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment