Auditing your SQL Servers with Powershell

Being able to know the setup and configuration of your SQL Servers is important for many IT Professionals. Powershell, combined with SMO, makes this task easy. SMO exposes a lot of properties allowing you to easily retrieve things like Processor & RAM Information, Service Pack Level, Operating System information, Collation Settings, number of Databases, and much more. Be sure to explore SMO for your specific needs. PowerGUI, from Quest Software, has a nice Intellisense feature that makes exploring object properties easy.

Exploring object properties with PowerGUI

Here’s a Powershell script for documenting your SQL Servers setup and configuration. There’s a couple of Powershell variables in the script to configure before you get started;

$sqlservers – This points at a text file containing your list of SQL Servers. $auditDatabases – If set to $true then the script will produce an additional file documenting the databases on each SQL Server. Set to $false if you don’t want to produce this file.

?View Code POWERSHELL
# Get list of SQL servers
$sqlservers = Get-Content "C:\sqlservers.txt";
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null;
# Get the datetime to use for the log filename
$datetime = Get-Date -Format "yyyy-MM-ddThh-mm-ssZ";
$filename = "$datetime.csv";
# If database details should be audited
$auditDatabases = $true;
# Flag used to indicate column headers have been added to the database audit file
$headerAdded = $false;
 
# Add the column headers to the log file
Add-Content "C:\$filename" "sqlserver,Collation,Edition,EngineEdition,OSVersion,PhysicalMemory,Processors,VersionString,Version,ProductLevel,Product,Platform,loginMode,LinkedServerCount,databaseCount,minConfigMem,clrRunValue,clrConfigValue"; 
 
# For each SQL server listed in $sqlservers
foreach($sqlserver in $sqlservers)
{
	Write-Host "Processing sql server: $sqlserver.";
	# Create an instance of SMO.Server for the current sql server
	$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;
 
	$collation = $srv.Information.Collation;					# Server collation
	$edition = $srv.Information.Edition;						# Server edition
	$engineEdition = $srv.Information.EngineEdition;			# Engine Edition
	$OSVersion = $srv.Information.OSVersion;					# OS Version
	$PhysicalMemory = $srv.Information.PhysicalMemory;			# Physical Memory
	$Product = $srv.Information.Product;						# Server Product
	$Platform = $srv.Information.Platform;						# Server Platform
	$Processors = $srv.Information.Processors;					# Processor count
	$VersionString = $srv.Information.VersionString;			# Version String
	$Version = $srv.Information.Version;						# Version
	$ProductLevel = $srv.Information.ProductLevel;				# Product Level
 
	$loginMode = $srv.Settings.LoginMode;						# Login Mode setting
	$linkedServers = $srv.LinkedServers.Count;					# Get the number of linked servers
	$databaseCount = $srv.Databases.Count;						# Get the number of databases hosted by the sql server
	$minMem = $srv.Configuration.MinServerMemory.ConfigValue;	# Configured minimum memory
	$clrRun = $srv.Configuration.IsSqlClrEnabled.RunValue;		# SQLCLR run value
	$clrConfig = $srv.Configuration.IsSqlClrEnabled.ConfigValue;# SQLCLR config value
 
	# Write the info for the current sql server
	Add-Content "C:\$filename" "$sqlserver,$collation,$edition,$engineEdition,$OSVersion,$PhysicalMemory,$Processors,$VersionString,$Version,$ProductLevel,$Product,$Platform,$loginMode,$linkedServers,$databaseCount,$minMem,$clrRun,$clrConfig";
 
	# If $auditDatabases is true then log details of databases
	if($auditDatabases)
	{
		$dbFilename = "C:\databases_$filename";
		# Get the databases on the current server
		$databases = $srv.Databases;
 
		# Check to see if the header has been added
		if($headerAdded -eq $false)
		{
			# Add column headers to the file
			Add-Content $dbFilename "sqlserver,dbName,ActiveConnections,CaseSensitive,Collation,CompatibilityLevel,CreateDate,DefaultSchema,Owner,Size,SpaceAvailable,Status,ProcCount,TableCount,ViewCount,TriggerCount,UDFCount";
			# Set to true so the header isn't added again
			$headerAdded = $true;
		}
		# For each database on the current server
		foreach($database in $databases)
		{
			Write-Host "Processing database: $database.";
			# Get database object properties
			$dbName = $database.Name;
			$ActiveConnections = $database.ActiveConnections;
			$CaseSensitive = $database.CaseSensitive;
			$Collation = $database.Collation;
			$CompatibilityLevel = $database.CompatibilityLevel;
			$CreateDate = $database.CreateDate;
			$DefaultSchema = $database.DefaultSchema;
			$Owner = $database.Owner;
			$Size = $database.Size;
			$SpaceAvailable = $database.SpaceAvailable;
			$Status = ([string]$database.Status) -replace ",", "";
			$ProcCount = $database.StoredProcedures.Count;
			$TableCount = $database.Tables.Count;
			$ViewCount = $database.Views.Count;
			$TriggerCount = $database.Triggers.Count;
			$UDFCount = $database.UserDefinedFunctions.Count;
 
			# Append line to file for the current database
			Add-Content $dbFilename "$sqlserver,$dbName,$ActiveConnections,$CaseSensitive,$Collation,$CompatibilityLevel,$CreateDate,$DefaultSchema,$Owner,$Size,$SpaceAvailable,$Status,$ProcCount,$TableCount,$ViewCount,$TriggerCount,$UDFCount";
		}
	}
 
}

