Automating Database and Tables Size Checks

Working with SCCM, WSUS and other SQL DB driven applications, it seems like every once in a while you find that the database has grown out of control. Whenever this happens, obviously we have to quickly determine why it is so big, which means that we have to figure out what part of the database is growing and eating up all of that space. The sp_spaceused stored procedure is an easy and quick way to get database and database object sizes.

To get the database size, we can simply execute:
    exec sp_spaceused
This tells us how big the database is, and breaks it down into data, reserved space, index size, and unused space. As sweet as that is, at this point we already know that the db is big, but we need to figure out why. Again, we can use the sp_spaceused stored procedure to check specific tables that we know may be problematic. For instance, if your WSUS SUSDB database is out of control, you could check the size of the tbEventInstance table by firing off:
exec sp_spaceused 'tbEventInstance'

This is great if you have a pretty good idea about which table is causing problems or if your dealing with a database that only has a couple of tables, but in systems like SCCM or WSUS, there could be any number of tables causing the problem. Executing that command against each table would take far too long. We can, however, use the sp_msForEachTable stored procedure to execute it against all tables at once:
exec sp_msForEachTable 'exec sp_spaceused [?]'

This gets us the size of each table, which is what we want. The problem is that we get a different result for each execution, that looks something like this:

This is a good start, because now we just have to scan the list, but we can't sort this, so its still a manual process of looking through all of the output to find the problem. In a system like SCCM with a massive number of tables, this could still take a very long time.

From here we can solve that problem by adding some SQL to create a temp table, dump the results of the command into that table, and then display the sorted contents of that table.



declare @tablesize table (
    [name] nvarchar(256),
    [rows] varchar(18),
    reserved varchar(18), 
    data varchar(18), 
    index_size varchar(18),
    unused varchar(18)
)


insert @tablesize exec sp_msForEachTable 'exec sp_spaceused [?]' 
select * from @tablesize order by data desc



Now we have exactly what we want, with the largest tables right at the top!


This is much improved, but if we wanted to take it a step further, we could actually use PowerShell to run this query on a regular schedule and alert us if a table grows over a certain threshold. PowerShell makes it easy to grab the results of our exec sp_msForEachTable 'exec sp_spaceused [?]' and put it into a data set.



$SQLQuery = "exec sp_msForEachTable 'exec sp_spaceused [?]'"
$Conn = New-Object System.Data.SqlClient.SqlConnection
$Conn.ConnectionString = "Server = mydbserver; Database = MyDB; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $Conn
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
[void]$SqlAdapter.Fill($DataSet)
$Conn.Close()
$DataSet.Tables

Now you can loop through the $DataSer.Tables, check for a desired threshold, and execute actions if certain conditions are met. Then you will not only know that the database is growing rapidly before anyone notices, but you'll have the offending table identified so that you can go right to work on solving the problem. That certainly beats wasting time trying to identify the problem after people are complaining about application performance!

PowerShell Script to Backup and Delete List of GPO's (with Restore Script)

One of my first tasks in cleaning up the previously referenced group policy infrastructure, was to delete all of the unused Group Policy Objects. There were a lot, so a script was definitely in order. Fortunately, PowerShell has a native cmdlet (via the Group Policy Module), delete-gpo, that can delete group policy objects. I could very easily whip up a script to loop through a file and delete policies, but when we're talking about scripts and production environments, safety always comes first. It would be irresponsible to simply delete all of the policies, without a plan to quickly recover if something went wrong. Again, PowerShell makes it easy by having native cmdlets backup-gpo and restore-gpo.

So to be safe and prepared called for two scripts: one to backup and delete the GPOs, and one to restore them if necessary. Since it best to use a Backup ID to restore a GPO, the logging in the first script gives us both the Backup ID and the GUID. To make restoring as quickly and easy as possible, the scripts are configured so that the log from the backup/delete script can simply be passed to the restore script, and it will restore everything in the log file.

One thing that is important to point out is that restoring a GPO does not restore its links. In my case, I was only deleting unlinked GPO's, so this was not an issue. However, if any GPO's were linked before being deleted, they will need to be re-linked in order to take effect.

Script #1: Delete-GPOList.ps1


# Usage: Delete-GPOList -file <path to file> -path <path for backups>
# Creates Two Log Files:
# Success Log: Delete-GPOList_<date>.log
# Error Log: Delete-GPOList_Errors_<date>.log


Param(
[parameter(Mandatory=$true)] [string] $path,
[parameter(Mandatory=$true)] [string] $file
) 


import-module grouppolicy


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


#Backs up a GPO and then deletes it
function BackupDelete-GPO
{
param($GPOGUID, $BackupPath)

#clears any errors
$error.clear()


#Backup policy and check for errors
write-host Backing Up:`t $GPOGUID
$GPObackup = backup-gpo -guid $GPOGUID -path $BackupPath
if ($error) { return "Backup Error" }
write-host `t Backup Completed! Backup ID: $GPOBackup.Id.ToString()


#Delete policy and check for errors
write-host Deleting:`t $GPOGUID
remove-gpo -guid $GPOGUID
if ($error) { return "Delete Error" }
write-host `t Deletion Successful
return $GPOBackup.Id.ToString()
}




#-----------------------------------------------
# Test Backup and File Path
#-----------------------------------------------
if (!(test-path $path)) { 
write-warning "Backup Path Invalid: $Path"
break
}
if (!(test-path $file)) { 
write-warning "File Not Found: $File"
break
}


#-----------------------------------------------
# Creates Log File
#-----------------------------------------------


$date = (get-date).ToString('yyyy-MM-dd')
$logfile = "Delete-GPOList_$date.log"
$errorlog = "Delete-GPOList_Errors_$date.log"


#-----------------------------------------------
# Parses file and and backs up deletes GPO's
#-----------------------------------------------
Get-Content $file | foreach {
$GPOGUID = $_
$Delete = BackupDelete-GPO $GPOGUID $path
if ($Delete -eq "Backup Error") {
"Backup Error: $GPOGUID" >> $errorlog
} elseif ($Delete -eq "Delete Error") {
"Delete Error: $GPOGUID" >> $errorlog
} else {
"$GPOGUID;$Delete" >> $logfile
}
}



Script #2: Restore-GPOList.ps1

# Usage: Restore-GPOList -file <GPO-Delete Log> -path <Backup Path>


Param(
[parameter(Mandatory=$true)] [string] $path,
[parameter(Mandatory=$true)] [string] $file
) 


import-module grouppolicy


Get-Content $File | foreach {
$GPOGUID = ($_ -split ";")[0]
$BackupID = ($_ -split ";")[1]

$error.clear()
write-host Restoring: $GPOGUID
restore-gpo -BackupId $BackupID -path $Path

if ($error) {
write-warning Error Restoring:
write-warning `t GPO GUID:`t $GPOGUID
write-warning `t Backup ID:`t $BackupID
} else {
write-host `tComplete!
}
}

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