Powershell to collect SQLServer compliant info Verson 2

We established some best practice rules for SQL Server, we need method to collect these data and analyze the compliance.

Powershell script is our solution。

The Best Practice rules are:

##• Do not install sqlserver on C drive,
##• do not put database files on C drive
##• Set maximum memory: should not left as default, leave 1GB memory for OS
##• Agent start automatically
##• Database auto growth on, with 10% percentage
##• Database backup regularly, either by SQLServer maintenance, Netbackup agent or Doc Ave
##• Transaction log backup regularly to prevent db size growth out of control
##• Disable CLR, with exception on kdcwdbsqlprd03 where respontek requires CLR.
##• Disable xp_cmdshell
##• Database recovery mode: full recovery mode for production, simple recovery mode for PPE
##• Turn off DB autoshrink


Step 1: create the tables in oracle where the data will be saved, we choose oracle, it can be MSSQL,

create table mssqlhostcpuinfo (hostname varchar2(20), cores int,logicalcors int);
create table mssqlhostmemoryinfo (hostname varchar2(20), membytes number(13));
create table mssqlhostdiskinfo (hostname varchar2(20), diskletter varchar2(2),capacity number(13), freedisk number(13));
create table mssqlhostosinfo (hostname varchar2(20), os varchar2(60),version varchar2(15));
create table mssqlhostdbinfo (hostname varchar2(20), instancename varchar2(30), dbname varchar2(100),recoverymode varchar2(10),status varchar2(10),version varchar2(15),sp varchar2(10),edition varchar2(60));
create table mssqldbinfo (hostname varchar2(20), instancename varchar2(30), dbname varchar2(200),recoverymode varchar2(10),status varchar2(10),version varchar2(15),sp varchar2(10),edition varchar2(60),dbsizeMB varchar2(20),filename varchar2(200),ispercentgrowth varchar2(1),growth varchar2(20),autoshrink varchar2(1));
create table mssqlserviceinfo (hostname varchar2(20), instancename varchar2(30), servicename varchar2(30),state varchar2(10),startmode varchar2(10),pathname varchar2(200));
create table mssqlconfiginfo (hostname varchar2(20), instancename varchar2(30), name varchar2(50),value varchar2(20),description varchar2(100));
create table mssqldbbackupinfo (hostname varchar2(20), instancename varchar2(30), dbname varchar2(100),startdate varchar2(20),finishdate varchar2(20),backuptype varchar2(15),backupsize varchar2(20),physicalname varchar2(800));
--create table mssqlmaintplaninfo (hostname varchar2(20), instancename varchar2(30), planname varchar2(100),description varchar2(200),finishdate varchar2(20),backuptype varchar2(15),backupsize varchar2(20),physicalname varchar2(200));
create table mssqlserverlist (hostname varchar2(20), instancename varchar2(30))

Step 2: Powershell script: it will truncate the info tables, remove the output files, collect info and save info to output files, load data from output files to oracle tables using sqlldr

#* FileName: ServerInventory.ps1
#*=============================================================================
#* Script Name: [CollectMSSQLServerinfo.ps1]
#* Created: [Nov 6th, 2018]
#* Author: jiulu Sun
#* Company: BCFerries
# usage : BCFerries data service team managed sqlservers
# How to use : run powershell, type $PathOf\filename.ps1, it will loop all servers defined in $arrComputers
# you need to change executionpolicy first if its at restricted mode. or you will get an error
# "File C:\vb\sqlserver_info1.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details."
# this is an example
# PS C:\vb> Get-ExecutionPolicy
# Restricted
# PS C:\vb> set-executionpolicy remotesigned
# PS C:\vb> Get-ExecutionPolicy
# RemoteSigned

