SCCM Collection Queries by Server Role

MFfn7

Rather than spew forth a bunch of sample queries, I’ll just hand you a virtual fishing rod, a case of imaginary beer, and point you to the make-believe boat.  This little procedure came in handy today with a customer I was helping.  I hope it helps you as well…

  • Device Collections
    • Create Device Collection
      • Name: Servers – WDS Servers (example)
        • Limiting collection: (whatever has servers with clients)
        • Use incremental updates for this collection (check)
        • Add Rule > Query-Rule
          • Name: 1 (or whatever you want, I’m lazy)
          • Edit Query Statement:
            • Omit duplicate rows (check)
            • Criteria tab
              • “Select” button (click it)
              • Class = Server Feature
              • Attribute = Name (click OK)
                • Is Equal To (leave as-is)
              • Click the “Value” button
                • Select an appropriate Feature Name
                • Enjoy a cold one!

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)