SCCM SQL Queries: Find Computers by User Logins and Login Counts

1wearandtear

Find computers where a particular user has logged on at least once.  Find them by user account name or by SID.  I know this violates a cardinal rule about avoiding LIKE with double-ended ‘%’ wildcards, but hey.  YOLO!

Find by User Profile + UserName

SELECT DISTINCT 
  dbo.v_R_System.Name0 AS ComputerName, 
  dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model, 
  dbo.v_R_System.AD_Site_Name0 AS ADSiteName, 
  dbo.v_GS_COMPUTER_SYSTEM.Domain0 AS Domain 
FROM 
  dbo.v_R_System INNER JOIN
  dbo.v_GS_COMPUTER_SYSTEM 
  ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID 
  INNER JOIN
  dbo.v_GS_USER_PROFILE 
  ON dbo.v_R_System.ResourceID = dbo.v_GS_USER_PROFILE.ResourceID
WHERE 
  (dbo.v_GS_USER_PROFILE.LocalPath0 LIKE '%johndoe%')

Find Computers by User Profile SID

SELECT DISTINCT 
  dbo.v_R_System.Name0 AS ComputerName, 
  dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model, 
  dbo.v_R_System.AD_Site_Name0 AS ADSiteName, 
  dbo.v_GS_COMPUTER_SYSTEM.Domain0 AS Domain 
FROM 
  dbo.v_R_System INNER JOIN
  dbo.v_GS_COMPUTER_SYSTEM 
  ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID 
  INNER JOIN
  dbo.v_GS_USER_PROFILE 
  ON dbo.v_R_System.ResourceID = dbo.v_GS_USER_PROFILE.ResourceID
WHERE 
  (dbo.v_GS_USER_PROFILE.SID0 = 'S-1-5-21-123456789-1234567890-1234567890-1234')

Bonus – List UserNames by Most Computer Logins / Profiles

Find those promiscuous little users that seem to get their busy little fingers (or Remote Desktop connections) smeared on the most computers.  This one dumps a table of UserName, UserSID and count of Computers on which they have a local profile folder.

SELECT
  dbo.v_R_User.User_Name0 AS UserName, 
  dbo.v_R_User.SID0 AS UserSID, 
  COUNT(dbo.v_GS_USER_PROFILE.ResourceID) AS Computers
FROM 
  dbo.v_R_User INNER JOIN
  dbo.v_GS_USER_PROFILE ON dbo.v_R_User.SID0 = dbo.v_GS_USER_PROFILE.SID0
GROUP BY 
  User_Name0, dbo.v_R_User.SID0
ORDER BY 
  Computers DESC, UserName

SCCM SQL Query: Devices by User Login

Find all devices a particular user (in this example “contoso\sccmadmin”) has logged onto in the past 30 days.

SELECT 
  dbo.v_R_System.Name0 AS ComputerName, 
  dbo.v_GS_SYSTEM_CONSOLE_USER.ResourceID,
  dbo.v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 AS LastLogon,
  dbo.v_GS_SYSTEM_CONSOLE_USER.NumberOfConsoleLogons0 AS NumberLogons,
  dbo.v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 AS UserID,
  dbo.v_GS_SYSTEM_CONSOLE_USER.TotalUserConsoleMinutes0 AS LogonTotalTime
FROM dbo.v_GS_SYSTEM_CONSOLE_USER INNER JOIN
  dbo.v_R_System ON 
    dbo.v_GS_SYSTEM_CONSOLE_USER.ResourceID =
  dbo.v_R_System.ResourceID
WHERE 
  (dbo.v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 = 'contoso\sccmadmin')
  AND
  (DATEDIFF(dd, dbo.v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0, GETDATE()) < 30)

Output

The result set includes the Computer Name, the Computer ResourceID, the LastLogon timestamp,  the cumulative number of logins (per device), the UserID, and the cumulative login time (in minutes) per device.