Run the script and you should see the output similar to below;

Processing sql server: SQLSERVER1.

Processing database: [db1].

Processing database: [db2].

Processing database: [db3].

Processing database: [db4].

Processing database: [db5].

Processing sql server: SQLSERVER2.

Processing database: [db1].

Processing database: [db2].

Processing database: [db3].

Processing database: [db4].

Processing database: [db5].

After running this script you will have two datetime stamped csv files in the root of your C:\ drive. Below I’ve attached some sample files;

SQL Server Audit File

SQL Server Databases Audit File


6 Comments

  1. Underthefold says:

    Very nice, I have been trying something similar using merge to drop directly into the database. I also pull my list of servers from the 2008 CMS list of servers based on grouping.I think a pretty decent CMDB can be built using powershell.
    the sqlpsx project has some good stuff and cmille19 on twitter has some great sql examples using piping instead of loops – keep up the good work!

  2. admin says:

    Hi Ian, Thanks. I’ve been meaning to check out sqlpsx for a while now. checking out http://chadwickmiller.spaces.live.com/, The sql functions look simpler than I expected!

  3. […] rhyscampbell on twitter has a great post on his site […]

  4. Jerry says:

    Ftat WVery good. Quick question: Why I’m getting this error after running the script? I will appreciate your help:

    Add-Content “C:\$filename” “sqlserver,Collation,Edition,EngineEdition,OSVersion,Phys
    calMemory,Processors,VersionString,Version,ProductLevel,Product,Platform,loginMode,LinkedServerCount,databaseCount,minC
    nfigMem,clrRunValue,clrConfigValue”;
    Add-Content : Access to the path ‘C:\2012-11-28T11-18-31Z.csv’ is denied.
    At line:1 char:12
    + Add-Content <<<< "C:\$filename" "sqlserver,Collation,Edition,EngineEdition,OSVersion,PhysicalMemory,Processors,Versi
    onString,Version,ProductLevel,Product,Platform,loginMode,LinkedServerCount,databaseCount,minConfigMem,clrRunValue,clrCo
    nfigValue";
    + CategoryInfo : PermissionDenied: (C:\2012-11-28T11-18-31Z.csv:String) [Add-Content], UnauthorizedAccess
    Exception
    + FullyQualifiedErrorId : GetContentWriterUnauthorizedAccessError,Microsoft.PowerShell.Commands.AddContentCommand

  5. Jerry says:

    Never mind.I solved it by changing the path. But then I have this new problem:

    >>
    Processing sql server: MSSQLSERVER.
    The following exception was thrown when trying to enumerate the collection: “Failed to connect to server MSSQLSERVER.”.
    At line:40 char:16
    + foreach <<<< ($database in $databases)
    + CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException
    + FullyQualifiedErrorId : ExceptionInGetEnumerator

  6. Rhys says:

    Hi Jerry,

    I’m guessing you’re trying to run this against a default instance on the localhost? Just try “localhost” and it should work.

    Cheers,

    Rhys

Leave a Reply