Monday, June 4, 2012

Group Policy Status Excel Report PowerShell Script

I recently went through the process of streamlining and cleaning up a Group Policy implementation. There were hundreds of policies involved, so the only way to tackle this was through automation. To tackle this, I wrote a series of PowerShell scripts to help with the process. This post is about the script I use to gather the information that I needed.

With so much information to review, I really needed the information in a place where I could see everything at once, but could also sort and filter as needed. Since Excel is easily scriptable via PowerShell, that made the perfect choice.

The script I wrote grabs all of the following information and then dumps it into a spreadsheet:
  • GPO Name
  • GUID
  • When Created
  • Last Modified
  • WMI Filters
  • Computer Configuration Enabled
  • Computer Directory Version
  • Computer Sysvol Version
  • Comp Extensions Used
  • User Configuration Enabled
  • User Directory Version
  • User Sysvol Version
  • User Extensions Used
  • Links
  • Enabled Links
  • No Override
  • Owner
  • Security
  • Total Size
  • Policy Size
  • ADM File Size
  • ADM Files
From that point, I could sort, filter and compare to get whatever information that I needed (e.g. to generate a long list of stuff that needed to be fixed).

To summarize the script, it grabs a list of all policies in the domain, loops through them getting their XML data, dumps that into an array, and then writes it all to Excel.

Because the script requires the Group Policy module, it will only work on Windows Server 2008 or Windows 7 systems that have the Remote Server Administration Tools installed.

The script also takes an argument that tells it the path to the policies folder on the sysvol, which it uses to get adm files and policy sizes. So you would call the script like: .\get-gpoinfo.ps1 \\mydomain.com\sysvol\mydomain.com\policies


import-module grouppolicy



$Policies = $Args[0] 

#-----------------------------------------------
# Functions
#-----------------------------------------------

