PowerShell – SQL Audit Script

 

As demonstrated at TechEd 2014, here is the PowerShell script.

Actually there are two scripts.

The first script is contained in this page, copy/paste (as Johan says ‘steal with pride’ 😉 and name the first:

‘SQLAuditv02.ps1’

The second was a community script that converts the CSV files to Excel.

Download that one, and name it:

‘compilereports.ps1’

Note that these scripts need to be copied to, and run from  the C:\Temp drive, change first (3) variables as needed.

As requested, if there are any enhancements that you would like to see, please let me know?

 

$SQLServer = "CM01" #use Server\Instance for SQL instances
$SQLCMDBName = "CM_Poc"

# Change path as appropriate
$dir = "C:\TEMP\"

# Default SQL Server initial DB connection
$SQLDBName = "Master"
 
# Create and Open new connection 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

# 01-Server Properties
$SqlQuery = "SELECT SERVERPROPERTY('MachineName') AS [MachineName], SERVERPROPERTY('ServerName') AS [ServerName],  
SERVERPROPERTY('InstanceName') AS [Instance], SERVERPROPERTY('IsClustered') AS [IsClustered], 
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS], 
SERVERPROPERTY('Edition') AS [Edition], SERVERPROPERTY('ProductLevel') AS [ProductLevel], 
SERVERPROPERTY('ProductVersion') AS [ProductVersion], SERVERPROPERTY('ProcessID') AS [ProcessID],
SERVERPROPERTY('Collation') AS [Collation], SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled], 
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly];"
 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
$DataSet.Tables[0] | export-csv ($dir + "01-Server Properties.csv") -notypeinformation

# 02-Windows Info
$SqlQuery = "SELECT windows_release, windows_service_pack_level, 
       windows_sku, os_language_version
FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "02-Windows Info.csv") -notypeinformation

# 03-Version Info
$SqlQuery = "SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "03-Version Info.csv") -notypeinformation

# 04-SQL Server Install Date
$SqlQuery = "SELECT @@SERVERNAME AS [Server Name], createdate AS [SQL Server Install Date] 
FROM sys.syslogins WITH (NOLOCK)
WHERE [sid] = 0x010100000000000512000000;" 

$SqlQuery = "SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "04-SQL Server Install Date.csv") –notypeinformation

# 05-Configuration Values
$SqlQuery = "SELECT name, value, value_in_use, [description] 
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);" 

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "05-Configuration Values.csv") -notypeinformation


# 06-DB File Names and paths
$SqlQuery = "SELECT DB_NAME([database_id])AS [Database Name], 
       [file_id], name, physical_name, type_desc, state_desc, 
       CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4 
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "06-DB File Names and paths.csv") -notypeinformation


# 07-DB Properties
$SqlQuery = "SELECT db.[name] AS [Database Name], 
suser_sname( owner_sid ) As [Database Owner],
db.recovery_model_desc AS [Recovery Model], 
db.log_reuse_wait_desc AS [Log Reuse Wait Description], 
ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %], 
db.[compatibility_level] AS [DB Compatibility Level], 
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,
db.is_auto_update_stats_async_on, db.is_parameterization_forced, 
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
db.is_auto_close_on, db.is_auto_shrink_on, db.is_cdc_enabled
FROM sys.databases AS db WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK) 
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%' 
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0 OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "07-DB Properties.csv") -notypeinformation


# 08-Server Hardware
$SqlQuery = "SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)], 
sqlserver_start_time --, affinity_type_desc -- (affinity_type_desc is only in 2008 R2)
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0]  | export-csv ($dir + "08-Server Hardware.csv") -notypeinformation


# 09-System Manufacturer
$SqlQuery = "EXEC xp_readerrorlog 0,1,'Manufacturer';" 

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "09-System Manufacturer.csv") -notypeinformation


# 10-Fixed Drive Freespace
$SqlQuery = "EXEC xp_fixeddrives;" 

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "10-Fixed Drive Freespace.csv") -notypeinformation



# 11-IO Util by DB
$SqlQuery = "WITH Aggregate_IO_Statistics
AS
(SELECT DB_NAME(database_id) AS [Database Name],
CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
GROUP BY database_id)
SELECT ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS [I/O Rank], [Database Name], io_in_mb AS [Total I/O (MB)],
       CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O Percent]
FROM Aggregate_IO_Statistics
ORDER BY [I/O Rank] OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "11-IO Util by DB.csv") -notypeinformation



