functions/Get-DbaServerInstallDate.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
115
116
117
118
119
120
121
122
123
124
125
126
function Get-DbaServerInstallDate {
<#
.SYNOPSIS
Returns the install date of a SQL Instance and Windows Server, depending on what is passed.
  
.DESCRIPTION
By default, this command returns for each SQL Instance instance passed in:
SQL Instance install date, formatted as a string
Hosting Windows server install date, formatted as a string
  
.PARAMETER SqlInstance
The SQL Server that you're connecting to.
 
.PARAMETER SqlCredential
Credential object used to connect to the SQL Server as a different user
 
.PARAMETER Credential
Credential object used to connect to the SQL Server as a different user
 
.PARAMETER IncludeWindows
Includes the Windows Server Install date information
 
.PARAMETER Silent
Use this switch to disable any kind of verbose messages
 
.NOTES
Tags: CIM
Author: Mitchell Hamann (@SirCaptainMitch), mitchellhamann.com
  
Website: https://dbatools.io
Copyright: (C) Chrissy LeMaire, clemaire@gmail.com
License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0
 
.LINK
https://dbatools.io/Get-DbaServerInstallDate
 
.EXAMPLE
Get-DbaServerInstallDate -SqlInstance SqlBox1\Instance2
 
Returns an object with SQL Instance Install date as a string and the Windows install date as string.
 
.EXAMPLE
Get-DbaServerInstallDate -SqlInstance winserver\sqlexpress, sql2016
 
Returns an object with SQL Instance Install date as a string and the Windows install date as a string for both SQLInstances that are passed to the cmdlet.
  
.EXAMPLE
Get-DbaServerInstallDate -SqlInstance sqlserver2014a, sql2016
 
Returns an object with only the SQL Server Install date as a string.
 
.EXAMPLE
Get-DbaServerInstallDate -SqlInstance sqlserver2014a, sql2016 -IncludeWindows
 
Returns an object with the Windows Install date and the SQL install date as a string.
 
.EXAMPLE
Get-DbaRegisteredServer -SqlInstance sql2014 | Get-DbaServerInstallDate
 
Returns an object with SQL Instance install date as a string for every server listed in the Central Management Server on sql2014
  
#>

    [CmdletBinding()]
    Param (
        [parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias("ServerInstance", "SqlServer", "ComputerName")]
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]
        $SqlCredential,
        [PSCredential]
        $Credential,
        [Switch]$IncludeWindows,
        [switch]$Silent
    )
    
    process {
        foreach ($instance in $SqlInstance) {
            try {
                Write-Message -Level VeryVerbose -Message "Connecting to $instance" -Target $instance
                $server = Connect-SqlInstance -SqlInstance $instance -SqlCredential $SqlCredential
            }
            catch {
                Stop-Function -Message "Failed to process Instance $Instance" -ErrorRecord $_ -Target $instance -Continue
            }
            
            if ($server.VersionMajor -ge 9) {
                Write-Message -Level Verbose -Message "Getting Install Date for: $instance"
                $sql = "SELECT create_date FROM sys.server_principals WHERE sid = 0x010100000000000512000000"
                [DbaDateTime]$sqlInstallDate = $server.Query($sql, 'master', $true).create_date
                
            }
            else {
                Write-Message -Level Verbose -Message "Getting Install Date for: $instance"
                $sql = "SELECT schemadate FROM sysservers"
                [DbaDateTime]$sqlInstallDate = $server.Query($sql, 'master', $true).create_date
            }
            
            $WindowsServerName = $server.ComputerNamePhysicalNetBIOS
            
            if ($IncludeWindows) {
                try {
                    [DbaDateTime]$windowsInstallDate = (Get-DbaCmObject -ClassName win32_OperatingSystem -ComputerName $WindowsServerName -Credential $Credential -Silent).InstallDate
                }
                catch {
                    Stop-Function -Message "Failed to connect to: $WindowsServerName" -Continue -Target $instance -ErrorRecord $_
                }
            }
            
            $object = [PSCustomObject]@{
                ComputerName = $server.NetName
                InstanceName = $server.ServiceName
                SqlInstance = $server.DomainInstanceName
                SqlInstallDate = $sqlInstallDate
                WindowsInstallDate = $windowsInstallDate
            }
            
            if ($IncludeWindows) {
                Select-DefaultView -InputObject $object -Property ComputerName, InstanceName, SqlInstance, SqlInstallDate, WindowsInstallDate
            }
            else {
                Select-DefaultView -InputObject $object -Property ComputerName, InstanceName, SqlInstance, SqlInstallDate
            }
            
        }
    }
}