#Get-GPOInfo gets an XML report of the GPO and uses it to return specific data in an array
function Get-GPOInfo
{
param($GPOGUID)

#Gets the XML version of the GPO Report
$GPOReport = get-gporeport -guid $GPOGUID -reporttype XML
#Converts it to an XML variable for manipulation
$GPOXML = [xml]$GPOReport
#Create array to store info
$GPOInfo = @()

#Get's info from XML and adds to array
#General Information
$Name = $GPOXML.GPO.Name
$GPOInfo += , $Name
$GUID = $GPOXML.GPO.Identifier.Identifier.'#text'
$GPOInfo += , $GUID
[DateTime]$Created = $GPOXML.GPO.CreatedTime
$GPOInfo += , $Created.ToString("G")
[DateTime]$Modified = $GPOXML.GPO.ModifiedTime
$GPOInfo += , $Modified.ToString("G")
#WMI Filter
if ($GPOXML.GPO.FilterName) {
$WMIFilter = $GPOXML.GPO.FilterName
} else {
$WMIFilter = "<none>"
}
$GPOInfo += , $WMIFilter
#Computer Configuration
$ComputerEnabled = $GPOXML.GPO.Computer.Enabled
$GPOInfo += , $ComputerEnabled
$ComputerVerDir = $GPOXML.GPO.Computer.VersionDirectory
$GPOInfo += , $ComputerVerDir
$ComputerVerSys = $GPOXML.GPO.Computer.VersionSysvol
$GPOInfo += , $ComputerVerSys
if ($GPOXML.GPO.Computer.ExtensionData) { 
$ComputerExtensions = $GPOXML.GPO.Computer.ExtensionData | %{ $_.Name }
$ComputerExtensions = [string]::join("`n", $ComputerExtensions)
} else {
$ComputerExtensions = "<none>"
}
$GPOInfo += , $ComputerExtensions
#User Configuration
$UserEnabled = $GPOXML.GPO.User.Enabled
$GPOInfo += , $UserEnabled
$UserVerDir = $GPOXML.GPO.User.VersionDirectory
$GPOInfo += , $UserVerDir
$UserVerSys = $GPOXML.GPO.User.VersionSysvol
$GPOInfo += , $UserVerSys
if ($GPOXML.GPO.User.ExtensionData) {
$UserExtensions = $GPOXML.GPO.User.ExtensionData | %{ $_.Name }
$UserExtensions = [string]::join("`n", $UserExtensions)
} else {
$UserExtensions = "<none>"
}
$GPOInfo += , $UserExtensions
#Links
if ($GPOXML.GPO.LinksTo) {
$Links = $GPOXML.GPO.LinksTo | %{ $_.SOMPath }
$Links = [string]::join("`n", $Links)
$LinksEnabled = $GPOXML.GPO.LinksTo | %{ $_.Enabled }
$LinksEnabled = [string]::join("`n", $LinksEnabled)
$LinksNoOverride = $GPOXML.GPO.LinksTo | %{ $_.NoOverride }
$LinksNoOverride = [string]::join("`n", $LinksNoOverride)
} else {
$Links = "<none>"
$LinksEnabled = "<none>"
$LinksNoOverride = "<none>"
}
$GPOInfo += , $Links
$GPOInfo += , $LinksEnabled
$GPOInfo += , $LinksNoOverride
#Security Info
$Owner = $GPOXML.GPO.SecurityDescriptor.Owner.Name.'#text'
$GPOInfo += , $Owner
$SecurityInherits = $GPOXML.GPO.SecurityDescriptor.Permissions.InheritsFromParent
$SecurityInherits = [string]::join("`n", $SecurityInherits)
$GPOInfo += , $SecurityInherits
$SecurityGroups = $GPOXML.GPO.SecurityDescriptor.Permissions.TrusteePermissions | %{ $_.Trustee.Name.'#text' }
$SecurityGroups = [string]::join("`n", $SecurityGroups)
$GPOInfo += , $SecurityGroups
$SecurityType = $GPOXML.GPO.SecurityDescriptor.Permissions.TrusteePermissions | % { $_.Type.PermissionType }
$SecurityType = [string]::join("`n", $SecurityType)
$GPOInfo += , $SecurityType
$SecurityPerms = $GPOXML.GPO.SecurityDescriptor.Permissions.TrusteePermissions | % { $_.Standard.GPOGroupedAccessEnum }
$SecurityPerms = [string]::join("`n", $SecurityPerms)
$GPOInfo += , $SecurityPerms
#Policy File System Size
$GPOSize = Get-GPOSize $GUID $Policies
$GPOInfo += , $GPOSize.Total
$GPOInfo += , $GPOSize.Policy
$GPOInfo += , $GPOSize.ADM
$GPOInfo += , $GPOSize.ADMFiles
    return $GPOInfo
}

#Get-GPOSize returns the GPO file size. It requires a GUID and the sysvol policy path. It returns a Hash Table with three sizes (in Bytes) Total/ADM/Policy
function Get-GPOSize
{
param($GPOGUID,$PoliciesPath)
#Creates $objFSO if not already created
if (!$objFSO) { $objFSO = New-Object -com  Scripting.FileSystemObject }

$PolicyPath = $PoliciesPath + $GPOGUID
$ADMPath = $PolicyPath + "\Adm"

$TotalSize = $objFSO.GetFolder($PolicyPath).Size
    if (test-path $ADMPath) { 
$ADMSize = $objFSO.GetFolder($ADMPath).Size
$Files = $objFSO.GetFolder($ADMPath).Files | %{ $_.Name }
if ($Files) { $ADMFiles = [string]::join("`n", $Files) } else { $ADMFiles = "<none>" }
} else { 
$ADMSize = 0 
$ADMFiles = "<none>"
}
$PolicySize = $TotalSize - $ADMSize
$Size = @{"Total" = $TotalSize.ToString(); "ADM" = $ADMSize.ToString(); "Policy" = $PolicySize.ToString(); "ADMFiles" = $ADMFiles}
return $Size
}

#-----------------------------------------------
# Get's list of GPO's
#-----------------------------------------------