# 12-System Memory
$SqlQuery = "SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)], 
       available_physical_memory_kb/1024 AS [Available Memory (MB)], 
       total_page_file_kb/1024 AS [Total Page File (MB)], 
       available_page_file_kb/1024 AS [Available Page File (MB)], 
       system_cache_kb/1024 AS [System Cache (MB)],
       system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);" 

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "12-System Memory.csv") -notypeinformation



# 13-Process Memory
$SqlQuery = "SELECT physical_memory_in_use_kb/1024 AS [SQL Server Memory Usage (MB)],
       large_page_allocations_kb, locked_page_allocations_kb, page_fault_count, 
       memory_utilization_percentage, available_commit_limit_kb, 
       process_physical_memory_low, process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "13-Process Memory.csv") -notypeinformation



# 14-SQL Log file freespace
$SqlQuery = "DBCC SQLPERF(LOGSPACE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "14-SQL Log file freespace.csv") -notypeinformation

# Part 2
# Change to CM database for remainder of queries
# Reconnect to previous connection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLCMDBName; Integrated Security = True"

# 15-CM File Sizes
# DB Name needed here...
$SqlQuery = "SELECT f.name AS [File Name] , f.physical_name AS [Physical Name], 
CAST((f.size/128.0) AS decimal(15,2)) AS [Total Size in MB],
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS decimal(15,2)) 
AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK) 
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) 
ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "15-CM File Sizes.csv") -notypeinformation

# 16-CM DB Statistics
$SqlQuery = "SELECT DISTINCT
     OBJECT_NAME(s.[object_id]) AS TableName,
     c.name AS ColumnName,
     s.name AS StatName,
     s.auto_created,
     s.user_created,
     s.no_recompute,
     s.[object_id],
     s.stats_id,
     sc.stats_column_id,
     sc.column_id,
     STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated
 FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
     JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
     JOIN sys.partitions par ON par.[object_id] = s.[object_id]
     JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
 WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
    AND (s.auto_created = 1 OR s.user_created = 1);"  
    
$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "16-CM DB Statistics.csv") -notypeinformation

# 17-CM Index Frag
# This one may be a long running query, it is important to determine if the indeses are fragmented.
$SqlQuery = "SELECT DB_NAME(database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name], 
i.name AS [Index Name], ps.index_id, index_type_desc,
CONVERT(decimal,  avg_fragmentation_in_percent), fragment_count, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,N'LIMITED') AS ps 
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ps.[object_id] = i.[object_id] 
AND ps.index_id = i.index_id
WHERE database_id = DB_ID()
AND page_count > 1500
ORDER BY avg_fragmentation_in_percent DESC OPTION (RECOMPILE);" 

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "17-CM Index Frag.csv") -notypeinformation

# 18 - VLF Information
# Virtual Log File - numbers below 50 are generally good. Large numbers of VLF can affect write performance, and startup times
$SqlQuery = "DBCC LOGINFO;" 

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "18-CM VLF Info.csv") -notypeinformation


$SqlConnection.Close()

# Call *.CSV to Excel converter!
Invoke-Expression c:\temp\compilereports.ps1

Advertisements
This entry was posted in PowerShell, SQL Server, TechEd 2014. Bookmark the permalink.