Turn SCCM SQL Queries into Web Reports

The goal of this exercise is to add custom reports to your Configuration Manager site so you can dazzle executives while you sneak out the door.  If you’re smart, you’ll make some that include pie and bar charts with lots of spiffy colors and formatting.  That would give you enough time to get to Starbucks and back before they even look back in your direction.

Ok, serious stuff now.

I’m using (long inhale….) Microsoft System Center Configuration Manager (aka SCCM) “Current Branch” build 1606, with the latest hotfixes, running on Windows Server 2012 R2 and SQL Server 2014 SP1.  In addition, I’ve installed SQL Report Builder 3.0 for SQL Server 2008 R2 **.

If you read any of my posts on SQL queries for SCCM, you can follow the same basic approach for the query building part.  It’s much easier/better/smoother to do that in SQL Management Studio than in the Report Builder.

** If you download SQL Report Builder for SQL 2016, it will look prettier, but the reports won’t import or run in SCCM 1606. It is partly to do with the schema level buried inside the RDL report file.  I’m hoping this gets addressed soon, since SQL Server 2016 is the cat’s meow already.  Anyhow, the SQL Report Builder is installed with the default settings.

Step 1 – Build a SQL query in SSMS

  • (we already did this in previous posts here and here and a few others)
  • Test the query until you get the results you want
  • Copy the SQL statement to the clipboard and proceed

Step 2 – Create a SQL Report

  1. IMPORTANT: Make sure you log onto the server using an account with permissions to create and view reports
  2. Open SQL Report Builder.
  3. Select “New Report”
  4. Choose the desired wizard (I’m using Table or Matrix Wizard for this example)

cmrep1

  1. Select “Create a dataset” at the bottom of the New Table or Matrix form, and click Next.
  2. Select the “New” button.
  3. Enter a Name for the data source.  For this example, I used the name of the SCCM database, which is “CM_PS1”.  Leave the other settings as-is, and select the “Build…” button.
  4. When the Connection Properties form opens, TYPE IN the SCCM SQL Server host name (do not click the drop-down or you will grow old waiting for it to browse your network).  I prefer the FQDN, but the NetBIOS name will also work (usually).
  5. Select the SCCM database which is “CM_PS1” in my example. cmrep2
  6. Click Test Connection to verify it works, then click OK
  7. Back in the Data Source Properties form, click OK.  (Note that the 2 radio button options will be disabled while creating the Data Source, but afterwards, if you click Edit, they are enabled.  You just can’t do much with them yet.  So just leave it so the connection is embedded in the report for now)cmrep3
  8. Back in the “New Table or Matrix” wizard, you should now see one Data Source Connection (e.g. “CM_PS1”) with a sub-title “(in this Report)”.  Click Next.
  9. You should now be in the Design a Query form.  Click to expand the ‘dbo‘ schema in the left-hand Database View panel.
    cmrep4.PNG
  10. Expand Views and scroll to see how all of the SCCM database views are visible here (or should be).  Now click on the “Edit as Text” link at top-left.
    cmrep5.PNG
  11. Paste your SQL query statement into the top edit box and click the “!” (exclamation) link to run the query.  When it looks right, click Next.
  12. The next form is where you arrange the column structure for the table layout.  In this example, drag the ResourceID field into the “Row Groups” box.  Then select all of the fields except ResourceID and drag them into the Values box.  Click Next.
    cmrep6.PNG
  13. Since this example isn’t using grouping or other aggregate / scalar functionality, it just a raw table output, in the next form (Choose the Layout), I unchecked both options at left, and click Next.
    cmrep7.PNG
  14. In the “Choose a Style” form, you can change the CSS theme if you like, or leave the default as “Ocean”, and click Finish.
    cmrep8.PNG
  15. In the Report Designer form, click on the “Click to add title” text box and change the name to something relevant.  In this case, I entered “Physical Computers”.
    cmrep9.PNG
  16. You can do much more from here, but for now, I’m just saving the RDL file so I can import it into the SCCM reports library.  It’s recommended that you prepare or identify a common folder to save your custom report files.  It will make it easier to manage and protect them.  Once you’ve saved the report, you can close the SQL Report Builder if you like.