write-host Getting GPO Information...
$GPOs = get-gpo -all
write-host `tGPOs: $GPOs.Count

#-----------------------------------------------
# Creates an array and populates it with GPO information arrays
#-----------------------------------------------

$AllGPOs = @()

write-host Getting GPO XML Reports...

$GPOCount = 0
$GPOs | foreach-object {

$GPOCount++
write-host `t$GPOCount : $_.DisplayName / $_.ID
$ThisGPO = get-gpoinfo $_.ID
$AllGPOs += ,$ThisGPO

}

#-----------------------------------------------------
# Exports all information to Excel (nicely formatted)
#-----------------------------------------------------

write-host Exporting information to Excel...

#Excel Constants
$White = 2
$DarkGrey = 56
$Center = -4108
$Top = -4160

$e = New-Object -comobject Excel.Application
$e.Visible = $True #Change to Hide Excel Window
$e.DisplayAlerts = $False
$wkb = $E.Workbooks.Add()
$wks = $wkb.Worksheets.Item(1)

#Builds Top Row
$wks.Cells.Item(1,1) = "GPO Name"
$wks.Cells.Item(1,2) = "GUID"
$wks.Cells.Item(1,3) = "Created"
$wks.Cells.Item(1,4) = "Last Modified"
$wks.Cells.Item(1,5) = "WMI Filter"
$wks.Cells.Item(1,6) = "Comp Config"
$wks.Cells.Item(1,7) = "Comp Dir Ver"
$wks.Cells.Item(1,8) = "Comp Sysvol Ver"
$wks.Cells.Item(1,9) = "Comp Extensions"
$wks.Cells.Item(1,10) = "User Config"
$wks.Cells.Item(1,11) = "User Dir Ver"
$wks.Cells.Item(1,12) = "User Sysvol Ver"
$wks.Cells.Item(1,13) = "User Extensions"
$wks.Cells.Item(1,14) = "Links"
$wks.Cells.Item(1,15) = "Enabled"
$wks.Cells.Item(1,16) = "No Override"
$wks.Cells.Item(1,17) = "Owner"
$wks.Cells.Item(1,18) = "Inherits"
$wks.Cells.Item(1,19) = "Groups"
$wks.Cells.Item(1,20) = "Perm Type"
$wks.Cells.Item(1,21) = "Permissions"
$wks.Cells.Item(1,22) = "Total Size"
$wks.Cells.Item(1,23) = "Policy Size"
$wks.Cells.Item(1,24) = "ADM Size"
$wks.Cells.Item(1,25) = "ADM Files"

#Formats Top Row
$wks.Range("A1:Y1").font.bold = "true"
$wks.Range("A1:Y1").font.ColorIndex = $White
$wks.Range("A1:Y1").interior.ColorIndex = $DarkGrey

#Fills in Data from Array
$row = 2
$AllGPOs | foreach {
$wks.Cells.Item($row,1) = $_[0]
$wks.Cells.Item($row,2) = $_[1]
$wks.Cells.Item($row,3) = $_[2]
$wks.Cells.Item($row,4) = $_[3]
$wks.Cells.Item($row,5) = $_[4]
$wks.Cells.Item($row,6) = $_[5]
$wks.Cells.Item($row,7) = $_[6]
$wks.Cells.Item($row,8) = $_[7]
$wks.Cells.Item($row,9) = $_[8]
$wks.Cells.Item($row,10) = $_[9]
$wks.Cells.Item($row,11) = $_[10]
$wks.Cells.Item($row,12) = $_[11]
$wks.Cells.Item($row,13) = $_[12]
$wks.Cells.Item($row,14) = $_[13]
$wks.Cells.Item($row,15) = $_[14]
$wks.Cells.Item($row,16) = $_[15]
$wks.Cells.Item($row,17) = $_[16]
$wks.Cells.Item($row,18) = $_[17]
$wks.Cells.Item($row,19) = $_[18]
$wks.Cells.Item($row,20) = $_[19]
$wks.Cells.Item($row,21) = $_[20]
$wks.Cells.Item($row,22) = $_[21]
$wks.Cells.Item($row,23) = $_[22]
$wks.Cells.Item($row,24) = $_[23]
$wks.Cells.Item($row,25) = $_[24]
$row++
}