#get the sqlserver list
$OutputfileServerList = "C:\projects\mssql\CMDB\MSSQLServerList.txt"
$server="emrept"
remove-item -Path $OutputfileServerList -ErrorAction SilentlyContinue
$sql= @"
set echo off
set pagesize 0
set termout off
set feedback off
set pause off
set verify off
set pagesize 0
--select distinct '"'||hostname||'","'||instancename||'"' from mssqlserverlist;
select distinct instancename from mssqlserverlist;
"@
(echo $sql)|sqlplus -S jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))" > $OutputfileServerList

# loop the instancenames, define outputfiles names
#$arrComputers = get-Content -Path "C:\projects\mssql\CMDB\SQLServerList.txt"
$arrComputers = get-Content -Path $OutputfileServerList
$OutputfileCPU = "C:\projects\mssql\CMDB\MSSQLCPU.txt"
$OutputfileMem = "C:\projects\mssql\CMDB\MSSQLMem.txt"
$OutputfileDisk = "C:\projects\mssql\CMDB\MSSQLDisk.txt"
$OutputfileDB = "C:\projects\mssql\CMDB\MSSQLDB.txt"
$OutputfileDBBackup = "C:\projects\mssql\CMDB\MSSQLDBBackup.txt"
$OutputfileOS = "C:\projects\mssql\CMDB\MSSQLOS.txt"
$OutputfileService = "C:\projects\mssql\CMDB\MSSQLService.txt"
$OutputfileConfig = "C:\projects\mssql\CMDB\MSSQLConfig.txt"

#clear the outputfiles
remove-item -Path $OutputfileCPU -ErrorAction SilentlyContinue
remove-item -Path $OutputfileMem -ErrorAction SilentlyContinue
remove-item -Path $OutputfileDisk -ErrorAction SilentlyContinue
remove-item -Path $OutputfileDB -ErrorAction SilentlyContinue
remove-item -Path $OutputfileDBBackup -ErrorAction SilentlyContinue
remove-item -Path $OutputfileOS -ErrorAction SilentlyContinue
remove-item -Path $OutputfileService -ErrorAction SilentlyContinue
remove-item -Path $OutputfileConfig -ErrorAction SilentlyContinue

$ONETAB+=" "
$TWOTABS=" "
$BLANKLINE=""

Function SQLserviceinfo {
Get-WmiObject -computername $strComputer win32_service | ?{$_.Name -like '*sql*'} | select systemname,@{ Name = 'instancename'; Expression = {  $strInstance }},Name, State, startmode, PathName|Export-Csv -Path $OutputfileService -append -NoTypeInformation -force
}

Function MemInfo {
Get-WmiObject Win32_ComputerSystem -Namespace "root\CIMV2" -ComputerName $strComputer|Select-Object -Property "Name","TotalPhysicalMemory" |Export-Csv -Path $OutputfileMEM -append -NoTypeInformation -force
}

Function OSInfo {
 Get-WmiObject Win32_OperatingSystem -ComputerName $strComputer|select-object csname,caption,version|Export-Csv -Path $OutputfileOS -append -NoTypeInformation -force
}

Function CPUInfo {
$property = "systemname","numberOfCores", "NumberOfLogicalProcessors"
Get-WmiObject win32_processor -computername $strComputer |Select-Object -Property $property |sort -unique|Export-Csv -Path $OutputfileCPU -append -NoTypeInformation -force
}

Function DiskInfo {
$property = "__SERVER","DriveLetter","Capacity","FreeSpace"
Get-WmiObject Win32_Volume -filter "DriveLetter IS NOT NULL AND FileSystem IS NOT NULL" -computername $strComputer|Select-Object -Property $property|Export-Csv -Path $OutputfileDISK -append -NoTypeInformation -force
}