18 Responses to PowerShell – SQL Audit Script

  1. Dennis Mack says:

    Hello-

    I really like your audit script and was wonder if it was possible to add a column that also shows the compatibility level of each database under the database properties piece.

    One question I do have is what exactly is the variable $SQLCMDBName? Not sure what this was about.

    • configmgrmvp says:

      For Query #7, you’ll see that there is a column named compatibility_level (DB Compatibility Level), that will give you what you need.

      $SQLCMDBName – this script was written to capture core SQL information, then more in-depth information about the ConfigMgr database. So, about mid way through the script you’ll see where the database context switches to that variable. It will work for any database that you need more information.

  2. Kaspars says:

    Why it does not convert? Do nothing. I downloaded convert script to c:\temp, renamed. Audit works great but not conversion part.

  3. gopi says:

    18 csv files are creating in temp folder but those are empty

    • configmgrmvp says:

      If those csv files are empty, you’ll need to check that you are actually connecting to your SQL instance. Note there are comments in the POS script that mention the variables that need to be changed; SQL Server Instance, CM database, etc.

  4. Pingback: SCU 2015 – Top 10 ConfigMgr 2012 Issues – SQL Content Links | Steve Thompson [MVP]

  5. John Bruckler says:

    Steve, first of all, thank you for this script, it’s incredibly useful. I did make a few modifications to it, a couple were for usability, and a couple of the queries were throwing errors in my environment. Those changes are below.

    Usability wise, I dropped all the ‘clear’ statements you have. I gather those were to get rid of the output to the screen when calling Fill on the $Dataset. The problem was that it was clearing error messages, too. So instead of clearing the host screen, I replaced added “$null = ” to all of the “$SqlAdapter.Fill($DataSet)” lines so that they became “$null = $SqlAdapter.Fill($DataSet)”

    I also had to change a couple of the queries in order for them to work (I’m on Microsoft SQL Server 2012 (SP1) – 11.0.3128.0 (X64)). Those changes were:

    On Line 151 I change ‘physical_memory_in_bytes’ to ‘physical_memory_kb’ as the former is not a valid column name for me.

    On line 169, I had to add N in front of ‘Manufacturer’ so that the query became “EXEC xp_readerrorlog 0, 1, N’Manufacturer’;”

    The compiling wasn’t working for me, so I changed the last line of the script to read as follows:
    # Call *.CSV to Excel converter!
    . (Join-Path $dir ‘compilereports.ps1’)
    Get-ChildItem *.csv | ConvertCSV-ToExcel -output ‘Report.xlsx’

  6. Vetri says:

    All CSV files have been generated but conversion doesnt work, please advise..
    Do we need to change or add anything in that conversion script

    • Steve T says:

      You have to download the CSV to Excel Workbook, conversion script and save it locally. The instructions are there.

      • Vetri says:

        I have downloaded and saved locally but still conversion doesnt work. As per John advise , I have changed the last line of the script to read as follows:
        # Call *.CSV to Excel converter!
        . (Join-Path $dir ‘compilereports.ps1′)
        Get-ChildItem *.csv | ConvertCSV-ToExcel -output ‘Report.xlsx’

        It is asking for child path parameters. What needs to be given on it???

  7. Steve T says:

    Don’t have time to test right now, you could use the compilereports.ps1 as a separate process to combine those csv’s into a spreadsheet.

    • Steve, I can confirm John Bruckler’s edit works well. It looks like the compilereports.ps1 now requires input and output parameters, but does accept pipeline input.

  8. Pingback: Deployment News – Episode #7 (Watch in HD) | iPhone 7 News

  9. ers says:

    Very good script, but doesn’t work for me , here are errors:
    At C:\TEMP\SQLAuditv02.ps1:96 char:42
    + $DataSet.Tables[0] | export-csv ($dir + “05-Configuration Values.csv” …
    + ~~~~~~~~~~~~~~~~
    Unexpected token ’05-Configuration’ in expression or statement.
    At C:\TEMP\SQLAuditv02.ps1:96 char:44
    + $DataSet.Tables[0] | export-csv ($dir + “05-Configuration Values.csv” …
    + ~~~~~~~~~~~~~~
    Unexpected token ‘-Configuration’ in expression or statement.
    At C:\TEMP\SQLAuditv02.ps1:96 char:59
    + … export-csv ($dir + “05-Configuration Values.csv”) -notypeinformation
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Unexpected token ‘Values.csv”) -notypeinformation
    # 06-DB File Names and paths
    $SqlQuery = “SELECT’ in expression or statement.
    At C:\TEMP\SQLAuditv02.ps1:102 char:23
    + CONVERT( bigint, size/128.0) AS [Total Size in MB]
    + ~
    Missing argument in parameter list.
    At C:\TEMP\SQLAuditv02.ps1:103 char:1
    + FROM sys.master_files WITH (NOLOCK)
    + ~~~~
    The ‘from’ keyword is not supported in this version of the language.
    At C:\TEMP\SQLAuditv02.ps1:105 char:20
    + AND [database_id] 32767
    + ~
    Missing file specification after redirection operator.
    At C:\TEMP\SQLAuditv02.ps1:105 char:19
    + AND [database_id] 32767
    + ~
    The ‘<' operator is reserved for future use.
    At C:\TEMP\SQLAuditv02.ps1:117 char:42
    + $DataSet.Tables[0] | export-csv ($dir + "06-DB File Names and paths.c …
    + ~~~~~
    Unexpected token '06-DB' in expression or statement.
    At C:\TEMP\SQLAuditv02.ps1:117 char:44
    + $DataSet.Tables[0] | export-csv ($dir + "06-DB File Names and paths.c …
    + ~~~
    Unexpected token '-DB' in expression or statement.
    Not all parse errors were reported. Correct the reported errors and try again.
    + CategoryInfo : ParserError: (:) [], ParseException

    • ConfigMgrMVP says:

      Try isolating the connection to execute one query. I suspect that your connection to SQL Server is not quite right.

  10. Pingback: Links from our MMS ConfigMgr precon session – CTGlobal

  11. Pingback: TSQL2sday #94–SQL Server and PowerShell | Steve Thompson [MVP]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s