Documenting Your IT Environment (The Easy Way)

One of the most common, and tragically problematic, aspects of today’s IT world is the lack of sufficient documentation. In fact, ANY documentation. It’s not associated with any one area either. It crosses everything from help desk tickets, to change requests, to Active Directory objects, to Group Policy, to SQL Server, to System Center to Azure.

UPDATED 8/8/17 – added SQL and SCCM examples at very bottom.

When and Why Does It Matter?

It matters when things go wrong (or “sideways” as many of my colleagues would say).  There’s the “oh shit!” moments where you or your staff find the problem first.  Then there’s the “oh holy shit!!” moments when your boss, his/her boss, and their bosses find the problem first and come down hard on you and your team.  When shit goes wrong/sideways, the less time you waste on finding out the what/where/why/who/when questions for the pieces involved with the problem, the better.  To that end, you can document things in other places, like Word documents, spreadsheets, help desk and ITIL systems, post-it notes, whiteboards, etc.  Or you can attach comments DIRECTLY to the things which are changed.  This isn’t possible in all situations, but it is most definitely possibly for many of the things that will straight-up shove your day into shit storm, and I have no idea what that really means, but it just sounds cool.  Anyhow…

The best part of this approach is that you can leverage PowerShell and other (free and retail) utilities to access and search comments you nest throughout the environment.  For example, you can collect and query Group Policy Object comments…

Need to Get the Comment buried in a particular GPO or GPPref setting?  Here’s one example for querying a GPO under User Configuration / Preferences / Control Panel Settings / Folder Options…

#requires -modules GroupPolicy
<#
.SYNOPSIS
  Get-GPPrefFolderOptionsComments.ps1
#>
param (
  [parameter(Mandatory=$True)]
  [ValidateNotNullOrEmpty()]
  [string] $GPOName
)
try {
  $policy = Get-GPO -Name $GPOName -ErrorAction Stop
}
catch {
  Write-Error "Group Policy $GPOName not found"
  break
}
$policyID = $policy.ID
$policyDomain = $policy.DomainName
$policyName  = $policy.DisplayName
$policyVpath = "\\$($policyDomain)\SYSVOL\$($policyDomain)\Policies\{$($policyID)}\User\Preferences\FolderOptions\FolderOptions.xml"
Write-Verbose "loading: $policyVpath"

if (Test-Path $policyVpath) {
  [xml]$SettingXML = Get-Content $policyVpath
  $result = $SettingXML.FolderOptions.GlobalFolderOptionsVista.desc
}
else {
  Write-Error "unable to load xml data"
}
Write-Output $result

Example…

So, What Now?

So, in the interest of avoiding more bad Mondays, start documenting your environment now.  Need some examples of where to start?  Here you go…

 

Thank you for your support.

Advertisements

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')