Function sysdbbackupInfo {
$sql= @"
SET NOCOUNT ON
SELECT CONVERT(varchar(80),serverproperty('MachineName'))
+ ',' +CONVERT(varchar(100), SERVERPROPERTY('Servername'))
+ ',' +msdb.dbo.backupset.database_name
+ ',' +CONVERT(varchar,msdb.dbo.backupset.backup_start_date,120)
+ ',' +CONVERT(varchar,msdb.dbo.backupset.backup_finish_date,120)
+ ',' +CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END
+ ',' +CONVERT(varchar(100),msdb.dbo.backupset.backup_size)
+ ',' +msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1)
"@
#invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|Export-Csv -append -path $OutputfileDB -NoTypeInformation -force
invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|ConvertTo-Csv -NoTypeInformation -Delimiter "," | Select-Object -Skip 1 | % {$_ -replace '"', ""} | Out-File -Force  -append -FilePath $OutputfileDBBackup
}

Function sysdbInfo {
$sql= @"
SET NOCOUNT ON
SELECT CONVERT(varchar(80),serverproperty('MachineName'))
+ ',' + CONVERT(varchar(64),serverproperty ('servername'))
+ ',' + name
+ ',' + CONVERT(varchar(20),DATABASEPROPERTYEX(sd.name, 'Recovery'))
+ ',' + CONVERT(varchar(20),DATABASEPROPERTYEX(sd.name, 'Status'))
+ ',' + RTRIM (CONVERT(char(20),serverproperty('ProductVersion')))
+ ',' + CONVERT(varchar(20), serverproperty('ProductLevel'))
+ ',' + CONVERT(varchar(80),SERVERPROPERTY('Edition'))
FROM master.dbo.sysdatabases sd
"@
#invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|Export-Csv -append -path $OutputfileDB -NoTypeInformation -force
invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|ConvertTo-Csv -NoTypeInformation -Delimiter "," | Select-Object -Skip 1 | % {$_ -replace '"', ""} | Out-File -Force  -append -FilePath $OutputfileDB
}

Function sysdbInfo2 {
$sql= @"
SET NOCOUNT ON
SELECT CONVERT(varchar(80),serverproperty('MachineName'))
+ ',' + CONVERT(varchar(64),serverproperty ('servername'))
+ ',' + sd.name
+ ',' + CONVERT(varchar(20),DATABASEPROPERTYEX(sd.name, 'Recovery'))
+ ',' + CONVERT(varchar(20),DATABASEPROPERTYEX(sd.name, 'Status'))
+ ',' + RTRIM (CONVERT(char(20),serverproperty('ProductVersion')))
+ ',' + CONVERT(varchar(20), serverproperty('ProductLevel'))
+ ',' + CONVERT(varchar(80),SERVERPROPERTY('Edition'))
+ ',' + convert(varchar(20),(CAST(smf.size AS FLOAT) * 8096) / (1024 * 1024))
+ ',' + smf.physical_name
+ ',' + convert(varchar(1),smf.is_percent_growth)
+ ',' + convert(varchar(20),smf.growth)
+ ',' + CONVERT(varchar(1),DATABASEPROPERTYEX(sd.name, 'IsAutoShrink'))
  FROM master.sys.databases sd
  INNER JOIN sys.master_files smf ON sd.database_id = smf.database_id
  WHERE
  smf.type_desc IN (
      'ROWS'
      ,'LOG'
    )
"@
#invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|Export-Csv -append -path $OutputfileDB -NoTypeInformation -force
invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|ConvertTo-Csv -NoTypeInformation -Delimiter "," | Select-Object -Skip 1 | % {$_ -replace '"', ""} | Out-File -Force  -append -FilePath $OutputfileDB
}

Function sqlconfiginfo {
$sql= @"
SET NOCOUNT ON
SELECT CONVERT(varchar(80),serverproperty('MachineName'))
+ ',' + CONVERT(varchar(64),serverproperty ('servername'))
+ ','+ name+ ',' + CONVERT(varchar(20),value)+ ','  +[description]
FROM sys.configurations
WHERE lower(name) like '%max server memory%'
or lower(name) like '%degree of parallel%'
or lower(name) like '%xp_cmdshell%'
or lower(name) like '%clr%'
"@
invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|ConvertTo-Csv -NoTypeInformation -Delimiter "," | Select-Object -Skip 1 | % {$_ -replace '"', ""} | Out-File -Force  -append -FilePath $OutputfileConfig
}