Step 3 – Import the Custom Report into SCCM

  1. Open the SCCM Report web site in Internet Explorer.
  2. Once you’re at the SQL Server Reporting Services home page, click “ConfigMgr_PS1” (where “PS1” is the site code, so yours may be different).
  3. If you’re like me, which I sure hope not, but in this case, maybe it’s okay, you hate the default folder organization, so I click “Details View” at the top right.
    cmrep10.PNG
  4. If you prefer placing your custom reports into a special folder.  If so, click “New Folder” in the top horizontal menu bar, and give it a cool name.  Then open that folder (or whatever folder you wish to add your custom report into).cmrep11
  5. Click the “Upload File” link in the top horizontal menu bar.  Click Browse to locate your RDL file, select the desired file and click OK. (Note: If you go through multiple import cycles while refining the report, you may need to click the Overwrite option to avoid errors).
    cmrep12.PNG
  6. Click OK.
  7. Your report is now ready to run!
    cmrep13a.PNG

If your report doesn’t work when you run it, you may need to update the connection assignment.  To do this, click the small down-arrow next to the report, and choose “Manage

cmrep13

cmrep14.PNG

From here, click “Data Sources” and update the connection settings as needed.

Note that this is VERY common when importing reports created by others (or downloaded from the web).  Since their connection settings aren’t portable, you will often need to change them using the above procedure, before you can run them.

Bonus – Queries the Hard Way

PS – If you really want to explore building SQL queries entirely within the SQL Report Builder, it is indeed possible.  Here’s how…

  1. When you get to the “Design a Query” form, select the fields from the tables or views you wish to merge.  In this example, I included “v_R_System“, “v_GS_COMPUTER_SYSTEM” and “v_GS_SYSTEM_ENCLOSURE” in order to pull the fields ResourceID, Name0, AD_Site_Name0, Model0, TotalPhysicalMemory0, and SerialNumber0.
  2. This is where it gets funky: In the center of the form, you will see “Auto Detect” highlighted.  Click on it to disable that link, and the itty-bitty, teeny-weeny itsy-bitsy icon to the right of “Edit Fields” becomes active.  If you move the mouse pointer over it, the tool tip will show “Add Relationship“, but if you click, you may not see anything happen.  It did something, but hid it from you.
    (NOTE: This form was developed by blind people in a cave at night.  There is no Min/Max options on the frame heading at top-right.  Just a typical red “X” to close it.  However, you can resize the form).
  3. Click the tiny-little double down arrows directly to the right of that icon.  That will expand a hidden panel for “Relationships” (you might call this an Easter Egg).
    cmrep15.PNG
  4. Click in the empty gray box under “Left Table“.  A weird looking popup listbox will appear.  For this SQL join, the left table will be v_R_System.
  5. The box under “Right Table” should now become highlighted.  Click in that box to select “v_GS_COMPUTER_SYSTEM
  6. Double-click in the “Join Fields” box and select the little icon at top-right that looks like microscopic, hot-water-shrunk database tables.  Click on it and it adds a row in the table grid below.  The “Left Join Field” and “Right Join Field” values will be empty.
    cmrep16.PNG
  7. Click in the “Left Join Field“.  It will display a list of the fields (columns) in the left table (e.g. v_R_System).  Select ResourceID.  Repeat this for the “Right Join Field” and select ResourceID as well (from v_GS_COMPUTER_SYSTEM).  Click OK
    cmrep17.PNG
  8. Change the Join Type from “Inner” to “Left Outer“.
  9. Because we’re joining three (3) tables/views, we need to add another join statement, so click the microscopic icon again and assign the Left Table to v_R_System again, but select v_GS_SYSTEM_ENCLOSURE for the Right Table.
  10. Repeat the same process to select ResourceID for the left and right join fields in the popup form.  Make sure the Join Type is also “Left Outer” as was the first join.
  11. Click “Run Query” to test and verify the results.
    cmrep18.PNG

And now you should see why it’s easier to build queries in SQL Server Management Studio.

Cheers!

SCCM / SQL – More Inventory Queries

Here’s a few more queries I’ve found helpful in the past week or two.  These were used on Configuration Manager 1602 and 1606 with SQL Server 2014 Sp1.  I will be taking a break for a while, but I would appreciate any feedback you have (and don’t forget to click the rating please?)

Computers with SSD’s Installed

SELECT DISTINCT
  ResourceID,
  SystemName0 AS ComputerName,
  Model0 AS Model,
  DeviceID0 AS DeviceID,
  Index0 AS [Index],
  InterfaceType0 AS Interface,
  Manufacturer0 AS Manufacturer,
  MediaType0 AS MediaType,
  Partitions0 AS Partitions,
  Size0 AS Capacity
FROM 
  dbo.v_GS_DISK
WHERE 
  Model0 LIKE '%SSD%'

Installation Counts for Operating Systems

SELECT DISTINCT 
  Caption0 AS OSName, 
  COUNT(*) AS Installs
FROM 
  dbo.v_GS_OPERATING_SYSTEM
GROUP BY 
  Caption0
ORDER BY
  Installs DESC

Machine Counts by BIOS Description

SELECT 
  DISTINCT Description0 AS BIOS,
  COUNT(*) AS Machines
FROM 
  dbo.v_GS_PC_BIOS
GROUP BY
  Description0
ORDER BY
  Machines DESC

Machine Counts by Time Zone

SELECT DISTINCT 
  CurrentTimeZone0 AS TimeZone,
  COUNT(*) AS Machines
FROM 
  dbo.v_GS_COMPUTER_SYSTEM
GROUP BY
  CurrentTimeZone0
ORDER BY
  CurrentTimeZone0

Count of Machines Online vs. Offline, by Site Server

SELECT DISTINCT
  SiteSystemName,
  ServerID,
  OnlineClients,
  OfflineClients,
  TotalClients
FROM 
  dbo.v_BgbServerCurrent
  LEFT OUTER JOIN dbo.v_BoundarySiteSystems ON
    dbo.v_BoundarySiteSystems.BoundaryID = ServerID
ORDER BY 
  SiteSystemName

Cheers!

Solving the Mystery of the Missing ConfigMgr Reports

marie-wilson-cooking

Symptoms

  1. Open 1602 ConfigMgr console, expand Monitoring / Reporting / Reports.
  2. Determined some reports are missing.  In this case, all were present up to “Software Updates – B Deployment Management“.  All folders and reports after that were not present
  3. Opened SQL Management Studio, connect to ReportServer database, run “select * from dbo.Catalog” to confirm returned rows.  Compare with a “normal” 1602 site server with the RSP role.

Steps to resolve

  1. Confirm that the service account used for the SCCM/SSRP connection is a member of the correct SQL roles for the correct databases.
  2. Confirm that the account has permissions to the correct folders. (in our case, the domain service account was added to local Administrators and the NTFS permissions were properly configured)
  3. Run, MOFCOMP.exe C:\Program Files (x86)\Microsoft SQL Server\120\Shared\SqlMgmProviderXpSp2Up.mof  (note: the number in red varies by the version of SQL Server installed.  For 2014, it is 120).
  4. Remove the Reporting Services Point role within the ConfigMgr console
  5. Reboot the server
  6. Add the Reporting Services Point role back, and monitor srsrp.log to confirm the reports being recreated.
  7. Verify reports within the console
  8. Verify reports within the web reports site.
  9. Eat and drink
  10. Dance naked.