SQLAudit.cmd - Steve Thompson [MVP]



Contents TOC \o "1-3" \h \z \u SQLAudit.cmd PAGEREF _Toc353792848 \h 1sqlauditV3.ps1 PAGEREF _Toc353792849 \h 2compilereports.ps1 PAGEREF _Toc353792850 \h 9SQLAudit.cmdREM Note: used to invoke sqlauditV3.ps1sqlps -command "&{c:\temp\sqlauditV3.ps1}"sqlauditV3.ps1# SQL Audit script# To invoke: sqlps -command "&{c:\temp\sqlauditV2.ps1}"# 3-6-2013 Added fixed disk, freespace# 3-30-2013 Added Statistics# Change path as appropriate$dir = "c:\temp"get-childitem ($dir + "\*.csv") | foreach {$_.Delete()}# assumes that Configuration Manager uses standard naming convention$DBName = (invoke-sqlcmd -query "select * from sys.sysdatabases where name like 'CM[_]%'").Name# 01-Server Propertiesinvoke-sqlcmd -query "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];" | export-csv ($dir + "\01-Server Properties.csv") -notypeinformation# 02-Windows Infoinvoke-sqlcmd -query "SELECT windows_release, windows_service_pack_level, windows_sku, os_language_versionFROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);" | export-csv ($dir + "\02-Windows Info.csv") -notypeinformation# 03-Version Infoinvoke-sqlcmd -query "SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];" | export-csv ($dir + "\03-Version Info.csv") -notypeinformation# 04-SQL Server Install Dateinvoke-sqlcmd -query "SELECT @@SERVERNAME AS [Server Name], createdate AS [SQL Server Install Date] FROM sys.syslogins WITH (NOLOCK)WHERE [sid] = 0x010100000000000512000000;" | export-csv ($dir + "\04-SQL Server Install Date.csv") –notypeinformation# 05-Configuration Valuesinvoke-sqlcmd -query "SELECT name, value, value_in_use, [description] FROM sys.configurations WITH (NOLOCK)ORDER BY name OPTION (RECOMPILE);" | export-csv ($dir + "\05-Configuration Values.csv") -notypeinformation# 06-DB File Names and pathsinvoke-sqlcmd -query "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] <> 32767OR [database_id] = 2ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);" | export-csv ($dir + "\06-DB File Names and paths.csv") -notypeinformation# 07-DB Propertiesinvoke-sqlcmd -query "SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model], db.log_reuse_wait_desc AS [Log Reuse Wait Description], tr_value AS [Log Size (KB)], tr_value AS [Log Used (KB)],CAST(CAST(tr_value AS FLOAT) / CAST(tr_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_enabledFROM sys.databases AS db WITH (NOLOCK)INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)ON db.name = lu.instance_nameINNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK) ON db.name = ls.instance_nameWHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%' AND ls.counter_name LIKE N'Log File(s) Size (KB)%'AND tr_value > 0 OPTION (RECOMPILE);" | export-csv ($dir + "\07-DB Properties.csv") -notypeinformation# 08-Server Hardwareinvoke-sqlcmd -query "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);" | export-csv ($dir + "\08-Server Hardware.csv") -notypeinformation# 09-System Manufacturerinvoke-sqlcmd -query "EXEC xp_readerrorlog 0,1,'Manufacturer';" | export-csv ($dir + "\09-System Manufacturer.csv") -notypeinformation# 10-Fixed Drive Freespaceinvoke-sqlcmd -query "EXEC xp_fixeddrives;" | export-csv ($dir + "\10-Fixed Drive Freespace.csv") -notypeinformation# 11-IO Util by DBinvoke-sqlcmd -query "WITH Aggregate_IO_StatisticsAS(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_mbFROM 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_StatisticsORDER BY [I/O Rank] OPTION (RECOMPILE);" | export-csv ($dir + "\11-IO Util by DB.csv") -notypeinformation# 12-System Memoryinvoke-sqlcmd -query "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);" | export-csv ($dir + "\12-System Memory.csv") -notypeinformation# 13-Process Memoryinvoke-sqlcmd -query "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_lowFROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);" | export-csv ($dir + "\13-Process Memory.csv") -notypeinformation# 14-SQL Log file freespaceinvoke-sqlcmd -query "DBCC SQLPERF(LOGSPACE);" | export-csv ($dir + "\14-SQL Log file freespace.csv") -notypeinformation# 15-CM File Sizes# DB Name needed here...invoke-sqlcmd -database $DBName -query "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);" | export-csv ($dir + "\15-CM File Sizes.csv") -notypeinformation# 16-CM DB Statisticsinvoke-sqlcmd -database $DBName -query "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') = 1AND (s.auto_created = 1 OR s.user_created = 1);" | 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.# Needed to extend query timeout parameter for this one to run!invoke-sqlcmd -QueryTimeout 65535 -database $DBName -query "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,avg_fragmentation_in_percent, fragment_count, page_countFROM 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_idWHERE database_id = DB_ID()AND page_count > 1500ORDER BY avg_fragmentation_in_percent DESC OPTION (RECOMPILE);" | export-csv ($dir + "\17-CM Index Frag.csv") -notypeinformationcompilereports.ps1################################# Functions uses for converting .csvs to one .xls ########################################### Release-Ref ($ref) { ([System.Runtime.InteropServices.Marshal]::ReleaseComObject( [System.__ComObject]$ref) -gt 0) [System.GC]::Collect() [System.GC]::WaitForPendingFinalizers() }Function ConvertCSV-ToExcel{<# .SYNOPSIS Converts one or more CSV files into an excel file. .DESCRIPTION Converts one or more CSV files into an excel file. Each CSV file is imported into its own worksheet with the name of the file being the name of the worksheet. .PARAMETER inputfile Name of the CSV file being converted .PARAMETER output Name of the converted excel file .EXAMPLE Get-ChildItem *.csv | ConvertCSV-ToExcel -output 'report.xlsx' .EXAMPLE ConvertCSV-ToExcel -inputfile 'file.csv' -output 'report.xlsx' .EXAMPLE ConvertCSV-ToExcel -inputfile @("test1.csv","test2.csv") -output 'report.xlsx' .NOTES Author: Boe Prox Date Created: 01SEPT210 Last Modified: #> #Requires -version 2.0 [CmdletBinding( SupportsShouldProcess = $True, ConfirmImpact = 'low',DefaultParameterSetName = 'file' )]Param ( [Parameter( ValueFromPipeline=$True, Position=0, Mandatory=$True, HelpMessage="Name of CSV/s to import")] [ValidateNotNullOrEmpty()] [array]$inputfile, [Parameter( ValueFromPipeline=$False, Position=1, Mandatory=$True, HelpMessage="Name of excel file output")] [ValidateNotNullOrEmpty()] [string]$output )Begin { #Configure regular expression to match full path of each file [regex]$regex = "^\w\:\\" #Find the number of CSVs being imported $count = ($inputfile.count -1) #Create Excel Com Object $excel = new-object -com excel.application #Disable alerts $excel.DisplayAlerts = $False #Show Excel application $excel.Visible = $False #Add workbook $workbook = $excel.workbooks.Add() #Remove other worksheets $workbook.worksheets.Item(2).delete() #After the first worksheet is removed,the next one takes its place $workbook.worksheets.Item(2).delete() #Define initial worksheet number $i = 1 }Process { ForEach ($input in $inputfile) { #If more than one file, create another worksheet for each file If ($i -gt 1) { $workbook.worksheets.Add() | Out-Null } #Use the first worksheet in the workbook (also the newest created worksheet is always 1) $worksheet = $workbook.worksheets.Item(1) #Add name of CSV as worksheet name $worksheet.name = "$((GCI $input).basename)" #Open the CSV file in Excel, must be converted into complete path if no already done If ($regex.ismatch($input)) { $tempcsv = $excel.Workbooks.Open($input) } ElseIf ($regex.ismatch("$($input.fullname)")) { $tempcsv = $excel.Workbooks.Open("$($input.fullname)") } Else { $tempcsv = $excel.Workbooks.Open("$($pwd)\$input") } $tempsheet = $tempcsv.Worksheets.Item(1) #Copy contents of the CSV file $tempSheet.UsedRange.Copy() | Out-Null #Paste contents of CSV into existing workbook $worksheet.Paste() #Close temp workbook $tempcsv.close() #Select all used cells $range = $worksheet.UsedRange #Autofit the columns $range.EntireColumn.Autofit() | out-null $i++ } } End { #Save spreadsheet $workbook.saveas("$pwd\$output") Write-Host -Fore Green "File saved to $pwd\$output" #Close Excel $excel.quit() #Release processes for Excel $a = Release-Ref($range) }} ################################# end conversion functions ########################################### get-childitem ("*.csv") | ConvertCSV-ToExcel -output ($DBNAME + ((get-date).tostring("yyyyMMddHHmmss")))get-childitem ("*.csv") | sort -descending | ConvertCSV-ToExcel -output ($DBNAME + ((get-date).tostring("yyyyMMddHHmmss"))) ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download