#*=============================================================================
#* SCRIPT BODY
#*=============================================================================

#truncate the cmdb tables before
$server="emrept"
(echo "truncate table mssqlhostcpuinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"
(echo "truncate table mssqlhostmemoryinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"
(echo "truncate table mssqlhostdiskinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"
(echo "truncate table mssqlhostosinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"
(echo "truncate table mssqlhostdbinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"
(echo "truncate table mssqlserviceinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"
(echo "truncate table mssqlconfiginfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"
(echo "truncate table mssqldbinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"
(echo "truncate table mssqldbbackupinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"

foreach ($strComputer in $arrComputers)

{ #Function Calls go here

"#########################################################################"
"Computer Name:" +$strComputer
"#########################################################################"

#* Call database Function
$BLANKLINE
"@@@SQLServer database information"
sysdbInfo2

#* Call database Function
$BLANKLINE
"@@@SQLServer database information"
sysdbbackupInfo

#* Call sqlconfiginfo Function
"SQLConfiginfo Information"
SQLconfiginfo

####now remove the name instance.
if ($strComputer.contains("\"))
{#'this is a named instance
$position=$strComputer.indexof("\")
$positionplus1=$strComputer.indexof("\")+1
$mylength=$strComputer.length-$positionplus1
$strInstance= $strComputer.substring($positionplus1,$mylength)
$strComputer = $strComputer.substring(0,$position)
}
else
{
$strInstance="default"
}
"computer name is :" + $strComputer
"instance name is :" + $strInstance

#* Call SysInfo Function
"Sytem Information"
MemInfo

#* Call OSInfo Function
"Operating System Information"
OSInfo

#* Call CPUInfo Function
"Processor Information"
CPUInfo

#* Call DiskInfo Function
"Disk Information"
DiskInfo

#* Call SQLserviceinfo Function
"SQLserviceinfo Information"
SQLserviceinfo

}


#now load these tables
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlcpu.ctl log=C:\projects\MSSQL\CMDB\mssqlcpu.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlOS.ctl log=C:\projects\MSSQL\CMDB\mssqlOS.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlMEM.ctl log=C:\projects\MSSQL\CMDB\mssqlMEM.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlDisk.ctl log=C:\projects\MSSQL\CMDB\mssqlDISK.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlservice.ctl log=C:\projects\MSSQL\CMDB\mssqlservice.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlconfig.ctl log=C:\projects\MSSQL\CMDB\mssqlconfig.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlDB2.ctl log=C:\projects\MSSQL\CMDB\mssqldbinfo.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlDBbackup.ctl log=C:\projects\MSSQL\CMDB\mssqlDBbackup.log

Step 3. Create controlfiles for sqlldr:
mssqlconfig.ctl:
LOAD DATA
CHARACTERSET AL16UTF16
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLconfig.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlconfig.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlconfig.dis'
    APPEND
    INTO TABLE jsun.mssqlconfiginfo
    --when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     INSTANCENAME char,
     NAME char,
     value char,
     Description char
     --     edition char "rtrim(:edition)"
     )
MSSQLCPU.ctl:
    LOAD DATA
CHARACTERSET UTF8
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLCPU.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlcpu.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlcpu.dis'
    APPEND
    INTO TABLE jsun.mssqlhostcpuinfo
    when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     CORES ,
     LOGICALCORS
    )
MSSQLDB2.ctl:
LOAD DATA
CHARACTERSET AL16UTF16
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLDB.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlDB.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlDB.dis'
    APPEND
    INTO TABLE jsun.mssqldbinfo
    --when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     INSTANCENAME char,
     DBNAME char(200),
     RECOVERYMODE char,
     STATUS char,
     VERSION char,
     SP char,
   --  edition char "trim(null from trim(:edition))"
     edition char "rtrim(:edition)",
     dbsizemb char,
     filename char(200),
     ispercentgrowth char,
     growth char,
     autoshrink char
     )
  MSSQLDBBackup.ctl:
LOAD DATA
CHARACTERSET AL16UTF16
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLDBBackup.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\MSSQLDBBackup.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\MSSQLDBBackup.dis'
    APPEND
    INTO TABLE jsun.mssqldbbackupinfo
    --when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     INSTANCENAME char,
     DBNAME char,
     --recoverymode char,
     startdate char,
     finishdate char,
     backuptype char,
     backupsize char,
     physicalname char(4000)
     )
MSSQLDisk.ctl:
 LOAD DATA
CHARACTERSET UTF8
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLDISK.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlDISK.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlDISK.dis'
    APPEND
    INTO TABLE jsun.mssqlhostdiskinfo
    when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     diskletter char,
     capacity,
     freedisk
     )
MSSQLMemory.ctl:
LOAD DATA
CHARACTERSET UTF8
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLMEM.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlMEM.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlMEM.dis'
    APPEND
    INTO TABLE jsun.mssqlhostmemoryinfo
    when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     membytes
     )
MSSQLOS.ctl:
LOAD DATA
CHARACTERSET UTF8
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLOS.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlOS.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlOS.dis'
    APPEND
    INTO TABLE jsun.mssqlhostosinfo
    when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     OS char ,
     version char
    )
MSSQLService.ctl:
LOAD DATA
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLService.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlService.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlService.dis'
    APPEND
    INTO TABLE jsun.mssqlserviceinfo
    --when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     INSTANCENAME char,
     servicename char,
     state char,
      startmode char,
     pathname char
     )
 
  Step 4: run the powershell script
step 5: analyze the compliant data:

  Best practices:

##•    Do not install sqlserver on C drive: violation 51%

  select sum(case instr(upper(pathname),'C:\') when 0 then 0 else 1 end)/count(*) Install_On_Cdrive,1-sum(case instr(upper(pathname),'C:\') when 0 then 0 else 1 end)/count(*) Install_Not_On_Cdrive,count(*) serviceCount

  from jsun.mssqlserviceinfo where (servicename like 'MSSQL$%' or servicename like 'MSSQLSERVER')

SQL> /

INSTALL_ON_CDRIVE INSTALL_NOT_ON_CDRIVE SERVICECOUNT

----------------- --------------------- ------------

       .514285714            .485714286           35

##•    do not put database files on C drive: Violation 9.8%

select sum(case instr(upper(filename),'C:\') when 0 then 0 else 1 end)/count(*) DBFile_On_Cdrive,1-sum(case instr(upper(filename),'C:\') when 0 then 0 else 1 end)/count(*) DBFile_Not_On_Cdrive,count(*) DBFileCount from jsun.mssqldbinfo

SQL> /

DBFILE_ON_CDRIVE DBFILE_NOT_ON_CDRIVE    DBFileCOUNT

---------------- -------------------- ----------

      .098191214           .901808786       1161

##•    Set maximum memory: (should not left as default.) violation: 78%

n  The value  in table mssqlconfiginfo for memory is MB, so we use 1024000 as threat hold, which is 1TB

select sum(case when (value >1024000) then 1 else 0 end)/count(*) Set_As_Max_memory, 1-sum(case when (value >1024000) then 1 else 0 end)/count(*) Set_As_Below_Max_memory,count(*) InstanceCount from MSSQLCONFIGINFO where lower(name) like '%memory%'

SQL> /

SET_AS_MAX_MEMORY SET_AS_BELOW_MAX_MEMORY INSTANCECOUNT

----------------- ----------------------- -------------

       .777777778              .222222222            27

##•    Agent start automatically: compliant 88%

SQL> select sum(case when startmode like 'Auto%' then 1 else 0 end)/count(*) Agent_Auto_Startup,count(*) ServiceCount from MSSQLSERVICEINFO where upper(SERVICENAME) like '%AGENT%';

AGENT_AUTO_STARTUP SERVICECOUNT

------------------ ------------

        .882352941           34

##•    Database auto growth on: compliant 42%

SQL> select sum(case when (ISPERCENTGROWTH='1' ) then 1 else 0 end)/count(*) db_grow_by_percentage,count(*) DBFileCount from MSSQLDBINFO;

DB_GROW_BY_PERCENTAGE    DBFileCOUNT

--------------------- ----------

            .42377261       1161

##•    Database backup regularly, (either by SQLServer maintenance, Netbackup agent or Doc Ave(new sharepoint)) violation: 38%

SELECT b.numb / a.numb No_backup_24hrs,a.numb DBCount

  FROM (SELECT COUNT (distinct instancename||dbname) numb FROM jsun.MSSQLDBINFO) a,

       (SELECT COUNT (distinct instancename||dbname) numb FROM jsun.MSSQLDBINFO dbinfo

         WHERE NOT EXISTS

                      (SELECT 1

                         FROM jsun.MSSQLDBBACKUPINFO bkinfo

                        WHERE     bkinfo.instancename = dbinfo.instancename

                              AND bkinfo.dbname = dbinfo.dbname)) b

NO_BACKUP_24HRS    DBCOUNT

--------------- ----------

     .380145278        413

##•    Transaction log backup regularly to prevent db size growth out of control: violation 34%

SELECT b.numb / a.numb No_Log_backup_24hrs,a.numb DBCount

  FROM (SELECT COUNT (distinct instancename||dbname) numb FROM jsun.MSSQLDBINFO where recoverymode<>'SIMPLE') a,

       (SELECT COUNT (distinct instancename||dbname) numb

          FROM jsun.MSSQLDBINFO dbinfo

         WHERE recoverymode<>'SIMPLE'

       and  NOT EXISTS

                      (SELECT 1

                         FROM jsun.MSSQLDBBACKUPINFO bkinfo

                        WHERE     bkinfo.instancename = dbinfo.instancename

                              AND bkinfo.dbname = dbinfo.dbname)) b

/

NO_LOG_BACKUP_24HRS    DBCOUNT

------------------- ----------

         .341584158        202

##•    Disable CLR. Violation : 0%

SQL> select sum(case value when '0' then 0 else 1 end) /count(*) CLR_Disabled,count(*) InstanceCount from MSSQLCONFIGINFO where name ='clr enabled';

CLR_DISABLED INSTANCECOUNT

------------ -------------

           0            27

##•    Disable xp_cmdshell : violation 0%

SQL> select sum(case value when '0' then 0 else 1 end) /count(*)  xpcmdshell_Disabled,count(*) InstanceCount from MSSQLCONFIGINFO where name ='xp_cmdshell';

XPCMDSHELL_DISABLED INSTANCECOUNT

------------------- -------------

0             27

##•    Rebuild/reorg index by maintenance plan or manually scheduled work

          Cannot get this yet, the value is in xml format cannot be easily get.

##•    Database recovery mode: (full recovery mode for production), violation 33%

Select sum(case recoverymode when ‘SIMPLE’ then 1 else 0 end)/count(*)  PRD_IN_SIMPLE_MODE, count(*) PRDCount

From

(

select distinct case instr(instancename,'PRD') when 0 then 'PPE' else 'PRD' end ENV , instancename, dbname,recoverymode from mssqldbinfo

) dbinfo

Where env=’PRD’;

PRD_IN_SIMPLE_MODE   PRDCOUNT

------------------ ----------

        .329113924        237

##•    Turn off DB autoshrink: violation 3%

select sum(case autoshrink when '1' then 1 else 0 end)/count(*) autoshrink,count(*) dbcount from (select distinct instancename,dbname,autoshrink from mssqldbinfo)

AUTOSHRINK    DBCOUNT

---------- ----------

.031476998        413