Monday, September 30, 2013

Systems Created in the last 90 days

Just an exercise with dates because I haven't done much along these lines:

SELECT     DATEPART(week, GETDATE()) - DATEPART(week, Creation_Date0) AS WeeksAgo, COUNT(DATEPART(week, GETDATE()) - DATEPART(week, Creation_Date0))
                      AS Count
FROM         v_R_System
WHERE     (DATEDIFF(dd, Creation_Date0, GETDATE()) < 90)
GROUP BY DATEPART(week, GETDATE()) - DATEPART(week, Creation_Date0)
ORDER BY WeeksAgo


Data looks like the following:

WeeksAgo
0
Count
9
1143
2110
3172
4100
5114
6108
7104
876
971
1078
1174
1292
1321

Then I stuck it in a SRSS chart:

Microsoft Security Blue Hat Challenge

I know I have a few contacts on G+ that would be interested in this (for the XBox Avatars if for nothing else):

"The BlueHat Challenge is a series of computer security problems of increasing difficulty to help you build and test your skills in three areas: reverse engineering, vulnerability discovery, and web browser manipulation attack concepts."

http://blogs.technet.com/b/srd/archive/2013/07/31/the-bluehat-challenge.aspx

Thursday, September 26, 2013

Server Software Updates Compliance (Updated)

This an update to the previous post and corrects cases where systems aren't in any of the expected collections.  My boss actually caught that and made the change...  Embarrassing...
 
SELECT
  Name0 AS Server, 
  SUM(Targeted) AS Targeted,
  SUM(Missing) AS Missing,
  SUM(Installed) AS Installed,
  TopUser,
  Collection
FROM
  (SELECT DISTINCT
    sys.Name0,
    ui.Title,
    (CASE WHEN ctm.ResourceID IS NOT NULL THEN 1 ELSE 0 END) AS Targeted,
    (CASE WHEN css.Status = 2 THEN 1 ELSE 0 END) AS Missing,
    (CASE WHEN css.Status = 3 THEN 1 ELSE 0 END) AS Installed,
     sys.Operating_System_Name_and0, v_Collection.Name AS Collection,
     v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 AS TopUser
     FROM v_UpdateComplianceStatus AS css
           JOIN v_UpdateInfo AS ui ON ui.CI_ID = css.CI_ID
           JOIN v_R_System AS sys ON css.ResourceID = sys.ResourceID and sys.Operating_System_Name_and0 LIKE '%server%'
           LEFT JOIN vWorkstationStatus AS ws ON ws.ResourceID = sys.ResourceID                        
           LEFT JOIN v_FullCollectionMembership ON css.ResourceID = v_FullCollectionMembership.ResourceID
           LEFT JOIN v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID
           LEFT JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP ON sys.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID
           LEFT JOIN v_CITargetedMachines AS ctm ON css.ResourceID = ctm.ResourceID AND ctm.CI_ID = css.CI_ID
      WHERE (v_Collection.Name LIKE CHANGEME or v_Collection.Name =CHANGEME or v_Collection.Name is null)
      GROUP BY sys.Name0, ctm.ResourceID, css.Status, ui.Title, sys.Operating_System_Name_and0, v_Collection.Name,
              v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0) AS derived
GROUP BY Name0, TopUser, Collection
ORDER BY Missing DESC

Monday, September 23, 2013

Server software updates compliance report

The server team asked me to come up with a report showing the number of software updates missing and applied.  This will list all servers with a count of targeted, missing, and installed software updates.  In addition it'll list the collections that they are in, I added that to troubleshoot why some systems weren't getting updates.

This was my first trip into the compliance tables in SCCM 2012. They are mostly the same as 2007, though there are a couple new views that are worth looking at.  I listed the other sites I used as a reference, but the queries directly on those sites weren't really working for me.  So I ended up using a SUM( ) instead of COUNT( ) and tweaking things a bit to get it to run somewhat quicker. 

I'm sure there is some more optimization that could be done, but this works for the time being:

SELECT     Name0 AS Server, SUM(Targeted) AS Targeted, SUM(Missing) AS Missing, SUM(Installed) AS Installed, Collection
FROM         (SELECT DISTINCT
                                              sys.Name0, ui.Title, (CASE WHEN ctm.ResourceID IS NOT NULL THEN 1 ELSE 0 END) AS Targeted, (CASE WHEN css.Status = 2 THEN 1 ELSE 0 END)
                                              AS Missing, (CASE WHEN css.Status = 3 THEN 1 ELSE 0 END) AS Installed, sys.Operating_System_Name_and0, v_Collection.Name AS Collection
                       FROM          v_UpdateComplianceStatus AS css INNER JOIN
                                              v_UpdateInfo AS ui ON ui.CI_ID = css.CI_ID INNER JOIN
                                              v_R_System AS sys ON css.ResourceID = sys.ResourceID INNER JOIN
                                              v_FullCollectionMembership ON css.ResourceID = v_FullCollectionMembership.ResourceID INNER JOIN
                                              v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID LEFT OUTER JOIN
                                              v_CITargetedMachines AS ctm ON css.ResourceID = ctm.ResourceID AND ctm.CI_ID = css.CI_ID
                       WHERE      (ui.CIType_ID IN (1, 8)) AND (sys.Operating_System_Name_and0 LIKE '%server%') AND (v_Collection.Name LIKE 'All %')
                       GROUP BY sys.Name0, ctm.ResourceID, css.Status, ui.Title, sys.Operating_System_Name_and0, v_Collection.Name) AS derived
GROUP BY Name0, Collection
ORDER BY Missing DESC


v_UpdateInfo.CIType 1 and CIType 8 indicate a software update and software update bundle respectively.  Don't for get the bundles, or you'll be missing a bunch of stuff.

The v_Collection.Name LIKE 'All %' will have to be changed to your environment.  My original query used a prefix that we you to indicate maintenance windows not 'All', but it would have probably not shown any results if you copied and pasted it...

The rest should be pretty self-explanatory. 

A couple sites that helped me along the way:
http://technet.microsoft.com/en-us/library/dd334594.aspx
http://www.myitforum.com/forums/SCCM-Compliance-Report-Help-m230864.aspx
http://anotherblabla.wordpress.com/2012/03/06/sccm-usefull-software-update-reports/
http://anotherblabla.wordpress.com/2012/12/09/sccm-2012-compliance-state-for-a-specific-computer-custom-report/

Wednesday, September 11, 2013

Desktop Backup (PowerShell plus Scheduled Task)

So, I test A LOT of stuff daily on my own workstation.  I suspect that most SCCM folks do.  I also have A LOT of data in my profile that it would take YEARS to rebuild.  Not something I want to accidentally blow up.  We do have pretty copious SAN storage, so I figured I'd continue with my learning PowerShell program and do a quick backup script.  Well, OK I copied most of it from another backup script we had but I still learned something.  The requirements were that it had to run fully with user rights only, and I don't want to copy 12GB or so daily over the network so it has to only copy new files.  And it has to be fast.  So here we go (beware the word wrap):

# Script to back up my stuff
# THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
# KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
# IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR
# PURPOSE.   
 
# Define starting variables
$StartingDir = "C:\Users\<MyUserName>"
$DestDir = "\\Path\To\Our\Users\Storage\Space\<MyUserName"
$maxprocess = 7
 
# Create the Collection Queue
$queue = [System.Collections.Queue]::Synchronized((New-Object System.Collections.Queue))
 
# Define the Directory List or Range
$DirList = (Get-ChildItem $StartingDir | ? {$_.PSIsContainer -eq $True})
 
# Queue the Directory List or Range
foreach ($Dir in $DirList){
    $Name = $Dir.name
    Switch ($Name) {
        "Applications" {$queue.Enqueue($Dir)}
        "Desktop"      {$queue.Enqueue($Dir)}  
        "Downloads"    {$queue.Enqueue($Dir)}
        "Favorites"    {$queue.Enqueue($Dir)}
        "Documents"    {$queue.Enqueue($Dir)}
        "Pictures"     {$queue.Enqueue($Dir)}
    }
}
 
# Function that pulls next object from the queue and starts a process with it
function CreateProcessFromQueue
{
    if ($queue.count -gt 0) {
   
        #write $queue.count
        $Dir = $queue.Dequeue()
        $SourceDir = $Dir.fullname
        $DirName = $Dir.BaseName
        $DestinationDir = $DestDir + "\" + $DirName
        $LogDir = """C:\Temp\"
        $RoboCopyArgs = "/E /PURGE /COPY:DATSO /XO /R:1 /W:1 /NP /FFT /LOG:" + $LogDir + "BAK" + $DirName + ".log"""
        #write $DirName       
       
        # Define process starting information       
        $psi = New-Object System.Diagnostics.ProcessStartInfo
        $psi.FileName = "robocopy.exe"
        $psi.Arguments = """$SourceDir""" + " " + """$DestinationDir""" + " $RoboCopyArgs" 
        $psi.CreateNoWindow = $true
        $psi.UseShellExecute = $false
        #write $psi.Arguments
       
        # Start new process
        $Robo = [System.Diagnostics.Process]::Start($psi)
    }
}
 
# Start max number of processes
for ($i = 0; $i -lt $maxprocess; $i++) {
    CreateProcessFromQueue
}
 
