SSRS query for SCCM local admin report

Mike G 71 Reputation points
2023-02-06T18:52:20.33+00:00

I am using SCCM to inventory members of the local administrators group on all of my endpoints and I have a SSRS report that I use for reporting. I am trying to modify the query in the report to add some additional data but I am having trouble doing so. I am hoping someone can help provide some insight and point me in the right direction. I am not a SQL guy so I am trying to learn as I go.

I am using SQL 2014. I am using this post https://eskonr.com/2017/03/sccm-configmgr-report-for-local-admins-and-local-group-members/ for querying the local admins on the endpoints and storing the data in the SCCM DB. What I have is a view in the DB called v_GS_LocalGroupMembers0 which contains these columns

[ResourceID] [GroupID] [RevisionID] [AgentID] [TimeStamp] [Account0] [Category0] [Disabled0] [Domain0] [GroupSID0] [Name0][Type0]

The query used in the report looks like this

select sys1.netbios_name0
,lgm.name0 [Name of the local Group]
,lgm.account0 [Account Contained within the Group]
,lgm.domain0 [Domain for Account]
,lgm.type0 [Type of Account]
,scum.TopConsoleUser0 [Top User]
from v_gs_localgroupmembers0 lgm
join v_gs_workstation_status ws on ws.resourceid=lgm.resourceid
join v_r_system sys1 on sys1.resourceid=lgm.resourceid
join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP scum on scum.ResourceID=lgm.ResourceID
where lgm.name0='Administrators'
order by sys1.netbios_name0, lgm.name0, lgm.account0

I recently modified the query from what it was originally by including a query for the TopConsoleUser0 column in v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP to provide the primary user of the PC. Now my goal is to include the department for the user following what is contained in the department field for each user in AD. I have configured user discovery in SCCM to inventory all users' departments in AD which I found in the department column in the v_R_User view in the SCCM DB. What I can't figure out is how to do it or if this is even possible.

The closest I got where the query doesn't error but also doesn't return any data is 

SELECT sys1.Netbios_Name0 AS Hostname, 
lgm.Name0 AS [Name of the local Group], 
lgm.Account0 AS [Account Contained within the Group],
lgm.Domain0 AS [Domain for Account],
lgm.Type0 AS [Type of Account],
scum.TopConsoleUser0 AS [Top User], usr.department AS Department
FROM v_GS_LocalGroupMembers0 AS lgm INNER JOIN
v_GS_WORKSTATION_STATUS AS ws ON ws.ResourceID = lgm.ResourceID INNER JOIN
v_R_System AS sys1 ON sys1.ResourceID = lgm.ResourceID INNER JOIN
v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP AS scum ON scum.ResourceID = lgm.ResourceID INNER JOIN
v_R_User AS usr ON usr.Name0 = scum.TopConsoleUser0
WHERE (lgm.Name0 = 'Administrators')
ORDER BY Hostname, [Name of the local Group], [Account Contained within the Group]

I'm sure this isn't working because I don't have a common column with matching records between the v_GS_LocalGroupMembers0 and v_R_User views for SQL to match to. Again, not being a SQL guy, I had thought about creating a separate query to get the departments and use a UNION statement to bring it together, but I don't think that will work either. All suggestions are welcome and I'll be happy to provide more info if needed.

Thanks

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,807 questions
Microsoft Configuration Manager
0 comments
Accepted answer
  1. AllenLiu-MSFT 40,571 Reputation points Microsoft Vendor
    2023-02-15T07:44:44.99+00:00

    Hi, @Mike G

    Thanks very much for your feedback. We're glad that the problem is solved now. Here's a short summary for the problem, we believe this will help other users to search for useful information more quickly.

    Problem/Symptom:

    Using SCCM to inventory members of the local administrators group on all endpoints with the TopConsoleUser0 column in v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP to provide the primary user of the PC.

    Solution/Workaround:

    SELECT sys1.Netbios_Name0, lgm.Name0 AS [Name of the local Group], lgm.Account0 AS [Account Contained within the Group], lgm.Domain0 AS [Domain for Account], lgm.Type0 AS [Type of Account], scum.TopConsoleUser0 AS [Top User], usr.department AS Department

FROM  v_GS_LocalGroupMembers0 AS lgm INNER JOIN

v_GS_WORKSTATION_STATUS AS ws ON ws.ResourceID = lgm.ResourceID INNER JOIN

v_R_System AS sys1 ON sys1.ResourceID = lgm.ResourceID INNER JOIN

v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP AS scum ON scum.ResourceID = lgm.ResourceID INNER JOIN

v_R_User AS usr ON usr.Unique_User_Name0 = scum.TopConsoleUser0

WHERE (lgm.Name0 = 'Administrators')

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click ""Add Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. AllenLiu-MSFT 40,571 Reputation points Microsoft Vendor
    2023-02-07T07:56:17.4366667+00:00

    Hi, @Mike G

    Thank you for posting in Microsoft Q&A forum.

    Try this to see if it works:

    SELECT sys1.Netbios_Name0 AS Hostname, 
lgm.Name0 AS [Name of the local Group], 
lgm.Account0 AS [Account Contained within the Group],
lgm.Domain0 AS [Domain for Account],
lgm.Type0 AS [Type of Account],
scum.TopConsoleUser0 AS [Top User], usr.department AS Department
FROM v_GS_LocalGroupMembers0 AS lgm INNER JOIN
v_GS_WORKSTATION_STATUS AS ws ON ws.ResourceID = lgm.ResourceID INNER JOIN
v_R_System AS sys1 ON sys1.ResourceID = lgm.ResourceID INNER JOIN
v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP AS scum ON scum.ResourceID = lgm.ResourceID INNER JOIN
v_R_User AS usr ON usr.Unique_User_Name0 = lgm.Account0
WHERE (lgm.Name0 = 'Administrators')
ORDER BY Hostname, [Name of the local Group], [Account Contained within the Group]

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Add comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. Mike G 71 Reputation points
    2023-02-14T16:15:42.9933333+00:00

    Hi Allen,

    Thanks for responding. I tried your suggestion but SQL returned column headings w/o any rows. After looking at the views again I figured out the query. In case anyone is trying to do the same, here is my query.

    SELECT sys1.Netbios_Name0, lgm.Name0 AS [Name of the local Group], lgm.Account0 AS [Account Contained within the Group], lgm.Domain0 AS [Domain for Account], lgm.Type0 AS [Type of Account], scum.TopConsoleUser0 AS [Top User], usr.department AS Department
FROM  v_GS_LocalGroupMembers0 AS lgm INNER JOIN
v_GS_WORKSTATION_STATUS AS ws ON ws.ResourceID = lgm.ResourceID INNER JOIN
v_R_System AS sys1 ON sys1.ResourceID = lgm.ResourceID INNER JOIN
v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP AS scum ON scum.ResourceID = lgm.ResourceID INNER JOIN
v_R_User AS usr ON usr.Unique_User_Name0 = scum.TopConsoleUser0
WHERE (lgm.Name0 = 'Administrators')
    0 comments