jeudi 26 juillet 2018

Powershell : audit des serveurs SQL Server

J'ai développé en prenant comme base ce script sur le TechNet un script Powershell récupérant diverses informations propres aux serveurs SQL Server dont le nom est renseigné dans un fichier CSV.

Le script parcourt dans le fichier CSV spécifié la colonne ayant pour entête ServerName (si jamais votre extract d'origine comprend plusieurs colonnes, comme le nom de l'instance par exemple) et va tenter de récupérer plusieurs informations pour chacun des hostname lus. Une fois un serveur traité, les détails recueillis sont ajoutés à un fichier CSV exploitable par la suite dans Excel par exemple.


Un exemple de fichier d'entrée avec les hostname des serveurs SQL à auditer.

Je n'ai pas réalisé de contrôles particuliers sur l'écriture ou l'accès aux serveurs SQL dans le script, il faudra donc en cas de dysfonctionnement quelconque s'assurer que les fichiers CSV d'entrée et sortie puissent être lus et écrits sur le disque et que l'utilisateur exécutant le script ait un minimum de privilèges sur SQL Server.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

function Get_ProperOS{
    param($OS)
    if ($OS -like '5.0*') { return "Windows Server 2000" }
    elseif ($OS -like '5.2*') { return "Windows Server 2003" }
    elseif ($OS -like '6.0*') { return "Windows Server 2008" }
    elseif ($OS -like '6.1*') { return "Windows Server 2008 R2" }
    elseif ($OS -like '6.2*') { return "Windows Server 2012" }
    elseif ($OS -like '6.3*') { return "Windows Server 2012 R2" }
    elseif ($OS -like '10.0*') { return "Windows Server 2016" }
    else { return "Unknown NT version" }
}

function Get_ProperSQL{
    param($SQL)
    if ($SQL -like '8.0*') { return "SQL Server 2000" }
    elseif ($SQL -like '9.0*') { return "SQL Server 2005" }
    elseif ($SQL -like '10.0*') { return "SQL Server 2008" }
    elseif ($SQL -like '10.5*') { return "SQL Server 2008 R2" }
    elseif ($SQL -like '11.0*') { return "SQL Server 2012" }
    elseif ($SQL -like '12.0*') { return "SQL Server 2014" }
    elseif ($SQL -like '13.0*') { return "SQL Server 2016" }
    elseif ($SQL -like '14.0*') { return "SQL Server 2017" }
    else { return "Unknown SQL version" }
}

function Get_ServerName{
    param($SqlHostProperties)
    $SrvHostname = $SqlHostProperties | where {$_.name -eq "ComputerNamePhysicalNetBios"} | select value
    return $SrvHostname.Value
}

function Get_ServerMemory{
    param($SqlHostProperties)
    $SrvMemory = $SqlHostProperties | where {$_.name -eq "PhysicalMemory"} | select value
    return $SrvMemory.Value
}

function Get_ServerProcessors{
    param($SqlHostProperties)
    $SrvCPUs = $SqlHostProperties | where {$_.name -eq "Processors"} | select value
    return $SrvCPUs.Value
}

Write-Host "SQL Server Inventory Powershell Script"
Write-Host "======================================"
$today = Get-Date -f "yyMMdd"
$CsvFile = "C:\temp\SqlServer_Inventory_$today.csv"
Clear-Content -path $CsvFile -Force
$CsvHeader = "ServerName,OS,SqlServerVersion,SqlServerEdition,SqlServicePack,Cores,RAM"
Add-Content $CsvFile $CsvHeader
$SqlSrvListFile = "sqlsrv_list.csv"
$SqlSrvList = [object]$SqlSrvList = Import-CSV $SqlSrvListFile
foreach ($SqlServer in $SqlSrvList.ServerName) {
    Write-Host "Working on $SqlServer"
    $SqlHost = New-Object -type Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SqlServer
    if ($SqlHost.ComputerNamePhysicalNetBIOS -ne $null) {
        $SqlHostProperties = $SqlHost.Information.Properties | select Name,Value
        $ServerName = Get_ServerName($SqlHostProperties)
        $OSVersString = Get_ProperOS($SqlHost.OSVersion)
        $SqlVersString = Get_ProperSQL($SqlHost.VersionString)
        $SqlEdit = $SqlHost.Edition
        $SqlSpack = $SqlHost.ProductLevel
        $Cpu = Get_ServerProcessors($SqlHostProperties)
        $Mem = Get_ServerMemory($SqlHostProperties)
        $Dump =  "$ServerName"+","+"$OSVersString"+","+"$SqlVersString"+","+"$SqlEdit"+","+"$SqlSpack"+","+"$Cpu"+","+"$Mem"
        Add-Content $CsvFile $Dump
    }
    else { Write-Host "Couldn't query $SqlServer." }
}
Write-Host "Done."

Voici une copie d'écran d'exemple du fichier CSV généré par le script :



Une version commentée du script est disponible en téléchargement. 💾

Aucun commentaire:

Enregistrer un commentaire