Thursday, 2 March 2017

T-SQL with Powershell

If you're a DBA or developer and you need to run T-SQL statements to get you results then this is for you.

Now for me I like to keep a healthy happy SQL Server so I have the best practice building up over the years of my own T-SQL and that was great but became quite hard to connect to ever growing farms and resulted in me creating reporting database, and that then mean checking it adding columns when I created new reports etc.

Nightmare, so here is another way to do it, Powershell to the rescue.
Invoke-Sqlcmd allows me to run the T-SQL against the server for example.


SELECT CONVERT(INT, ISNULL(value, value_in_use))
 AS config_value FROM sys.configurations WHERE name = N'xp_cmdshell'

If I want to run that check in Powershell it looks something like this.

#$instance = Get-Content -Path "C:\instances.txt"

#Check xp_cmdshell
foreach ($server in $instance){
try { 
    $xp_sqlcmd = (Invoke-Sqlcmd -QueryTimeout 200 -Query "SELECT CONVERT(INT, ISNULL(value, value_in_use))
     AS config_value FROM sys.configurations WHERE name = N'xp_cmdshell';" -ServerInstance $server)

write-host "My out put is" $xp_sqlcmd.config_value

 }
Catch { write-host "Neo Broke the Matrix"
        write-host $_  
       break
       }
}

Now I know that looks way longer right? but think about it that can run on 200 servers, and if you want to output to file with let's say some good vs bad values it can look like this.

$logout = "C:\Users\Administrator\Desktop\Results.csv"
#clean up log
$logout | Remove-Item -force -ErrorAction SilentlyContinue

$instance = Get-Content -Path "C:\instances.txt"

#Formating report
"Check,Finding,Server,Rating" | Add-Content $logout

#Check xp_cmdshell
foreach ($server in $instance){
try { 
    $xp_sqlcmd = (Invoke-Sqlcmd -QueryTimeout 200 -Query "SELECT CONVERT(INT, ISNULL(value, value_in_use))
     AS config_value FROM sys.configurations WHERE name = N'xp_cmdshell';" -ServerInstance $server)

$check = $xp_sqlcmd.config_value
"xp_cmdshell check should be return 0,$check,$server,High" | Add-Content $logout

    if ($xp_sqlcmd.config_value -eq "1" ) 
        {
        write-host -BackgroundColor Yellow -ForegroundColor Red "XP_CMDSHELL Is Enabled This is Not Desired On $server"
        }
        else {
             write-host -BackgroundColor Green -ForegroundColor Blue "XP_CMDSHELL Is Disabled On $server"
             }
 }
Catch { write-host "Neo Broke the Matrix"
        write-host $_  
       break
       }
}

Now I have a powerful reporting script and it can get longer better as I can add to it over time without needing a database or manually connecting to each server so in the end.

Note, if you have SQL Enterprise server you could do this my registering each server one at a time into  management studio however this is quicker and more dynamic in the long run.

No comments: