Reporting on Workstations in AD Group
So I last posted about creating a collection based on a user's membership in an AD Group, and tying that to a workstation based on the TopConsoleUser field.
Of course as soon as I let it loose that we now had that functionality, our group wanted to be able to run reports against it. "No problem!", I thought. Just translate the WQL to SQL and everything will be fabulous. Long story short, after several hours spread over a couple of days I threw in the towel. The WQL just does not line up in any way with SQL; the process where we actually match userid against AD Group is somehow hidden behind the curtain.
A quick call and our SQL guru comes to the rescue. After trying to parse it and also being unable to figure it out, we ran a SQL Profiler capture when executing the following WQL query:
select distinct UniqueUserName from SMS_R_User where UserGroupName = "<domain>\\<GroupA>"
After some searching, we found the following query in the Profiler trace:
select distinct Name0 from v_R_System INNER JOIN v_GS_SYSTEM_CONSOLE_USAGE on v_GS_SYSTEM_CONSOLE_USAGE.ResourceID = v_R_System.ResourceID where v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0 in (select distinct SMS_R_User.Unique_User_Name0 from User_DISC AS SMS_R_User LEFT OUTER JOIN User_User_Group_Name_ARR AS __erUser_User_Group_Name_ARR0 ON SMS_R_User.ItemKey = __erUser_User_Group_Name_ARR0.ItemKey where __erUser_User_Group_Name_ARR0.User_Group_Name0 = '<domain>\<GroupA>')
A bit more complex, yes? Turns out WQL is hiding some rather hinky SQL stuff from us, including tables we're not used to dealing with. The table dbo.User_User_Group_Name_ARR0 table appears to be where we actually map user names to AD Groups, by means of the ItemKey field.
In order to get this to return the workstation ID using the TopConsoleUser field, I turned it into this query:
select distinct Name0 from v_R_System INNER JOIN v_GS_SYSTEM_CONSOLE_USAGE on v_GS_SYSTEM_CONSOLE_USAGE.ResourceID = v_R_System.ResourceID where v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0 in (select distinct SMS_R_User.Unique_User_Name0 from User_DISC AS SMS_R_User LEFT OUTER JOIN User_User_Group_Name_ARR AS __erUser_User_Group_Name_ARR0 ON SMS_R_User.ItemKey = __erUser_User_Group_Name_ARR0.ItemKey where __erUser_User_Group_Name_ARR0.User_Group_Name0 = '<domain>\<GroupA>')
So now by replacing <domain>\<GroupA>' with pertinent info, we get the report that people are clamoring for. I may even make it a variable, with choices that auto-populate from the table. Yeah, that's it!
Now granted you have to take care of a couple of permissions issues on the 'new' tables (specifically dbo.User_User_Group_Name_ARR0 and dbo.User_DISC) to get them to appear in the web console view. Granting "Select" on these was not a concern for me, so I went ahead. Your shop my be a bit more touchy about that requirement.
Again this was SMS 2003, SP3, Asset Intelligence deployed. YMMV.