# SQLSaturday#85 in Orlando is September 24, 2011

# I'll be presenting "PowerShell by Example"

# Here's a typical example we will review.

#

# Want more?  Come to my session at 11 AM.

#

# Need more info on SQL Saturday Orlando, go here.

#

# Set permissions on multiple servers for GUARDIUM Vulnerability Assessment 
# Pre-requisites:
# PowerShell V1
# SQLPSX 1.6.1
# add-pssnapin sqlserverprovidersnapin100
# add-pssnapin sqlservercmdletsnapin100

1:  $serverName = "SQL999"

   2:  $databaseName = "InventoryDB"

   3:   

   4:  $FilePath = "C:\GUARDIUM\DataSources\Output"

   5:  $OutFile = Join-Path -path $FilePath -childPath ("MSSQL_GDM_Output" + (get-date).toString('yyyyMMdd_hhmmtt') + ".txt")

   6:   

   7:  # here string contains SQL query

   8:  $qry = @"

   9:  SELECT  DISTINCT

  10:          ISNULL(A.MACH_LPAR_NM, '') + CASE a.INST_NM

  11:                                         WHEN 'DEFAULT' THEN ''

  12:                                         ELSE '\'

  13:                                       END + CASE a.INST_NM

  14:                                               WHEN 'DEFAULT' THEN ''

  15:                                               ELSE ISNULL(a.INST_NM, '')

  16:                                             END AS InstanceName

  17:  FROM    InventoryDatabase

  23:   "@

  24:   

  25:  $Servers = Get-SqlData $serverName $databaseName $qry

  26:   

  27:  Start-Transcript $OutFile

  28:   

  29:  $ScriptToExecute =  "C:\GUARDIUM\Scripts\gdmmonitor-mss.sql"

  30:   

  31:  foreach ($svr in $Servers)

  32:  {

  33:      $serverName = $svr.InstanceName

  34:      Write-Host $serverName

  35:      trap {"Oops! It failed. $_"; continue } Invoke-Sqlcmd -InputFile $ScriptToExecute  -verbose -ServerInstance $serverName

  36:  }

  37:   

  38:  Stop-Transcript

# SQLSaturday#85 in Orlando is September 24, 2011

# I'll be presenting "PowerShell by Example"

# Here's a typical example we will review.

#

# Want more?  Come to my session at 11 AM.

#

# Need more info on SQL Saturday Orlando, go here.

#

# Set permissions on multiple servers for GUARDIUM Vulnerability Assessment 
# Pre-requisites:
# PowerShell V1
# SQLPSX 1.6.1
# add-pssnapin sqlserverprovidersnapin100
# add-pssnapin sqlservercmdletsnapin100

1:  $serverName = "SQL999"

   2:  $databaseName = "InventoryDB"

   3:   

   4:  $FilePath = "C:\GUARDIUM\DataSources\Output"

   5:  $OutFile = Join-Path -path $FilePath -childPath ("MSSQL_GDM_Output" + (get-date).toString('yyyyMMdd_hhmmtt') + ".txt")

   6:   

   7:  # here string contains SQL query

   8:  $qry = @"

   9:  SELECT  DISTINCT

  10:          ISNULL(A.MACH_LPAR_NM, '') + CASE a.INST_NM

  11:                                         WHEN 'DEFAULT' THEN ''

  12:                                         ELSE '\'

  13:                                       END + CASE a.INST_NM

  14:                                               WHEN 'DEFAULT' THEN ''

  15:                                               ELSE ISNULL(a.INST_NM, '')

  16:                                             END AS InstanceName

  17:  FROM    InventoryDatabase

  23:   "@

  24:   

  25:  $Servers = Get-SqlData $serverName $databaseName $qry

  26:   

  27:  Start-Transcript $OutFile

  28:   

  29:  $ScriptToExecute =  "C:\GUARDIUM\Scripts\gdmmonitor-mss.sql"

  30:   

  31:  foreach ($svr in $Servers)

  32:  {

  33:      $serverName = $svr.InstanceName

  34:      Write-Host $serverName

  35:      trap {"Oops! It failed. $_"; continue } Invoke-Sqlcmd -InputFile $ScriptToExecute  -verbose -ServerInstance $serverName

  36:  }

  37:   

  38:  Stop-Transcript

# My quick and dirty PowerShell script to check for the oldest entry in MSDB.

# I'm using a SQLPSX 1.6 function Get-SqlData to execute queries. 

# Runs against multiple servers

   1:  $start = get-date

   2:  write-host "Start: "  $start

   3:   

   4:  $serverName = "SERVER\INSTANCE"

   5:  $databaseName = "dbINVENTORY"

   6:   

   7:  $FilePath = "C:\Output"

   8:  $OutFile = Join-Path -path $FilePath -childPath ("MSDB_MIN_Date_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".csv")

   9:   

  10:  # here string contains SQL query

  11:  $qry = @"

  12:  SELECT DISTINCT

  13:          ISNULL(A.MACH_LPAR_NM, '') + CASE INST_NM

  14:                                         WHEN 'DEFAULT' THEN ''

  15:                                         ELSE '\'

  16:                                       END + CASE INST_NM

  17:                                               WHEN 'DEFAULT' THEN ''

  18:                                               ELSE ISNULL(INST_NM, '')

  19:                                             END AS InstanceName

  20:  FROM    dbInventory A

  24:  ORDER BY InstanceName

  25:  "@

  26:   

  27:  $Servers = Get-SqlData $serverName $databaseName $qry

  28:   

  29:  $qry = @"

  30:  SELECT  @@SERVERNAME AS ServerName, MIN(backup_finish_date) OldestMsdbBackupDate FROM  msdb.dbo.backupset 

  31:  "@

  32:  $databaseName = "msdb"

  33:   

  34:  # Version inventory

  35:  @(

  36:  foreach ($svr in $Servers)

  37:  {

  38:      $serverName = $svr.InstanceName

  39:      trap {"Oops! Query failed. $_"; continue } Get-SqlData $serverName $databaseName $qry

  40:      

  41:  }

  42:  )  | export-csv -noType $OutFile

  43:   

  44:  $end = get-date    

  45:  write-host "End: "  $end