# Ensure max number of processes continue to run until queue is empty
while ($queue.count -gt 0) {
     start-sleep -s 5
    $robocount = get-process robocopy
         if ($robocount.count -lt $maxprocess) {
             CreateProcessFromQueue
         }
  }
 
Yeay!  It works, I'm a happy geek!  So after that I created a quick scheduled task.  Ran it as my user, only when I'm logged on, scheduled daily (when I'm at lunch since I have to be logged on).  The Actions tab has a gotcha.  Set it to 'Start a Program'.  The program name is PowerShell.exe, but the arguments are "-command c:\<path>\BackMeUp.ps1" (without quotes).  Conditions and settings are pretty much whatever you want.

Tuesday, September 10, 2013

Uninstalling non-admin install of Google Chrome (Part II)

So my earlier attempt at uninstalling per-user / non-admin install of Google Chrome worked, but Google Updater was re-installing it daily.  So I re-wrote it in PowerShell mostly because I'm finally taking the plunge to learn it.  If you're using SCCM 2012 as I am, deploy the .bat file and use it as the command line.  It'll make sure execution policy doesn't get in your way.

--NOTE: I have NOT tested the MSI unistaller portion yet.  This is the way I imagine it'll work (get the uninstall string from the registry & run it, sounds simple).  But I haven't pulled doen the MSI and tested it yet.  When I do I'll update this section saying it's tested.

******************
* RemoveChrome.bat
******************
powershell set-executionpolicy unrestricted
Powershell .\ChromeKiller.ps1
powershell set-executionpolicy restricted

********End*******

******************
* ChromeKiller.ps1
* Beware word wrap
******************
# THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
# KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
# IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR
# PURPOSE.

$ChromeType = "NONE"
$Log = New-Item -ItemType File -Path "C:\Temp\Chrome.log" -Force
$Error
$QueryString = Gwmi Win32_OperatingSystem
$QueryString = $QueryString.Caption

#Determine OS
Switch -Wildcard ($QueryString){
    "*Windows 7*"
        {$OS = "Win7"
         $UserDir = "C:\Users"
        }
    "*XP*" {
         $OS = "WinXP"
         $UserDir = "C:Documents and Settings"
        }
    }
 
Add-Content $Log "I'm on a $OS machine"

#Determine Install Type and kill it when found
 If(Test-Path -Path 'Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\Google Chrome') {
  $Uninstall = Get-ItemProperty -Path 'Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\Google Chrome' -Name "UninstallString"
  & $Uninstall
  $ChromeType = "Full"
  Add-Content $Log "User based with Admin Rights on a x64 machine"
  Add-Content $Log "Ran $uninstall"
 }
 Else {
 Add-Content $Log "Not x64 Non-MSI w/ Admin Rights Installed"
 }

 If(Test-Path -Path 'Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\{C534608B-4744-3856-AE7B-672DD3CDFEC7}') {
  $Uninstall = Get-ItemProperty -Path 'Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\{C534608B-4744-3856-AE7B-672DD3CDFEC7}' -Name "UninstallString"
  & $Uninstall
  $ChromeType = "Full"
  Add-Content $Log "MSI based with Admin Rights on a x64 machine"
  Add-Content $Log "Ran $uninstall"
 }
 Else {
 Add-Content $Log "Not x64 MSI Installed"
 }

  If(Test-Path -Path 'Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\Google Chrome') {
  $Uninstall = Get-ItemProperty -Path 'Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\Google Chrome' -Name "UninstallString"
  & $Uninstall
  $ChromeType = "Full"
  Add-Content $Log "User based with Admin Rights on a x32 machine"
  Add-Content $Log "Ran $uninstall"
 }
  Else {
 Add-Content $Log "Not x32 Non-MSI w/ Admin Rights Installed"
 }

 If(Test-Path -Path 'Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\{C534608B-4744-3856-AE7B-672DD3CDFEC7}') {
  $Uninstall = Get-ItemProperty -Path 'Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\{C534608B-4744-3856-AE7B-672DD3CDFEC7}' -Name "UninstallString"
  & $Uninstall
  $ChromeType = "Full"
  Add-Content $Log "MSI based with Admin Rights on a x32 machine"
  Add-Content $Log "Ran $uninstall"
 }
 Else {
 Add-Content $Log "Not x32 MSI Installed"
 }

#If it not a full install, check to see if it's lite and if so brute kill it
If ($ChromeType -eq "NONE") {
 Add-Content $Log "Wasn't installed with admin rights, brute forcing"
 
 $Folders = Get-ChildItem -Path $UserDir
 Add-Content $Log "Working on $Folders"
 foreach ($folder in $Folders) {
   Add-Content $Log "Working on $folder"
   If (Test-Path -path "$UserDir\$folder\Appdata\local\google\chrome") {

     #Remove Files
     Remove-Item -Path "$UserDir\$folder\Appdata\local\google\chrome" -Recurse -Force -ErrorAction SilentlyContinue
     Add-Content $Log "Found Chrome dir, removed $UserDir\$folder\Appdata\local\google\chrome"
    
     #Remove HKLM Files
     Add-Content $Log "Removing HKLM Files"
     Remove-Item -Path 'HKLM:\SOFTWARE\Classes\ChromeHTML' -ErrorAction SilentlyContinue -Recurse -Force
     $Keys = Get-ChildItem HKLM:\SOFTWARE\Clients\StartMenuInternet
     foreach ($Key in $Keys) {
       If($key.Name.Contains("Google")) {
         remove-item $key.PSPath -Recurse -Force -ErrorAction SilentlyContinue
       }
     }
     Remove-ItemProperty -Path HKLM:\SOFTWARE\Clients\StartMenuInternet -Name "Chrome.exe" -ErrorAction SilentlyContinue
     Remove-ItemProperty -Path HKLM:\SOFTWARE\RegisteredApplications -Name "Chrome" -ErrorAction SilentlyContinue
     Remove-Item "HKLM:\SOFTWARE\google\update\ClientStateMedium\{8A69D345-D564-463c-AFF1-A69D9E530F96}" -ErrorAction SilentlyContinue
     Remove-Item "HKLM:\SOFTWARE\Google\Update\Clients\{8A69D345-D564-463c-AFF1-A69D9E530F96}" -ErrorAction SilentlyContinue
     Remove-Item "HKLM:\SOFTWARE\Google\Update\ClientState\{8A69D345-D564-463c-AFF1-A69D9E530F96}" -ErrorAction SilentlyContinue
     Remove-Item "HKLM:\SOFTWARE\Wow6432Node\Google\Update\Clients\{8A69D345-D564-463c-AFF1-A69D9E530F96}" -ErrorAction SilentlyContinue

     #Remove HKU Files
     Add-Content $Log "Removing HKU Files"
     $Keys = Get-ChildItem registry::Hkey_Users
     foreach ($Key in $Keys) {
      switch -Wildcard ($Key.Name) {
        "HKEY_Users\S-1-5-18" {Add-Content $Log "Skipping Local Admin Hive"}
        "HKEY_Users\S-1-5-19" {Add-Content $Log "Skipping Guest Hive"}
        "HKEY_Users\S-1-5-20" {Add-Content $Log "Skipping Network Service Hive"}
        "HKEY_Users\.Default" {Add-Content $Log "Skipping Local Service Hive"}
        default {
         $TargetRoot = $key.PSPath
         Add-Content $Log "Working on $TargetRoot"
         $SubKeys = Get-ChildItem "$TargetRoot\SOFTWARE\Clients\StartMenuInternet" -ErrorAction SilentlyContinue
         foreach ($SubKey in $SubKeys)
          {If($Subkey.Name.Contains("Google"))
           {remove-item $Subkey.PSPath -Recurse -Force -ErrorAction SilentlyContinue}
          }
         Remove-ItemProperty -Path "$TargetRoot\SOFTWARE\Clients\StartMenuInternet" -Name "chrome.exe"-ErrorAction SilentlyContinue
         Remove-ItemProperty -Path "$TargetRoot\SOFTWARE\RegisteredApplications" -Name "chrome"-ErrorAction SilentlyContinue
         Remove-Item -Path "$TargetRoot\SOFTWARE\Classes\ChromeHTML" -ErrorAction SilentlyContinue -Recurse -Force
         Remove-Item -Path "$TargetRoot\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\Chrome" -ErrorAction SilentlyContinue -Recurse -Force
         Remove-Item -Path "$TargetRoot\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\Google Chrome" -ErrorAction SilentlyContinue -Recurse -Force
         Remove-Item -Path "$TargetRoot\Software\Google\Update\Clients\{8A69D345-D564-463c-AFF1-A69D9E530F96}" -ErrorAction SilentlyContinue -Recurse -Force
         Remove-Item -Path "$TargetRoot\Software\Google\Update\ClientState\{8A69D345-D564-463c-AFF1-A69D9E530F96}" -ErrorAction SilentlyContinue -Recurse -Force
         Remove-Item -Path "$TargetRoot\Software\Google\Update\Clients\{00058422-BABE-4310-9B8B-B8DEB5D0B68A}" -ErrorAction SilentlyContinue -Recurse -Force
         Remove-Item -Path "$TargetRoot\Software\Google\Update\ClientState\{00058422-BABE-4310-9B8B-B8DEB5D0B68A}" -ErrorAction SilentlyContinue -Recurse -Force
        }
      }
     }
    }
    Else {
      Add-Content $Log "Chrome not installed for $folder"
    }
 }
}
 Add-Content $Log "Complete"

********End*******