functions/Get-DbrDetailedInfo.ps1

Function Get-DbrDetailedInfo
{
<#
.SYNOPSIS
Gets all of the information in the dbareports database about the estate

.DESCRIPTION


.PARAMETER ToScreen
Outputs results to screen default parameter


.PARAMETER


.PARAMETER
    

.NOTES
dbareports PowerShell module (https://dbareports.io, SQLDBAWithABeard.com)
Copyright (C) 2016 Rob Sewell

This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>.

.LINK
https://dbareports.io/functions/Get-DbrDetailedInfo

.EXAMPLE
Get-DbrDetailedInfo
Copies all policies and conditions from sqlserver2014a to sqlcluster, using Windows credentials.

.EXAMPLE
Get-DbrDetailedInfo -WhatIf
Shows what would happen if the command were executed.
    
.EXAMPLE
Get-DbrDetailedInfo -Policy 'xp_cmdshell must be disabled'
#>

    [CmdletBinding()]
    Param (
        [parameter(Mandatory = $false)]
        [switch]$ToScreen,
        [parameter(Mandatory = $false)]
        [string]$Filepath,
        [parameter(Mandatory = $false)]
        [switch]$Quiet
    )
    
    
    DynamicParam { return Get-ParamSqlServerInventory }
    
    BEGIN
    {
        $ToScreen = $True
        
        Get-Config
        $SqlServer = $script:SqlServer
        $InstallDatabase = $script:InstallDatabase
        $SqlCredential = $script:SqlCredential
        
        if ($SqlServer.length -eq 0)
        {
            throw "No config file found. Have you installed dbareports? Please run Install-DbaReports or Install-DbaReportsClient"
        }
        
        $sourceserver = Connect-SqlServer -SqlServer $sqlserver -SqlCredential $SqlCredential
        $source = $sourceserver.DomainInstanceName
        
        $InstanceName = $psboundparameters.Instance
        $ServerName = $psboundparameters.ServerName
    }
    
    PROCESS
    {
        $NumberofServerSQL = "/* Number of Servers */
                SELECT COUNT(ServerName) as Servers
                ,Environment
                ,Location
                 FROM $installdatabase.dbo.InstanceList il
                 GROUP BY Location, Environment"

        
        $SrvsDBEnvLocSQL = " /*Number of Servers, Number of Databases, Environment and Location */
                     SELECT COUNT(DISTINCT il.ServerName) as 'number of servers'
                    ,COUNT(d.Name) as 'number of databases'
                    ,il.Environment
                    ,il.Location
                     FROM $installdatabase.dbo.InstanceList il
                     JOIN $installdatabase.info.Databases d
                     ON il.InstanceID = d.InstanceID
                     GROUP BY Location,Environment"

        
        $DatabaseSizeSQL = " /*Size, Number of Servers, Number of Databases*/
                         SELECT IL.Environment, COUNT(DISTINCT il.ServerName) AS 'number of servers'
                        ,COUNT(d.Name) AS 'number of databases'
                        ,CAST((SUM(d.SizeMB) / 1024) AS Decimal(7,2)) AS 'Size Gb'
                         FROM $installdatabase.dbo.InstanceList il
                         JOIN $installdatabase.info.Databases d
                         ON il.InstanceID = d.InstanceID
                          GROUP BY Location,Environment"

        
        $SQLVersionSQL = " SELECT
                                SI.SQLVersion
                                ,Environment
                                ,COUNT(DISTINCT il.ServerName) AS 'number of servers'
                                FROM $installdatabase.dbo.InstanceList il
                                JOIN $installdatabase.info.SQLInfo SI
                                ON il.ServerName = SI.ServerName
                                GROUP BY Environment,SI.SQLVersion
                                ORDER BY Environment Desc"

        
        $SQLversionEditionSQL = " SELECT
                                SI.SQLVersion
                                ,SI.Edition
                                ,SI.ServicePack
                                ,COUNT(DISTINCT il.ServerName) AS 'number of servers'
                                FROM $installdatabase.dbo.InstanceList il
                                JOIN $installdatabase.info.SQLInfo SI
                                ON il.ServerName = SI.ServerName
                                GROUP BY Environment,SI.SQLVersion,SI.Edition
                                ,SI.ServicePack
                                ORDER BY SQLVersion Desc"

        
        $TotalAgentSQL = " /* Number of Agent Jobs */
                         SELECT COUNT(DISTINCT il.ServerName) as 'number of servers'
                        ,SUM(ajs.NumberOfJobs) as 'Total Agent Jobs'
                        ,il.Environment
                        ,il.Location
                        FROM $installdatabase.dbo.InstanceList il
                        JOIN $installdatabase.info.AgentJobServer AJS
                        ON il.InstanceID = AJS.InstanceID
                        WHERE DATEDIFF( d, AJS.NumberofJobs, GETDATE() ) >300
                        GROUP BY Location,Environment"

        
        $DatabasesWithoutBackupSQL = " /* Number of databases without a full backup*/
                                     SELECT COUNT(DISTINCT il.ServerName) as 'number of servers'
                                    ,COUNT(d.Name) as 'number of databases'
                                    ,CAST((SUM(d.SizeMB) / 1024) AS Decimal(7,2)) as 'Size Gb'
                                    ,il.Environment
                                    ,il.Location
                                     FROM $installdatabase.dbo.InstanceList il
                                     JOIN $installdatabase.info.Databases d
                                     ON il.InstanceID = d.InstanceID
                                     WHERE d.LastBackupDate = '0001-01-01 00:00:00.0000000'
                                     GROUP BY Location,Environment"

        
        $DbsWithoutLogBackupSQL = " /* Number of Full databases wihtout a transaction log backup */
                                 SELECT COUNT(DISTINCT il.ServerName) as 'number of servers'
                                ,COUNT(d.Name) as 'number of databases'
                                ,CAST((SUM(d.SizeMB) / 1024) AS Decimal(7,2)) as 'Size Gb'
                                ,il.Environment
                                ,il.Location
                                 FROM $installdatabase.dbo.InstanceList il
                                 JOIN $installdatabase.info.Databases d
                                 ON il.InstanceID = d.InstanceID
                                 WHERE d.LastLogBackupDate = '0001-01-01 00:00:00.0000000'
                                 and d.RecoveryModel = 'full'
                                 GROUP BY Location,Environment"

        
        $DBsByRecoveryModelSQL = "/* Databases by Recovery Model */
                                SELECT
                                il.Environment
                                ,d.RecoveryModel
                                ,COUNT(d.Name) as 'number of databases'
                                 FROM $installdatabase.dbo.InstanceList il
                                 JOIN $installdatabase.info.Databases d
                                 ON il.InstanceID = d.InstanceID
                                 GROUP BY Environment,d.RecoveryModel"

        
        $OSVersionSQL = "SELECT
                        SOI.OperatingSystem
                        ,IL.Environment
                        ,COUNT(DISTINCT il.ServerName) as 'Number of Servers'
                         FROM $installdatabase.dbo.InstanceList il
                         JOIN $installdatabase.info.ServerOSInfo SOI
                         on IL.ServerName = SOI.ServerName
                         GROUP BY soi.OperatingSystem,Environment
                         ORDER BY soi.OperatingSystem"

        
        $NumberofServersInfo = $sourceserver.ConnectionContext.ExecuteWithResults($NumberofServerSQL).Tables
        $SrvsDBEnvLoc = $sourceserver.ConnectionContext.ExecuteWithResults($SrvsDBEnvLocSQL).Tables
        $DatabaseSizeInfo = $sourceserver.ConnectionContext.ExecuteWithResults($DatabaseSizeSQL).Tables
        $SQLVersionInfo = $sourceserver.ConnectionContext.ExecuteWithResults($SQLVersionSQL).Tables
        $SQLversionEditionInfo = $sourceserver.ConnectionContext.ExecuteWithResults($SQLversionEditionSQL).Tables
        $TotalAgentInfo = $sourceserver.ConnectionContext.ExecuteWithResults($TotalAgentSQL).Tables
        $DatabasesWithoutBackupInfo = $sourceserver.ConnectionContext.ExecuteWithResults($DatabasesWithoutBackupSQL).Tables
        $DbsWithoutLogBackupINfo = $sourceserver.ConnectionContext.ExecuteWithResults($DbsWithoutLogBackupSQL).Tables
        $DBsByRecoveryModelINfo = $sourceserver.ConnectionContext.ExecuteWithResults($DBsByRecoveryModelSQL).Tables
        $OSVersionInfo = $sourceserver.ConnectionContext.ExecuteWithResults($OSVersionSQL).Tables
        
        if ($FilePath)
        {
            If ($Quiet)
            {
                $ToScreen = $false
            }
            try
            {
                $Date = Get-Date -Format dd-MM-yyyy-HH-mm-ss
                $FileName = $FilePath + "\DBAReports_$ServerName" + "_" + $InstanceName + "_" + $Date + ".txt"
                $null = New-Item -Path $FileName -ItemType File
            }
            catch
            {
                Write-Exception "FAILED : To create file $FileName"
                break
            }
            "Information from the dbareports about the estate" | Out-File -FilePath $FileName
            "===============================================================`n" | Out-File -FilePath $FileName -Append
            $NumberofServersInfo | Format-Table -AutoSize | Out-File -FilePath $FileName -Append
            "Number of Servers and Databases by Environment and Location from dbareport `n" | Out-File -FilePath $FileName -Append
            $SrvsDBEnvLoc | Format-Table -AutoSize | Out-File -FilePath $FileName -Append
            "Total Size Information from dbareport`n " | Out-File -FilePath $FileName -Append
            $DatabaseSizeInfo | Format-Table -AutoSize | Out-File -FilePath $FileName -Append
            "SQL Version by Environment from dbareports`n" | Out-File -FilePath $FileName -Append
            $SQLVersionInfo | Format-Table -AutoSize | Out-File -FilePath $FileName -Append
            "SQL Version Edition By Environment from dbareports`n" | Out-File -FilePath $FileName -Append
            $SQLversionEditionInfo | Format-Table -AutoSize | Out-File -FilePath $FileName -Append
            "Total Agent Jobs By Environment from dbareports`n" | Out-File -FilePath $FileName -Append
            $TotalAgentInfo | Format-Table -AutoSize | Out-File -FilePath $FileName -Append
            "Databases without a Backup from dbareports`n" | Out-File -FilePath $FileName -Append
            $DatabasesWithoutBackupInfo | Format-Table -AutoSize | Out-File -FilePath $FileName -Append
            "Databases without a Log Backup from dbareports`n" | Out-File -FilePath $FileName -Append
            $DbsWithoutLogBackupINfo | Format-Table -AutoSize | Out-File -FilePath $FileName -Append
            "Databases by Recovery Model from dbareports`n" | Out-File -FilePath $FileName -Append
            $DBsByRecoveryModelINfo | Format-Table -AutoSize | Out-File -FilePath $FileName -Append
            "Operating system information from dbareports`n" | Out-File -FilePath $FileName -Append
            $OSVersionInfo | Format-Table -AutoSize | Out-File -FilePath $FileName -Append
            Write-Output "Information written to $FileName"
        }
        
        if ($ToScreen)
        {
            Write-Output "Information from the dbareports about the estate"
            Write-Output "===============================================================`n"
            Write-output $NumberofServersInfo | Format-Table -AutoSize
            Write-output "Number of Servers and Databases by Environment and Location from dbareport `n"
            Write-output $SrvsDBEnvLoc | Format-Table -AutoSize
            Write-output "Total Size Information from dbareport`n "
            Write-output $DatabaseSizeInfo | Format-Table -AutoSize
            Write-output "SQL Version by Environment from dbareports`n"
            Write-output $SQLVersionInfo | Format-Table -AutoSize
            Write-output "SQL Version Edition By Environment from dbareports`n"
            Write-output $SQLversionEditionInfo | Format-Table -AutoSize
            Write-Output "Total Agent Jobs By Environment from dbareports`n"
            Write-Output $TotalAgentInfo | Format-Table -AutoSize
            Write-Output "Databases without a Backup from dbareports`n"
            Write-Output $DatabasesWithoutBackupInfo | Format-Table -AutoSize
            Write-Output "Databases without a Log Backup from dbareports`n"
            Write-Output $DbsWithoutLogBackupINfo | Format-Table -AutoSize
            Write-Output "Databases by Recovery Model from dbareports`n"
            Write-Output $DBsByRecoveryModelINfo | Format-Table -AutoSize
            Write-Output "Operating System Information from dbareports`n"
            Write-Output $OSVersionInfo | Format-Table -AutoSize
        }
    }
    
    END
    {
        $sourceserver.ConnectionContext.Disconnect()
        
    }
}

<#

/* OS Operating System*/

SELECT
SOI.OperatingSystem
,COUNT(DISTINCT il.ServerName) as 'Number of Servers'
 FROM dbo.InstanceList il
 JOIN info.ServerOSInfo SOI
 on IL.ServerName = SOI.ServerName
 GROUP BY soi.OperatingSystem
 ORDER BY soi.OperatingSystem
#>