#Adjust Formatting to make it easier to read
$wks.Range("I:I").Columns.ColumnWidth = 150
$wks.Range("M:M").Columns.ColumnWidth = 150
$wks.Range("N:N").Columns.ColumnWidth = 150
$wks.Range("S:S").Columns.ColumnWidth = 150
$wks.Range("Q:Q").Columns.ColumnWidth = 150
$wks.Range("U:U").Columns.ColumnWidth = 150
$wks.Range("Y:Y").Columns.ColumnWidth = 150
[void]$wks.Range("A:Y").Columns.AutoFit()
$wks.Range("A:U").Columns.VerticalAlignment = $Top
$wks.Range("F:H").Columns.HorizontalAlignment = $Center
$wks.Range("J:L").Columns.HorizontalAlignment = $Center
$wks.Range("R:R").Columns.HorizontalAlignment = $Center
$wks.Range("V:X").Columns.HorizontalAlignment = $Center

#Save the file
$Path = Get-Location
$SaveFile = $Path.path + "\GPO_Report.xlsx"
$wkb.SaveAs($SaveFile)

$e.Quit()

10 comments:

  1. Exception calling "GetFolder" with "1" argument(s): "Exception from HRESULT: 0x800A004C (CTL_E_PATHNOTFOUND)"
    At C:\Users\UserName\Documents\GPO Analysis\GPO Compare\get-gpoinfo.ps1:147 char:32
    + $TotalSize = $objFSO.GetFolder <<<< ($PolicyPath).Size
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

    You cannot call a method on a null-valued expression.
    At C:\Users\UserName\Documents\GPO Analysis\GPO Compare\get-gpoinfo.ps1:158 char:41
    + $Size = @{"Total" = $TotalSize.ToString <<<< (); "ADM" = $ADMSize.ToString(); "Policy" = $PolicySize.ToString(); "AD
    MFiles" = $ADMFiles}
    + CategoryInfo : InvalidOperation: (ToString:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    ReplyDelete
    Replies
    1. You may want to check and make sure you don't have any orphaned GPO's that are still showing in LDAP but have no correlating folder on the sysvol. You could protect against that throwing an error by testing the path before you snag the total size.

      Delete
    2. Anybody there? friend managed to fix this error?

      Delete
  2. Would it be possible to also dump out all of the Group Policies to HTML files and embed them in the excel sheet on the corresponding row?

    ReplyDelete
    Replies
    1. Get-GPO -All | %{
      Get-GPOReport -name $_.displayname -ReportType html -path ("C:\All-GPOs\"+$_.displayname+".html")
      }

      Delete
  3. To get this to work you need to be sure to add a "\" to the end of the call string provided at top. Without this it won't work.

    .\get-gpoinfo.ps1 \\mydomain.com\sysvol\mydomain.com\policies\

    ReplyDelete
  4. Hello,

    I'm using Windows 2012 x64 and getting below error while executing the script...

    Exporting information to Excel...
    New-Object : Retrieving the COM class factory for component with CLSID {00000000-0000-0000-0000-000000000000} failed
    due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).
    At Y:\GPO Scripts\get-gpoinfo.ps1:403 char:6
    + $e = New-Object -comobject Excel.Application
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ResourceUnavailable: (:) [New-Object], COMException
    + FullyQualifiedErrorId : NoCOMClassIdentified,Microsoft.PowerShell.Commands.NewObjectCommand

    Regards
    Jabri

    ReplyDelete
  5. Thank you for sharing the gr8 script Jeremy. Helps a lot. This post is pretty old but i am hoping you still be checking the blog now and again

    I just had a question regarding the capture of GPO settings using the above script. Is that something that can be implemented in the script so it captures the GPO settings as well

    Thanks again for sharing a great post

    ReplyDelete
  6. Is it possible to export Windows 10 GPO's?

    ReplyDelete