functions/Save-DbaDiagnosticQueryScript.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
function Save-DbaDiagnosticQueryScript {
  <#
.SYNOPSIS
Save-DbaDiagnosticQueryScript downloads the most recent version of all Glenn Berry DMV scripts
 
.DESCRIPTION
The dbatools module will have the diagnostic queries pre-installed. Use this only to update to a more recent version or specific versions.
 
This function is mainly used by Invoke-DbaDiagnosticQuery, but can also be used independently to download the Glenn Berry DMV scripts.
 
Use this function to pre-download the scripts from a device with an Internet connection.
  
The function Invoke-DbaDiagnosticQuery will try to download these scripts automatically, but it obviously needs an internet connection to do that.
 
.PARAMETER Path
Specifies the path to the output
  
.PARAMETER Silent
Use this switch to disable any kind of verbose messages
 
.NOTES
Author: André Kamman (@AndreKamman), http://clouddba.io
Tags: Diagnostic, DMV, Troubleshooting
 
Website: https://dbatools.io
Copyright: (C) Chrissy LeMaire, clemaire@gmail.com
License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0
 
.EXAMPLE
Save-DbaDiagnosticQueryScript -Path c:\temp
 
Downloads the most recent version of all Glenn Berry DMV scripts to the specified location.
If Path is not specified, the "My Documents" location will be used.
 
#>

  [CmdletBinding()]
  param (
    [System.IO.FileInfo]$Path = [Environment]::GetFolderPath("mydocuments"),
    [Switch]$Silent
  )
    
  if (-not (Test-Path $Path)) {
    Stop-Function -Message "Path does not exist or access denied" -Target $path
    return
  }
    
  Add-Type -AssemblyName System.Web
    
  Write-Message -Level Output -Message "Downloading SQL Server Diagnostic Query scripts"
    
  $glenberryrss = "http://www.sqlskills.com/blogs/glenn/feed/"
  $glenberrysql = @()
    
  Write-Message -Level Output -Message "Downloading $glenberryrss"
    
  try {
    $rss = Invoke-WebRequest -uri $glenberryrss -UseBasicParsing -ErrorAction Stop
  }
  catch {
    Stop-Function -Message "Invoke-WebRequest failed: $_" -Target $rss -InnerErrorRecord $_
    return
  }
    
  foreach ($link in $rss.Links.outerHTML) {
    if ($link -Match "https:\/\/dl.dropboxusercontent*(.+)\/SQL(.+)\.sql") {
      $URL = $matches[0]
            
      if ([System.Web.HttpUtility]::UrlDecode($URL) -Match "SQL Server (.+) Diagnostic") {
        $SQLVersion = $matches[1].Replace(" ", "")
      }
            
      if ([System.Web.HttpUtility]::UrlDecode($URL) -Match "\((.+) (.+)\)") {
        $FileYear = "{0}" -f $matches[2]
        [int]$MonthNr = [CultureInfo]::InvariantCulture.DateTimeFormat.MonthNames.IndexOf($matches[1]) + 1
        $FileMonth = "{0:00}" -f $MonthNr
      }
            
      $glenberrysql += [pscustomobject]@{
        URL         = $URL
        SQLVersion  = $SQLVersion
        FileYear    = $FileYear
        FileMonth   = $FileMonth
        FileVersion = 0
      }
    }
  }
    
  foreach ($group in $glenberrysql | Group-Object FileYear) {
    $maxmonth = "{0:00}" -f ($group.Group.FileMonth | Measure-Object -Maximum).Maximum
    foreach ($item in $glenberrysql | Where-Object FileYear -eq $group.Name) {
      if ($item.FileMonth -eq "00") {
        $item.FileMonth = $maxmonth
      }
    }
  }
    
  foreach ($item in $glenberrysql) {
    $item.FileVersion = "$($item.FileYear)$($item.FileMonth)"
  }
    
  foreach ($item in $glenberrysql | Sort-Object FileVersion -Descending | Where-Object FileVersion -eq ($glenberrysql.FileVersion | Measure-Object -Maximum).Maximum) {
    $filename = "{0}\SQLServerDiagnosticQueries_{1}_{2}.sql" -f $Path, $item.SQLVersion, $item.FileVersion
    Write-Message -Level Output -Message "Downloading $($item.URL) to $filename"
        
    try {
      Invoke-WebRequest -Uri $item.URL -OutFile $filename -ErrorAction Stop
    }
    catch {
      Stop-Function -Message "Invoke-WebRequest failed: $_" -Target $filename -InnerErrorRecord $_
      return
    }
  }
}