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

CSV Column Extraction with PowerShell

UPDATE: Don’t read this post!! –>  Read this one instead

I originally titled this “Kicking CSV in the Balls with PowerShell boots” but that was too violent, and too dramatic.  Then I thought about “Giving CSV a Happy Rub-Down with PowerShell Lotion” but that felt creepy.  So I went for the boring technically-apropos title.

1wearandtear

This was one of those “little things that drives someone (me) to stick their head in a sink filled with ice water and sing ‘God bless America’ until oxygen runs out”.  Go ahead, make fun of me, that’s okay. I do.  My dog does.  The confused people in the adjacent hotel room probably would. Anyhow…

I ran into a challenge that involved reading CSV files to extract specific column data by column name.  I ran across a bunch of articles, some with good advice and some with bad advice (note: those who say use Get-Content to parse column names by leveraging .Split(“,”) are ignoring CSV that contains quoted/nested commas).  So, this article kicked one of my functional brain cells right in the nuts, and got me on the right path (thank you!)

Example CSV data:

ID,Department,"Last,First Name",City
001,Marketing,"Jorgan,Hugh",Miami
002,Sales,"Phisting,Anita","New York"
003,Engineering,"McCracken,Phill","San Francisco"

You get the idea.  So, I wanted to be able to pull all of the values that are associated with the “Last,First Name” column.  The problem is this…

  • Import-CSV returns a custom object type, not a Hash table or Array
  • Without a native hash table, it’s difficult to “easily” extract row elements by name (i.e. Javascript getElementById, or PowerShell $hash.Item(), or the implicit alias thereof)
  • Parsing CSV by fetching the first row (column names) using Get-Content and parsing via Split() method is unreliable when column names contain nested commas.

The Code:

param (
  [parameter(Mandatory=$True)] [string] $CsvFile,
  [parameter(Mandatory=$True)] [string] $Column
)

# returns an array of logical column names using first "row"

function Get-CsvColumns {
  param ($csvData)
  Get-Member -InputObject $csvdata[0] |
    Where-Object {$_.MemberType -eq "NoteProperty"} |
      Select-Object -ExpandProperty Name
}

# returns the integer index for the element position
# among the logical array of column names

function Get-CsvColumnIndex {
  param ($column, $array)
  $cols = Get-CsvColumns $array
  $cindex = [array]::IndexOf($cols,"$column")
  return $cindex
}

if (Test-Path $CsvFile) {
  $fdata  = Import-Csv $CsvFile
  $colums = Get-CsvColumns -csvData $fdata
  $index  = Get-CsvColumnIndex -column "$column" -array $fdata
  foreach ($row in $fdata) {
    $v = $row.psobject.Properties.value[$index]
    # replace write-output with whatever you want, I dont care right now...
    Write-Output $v
  }
}
else {
  Write-Output "error: $CsvFile not found"
}

So, there you have it.  To run it…

.\Get-CsvData.ps1 -CsvFile ".\csvtest.csv" -Column "Last,First Name"

Jorgan, Hugh
Phisting, Anita
McCracken, Phill

And it will spit out the values for that logical “column” in the CSV file.

No more than 30 seconds after I stood up, and stretched my arms out to the sides and yelled “yes!!!  I have kicked this in the nuts!!” did it dawn on me that I could’ve simply invoked ADO and run it all via a T-SQL horse and buggy.  Damn it.  Victories fade quick.

Now, if you’ll excuse me, I need to pass out.

Enjoy!

Another SCCM SQL Query: SQL Server Hosts

Find Windows Server computers which have some version of SQL Server installed…

SELECT DISTINCT
  dbo.v_R_System.Name0 AS [Name], 
  dbo.v_R_System.ResourceID AS ResourceID,
  dbo.v_R_System.SMS_Unique_Identifier0 AS [GUID],
  dbo.v_R_System.Resource_Domain_OR_Workgr0 AS Domain,
  dbo.v_R_System.AD_Site_Name0 AS ADSiteName,
  dbo.v_R_System.Client0 AS Client 
FROM dbo.v_R_System 
  INNER JOIN dbo.v_GS_ADD_REMOVE_PROGRAMS 
  ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID = dbo.v_R_System.ResourceId 
  INNER JOIN dbo.v_GS_System
  ON dbo.v_GS_SYSTEM.ResourceId = dbo.v_R_System.ResourceId 
WHERE 
  (dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE 'Microsoft SQL Server%')
  AND
  (dbo.v_GS_SYSTEM.SystemRole0 = 'Server')

SCCM / SQL – Machines Not Scanned in 15 Days

Here’s a SQL query to return machines which have not run a Software Updates scan within the past 15 days.

SELECT DISTINCT 
  dbo.v_R_System.ResourceID, 
  dbo.v_R_System.Name0 AS ComputerName, 
  dbo.v_ScannedUpdates.ScanTime
FROM 
  dbo.v_ScannedUpdates INNER JOIN
  dbo.v_R_System ON 
  dbo.v_ScannedUpdates.ResourceID = dbo.v_R_System.ResourceID
WHERE 
  (dbo.v_ScannedUpdates.ScanTime IS NULL)
  OR
  (DATEDIFF(dd, dbo.v_ScannedUpdates.ScanTime, GETDATE()) > 15)

 

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.