functions/Get-DbrAllinfo.ps1

Function Get-DbrAllInfo
{
<#
.SYNOPSIS
Gets all of the information in the dbareports database about an instance

.DESCRIPTION
Gets all of the information in the dbareports database about an instance and displays it the screen or into a text file

.PARAMETER SQLInstance
The Server\Instance name to gather information about

.PARAMETER ToScreen
Outputs results to screen. This is default parameter

.PARAMETER FilePath
An optional filepath to have a text file with the information. File will be named \DBAReports_$ServerName" + "_" + $InstanceName + "_" + $Date + ".txt"

.PARAMETER Quiet
An optional parameter that will not display the output to the screen

.PARAMETER Confirm
Will prompt for confirmation

.PARAMETER WhatIf
Writes out the actions that would be taken

.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-DbrAllInfo

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


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

    [CmdletBinding(SupportsShouldProcess = $true)]
    Param (
        [parameter(Mandatory = $True)]
        $SQLInstance,
        [parameter(Mandatory = $false)]
        [switch]$ToScreen,
        [parameter(Mandatory = $false)]
        [string]$Filepath,
        [parameter(Mandatory = $false)]
        [switch]$Quiet
    )
    
    DynamicParam
    {
        Get-Config
        if ($script:SqlServer) { return (Get-ParamSqlServerInventory -SqlServer $script:SqlServer -SqlCredential $script:SqlCredential) }
    }
    
    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 = $SQLInstance.Split('\')[1]
        $ServerName = $SQLInstance.Split('\')[0]
        if ($InstanceName -eq $Null)
        {
        $InstanceName = 'MSSQLSERVER'
        }
    }
    
    PROCESS
    {
        $InstanceListsql = "/* Instance List info */
                SELECT IL.* FROM $installdatabase.dbo.InstanceList IL
                WHERE IL.ServerName = '$servername' AND IL.InstanceName = '$InstanceName'"

        
        $OSSQL = "/*OS Info*/
                SELECT DISTINCT OS.* FROM $installdatabase.dbo.InstanceList IL
                JOIN $installdatabase.info.ServerInfo OS
                ON IL.ServerName = OS.ServerName
                WHERE IL.ServerName = '$servername'"

        
        $SQLInfoSQL = "/*SQL Info */
                SELECT SQL.* FROM $installdatabase.dbo.InstanceList IL
                JOIN $installdatabase.info.SQLInfo SQL
                ON IL.InstanceID = SQL.InstanceID
                WHERE IL.ServerName = '$servername' AND IL.InstanceName = '$InstanceName'"

        
        $DatabaseSQL = "/*Database Info*/
                SELECT DB.* FROM $installdatabase.dbo.InstanceList IL
                JOIN $installdatabase.info.Databases DB
                ON IL.InstanceID = DB.InstanceID
                WHERE IL.ServerName = '$servername' AND IL.InstanceName = '$InstanceName'"

        
        $AgentJobsServerSQL = "/*Agent Jobs Server Level*/
                SELECT AJS.* FROM $installdatabase.dbo.InstanceList IL
                JOIN $installdatabase.info.AgentJobServer AJS
                ON IL.InstanceID = AJS.InstanceID
                WHERE IL.ServerName = '$servername' AND IL.InstanceName = '$InstanceName'"

        
        $AgentJobsDetailSQL = "/*Agent Jobs Detail Level*/
                SELECT AJd.* FROM $installdatabase.dbo.InstanceList IL
                JOIN $installdatabase.info.AgentJobDetail AJD
                ON IL.InstanceID = AJD.InstanceID
                Where IL.ServerName = '$servername' AND IL.InstanceName = '$InstanceName'"

        
        $InstanceListInfo = $sourceserver.ConnectionContext.ExecuteWithResults($InstanceListsql).Tables
        $OSInfo = $sourceserver.ConnectionContext.ExecuteWithResults($OSSQL).Tables
        $SQLInfo = $sourceserver.ConnectionContext.ExecuteWithResults($SQLInfoSQL).Tables
        $DatabasesInfo = $sourceserver.ConnectionContext.ExecuteWithResults($DatabaseSQL).Tables
        $AgentJobServerInfo = $sourceserver.ConnectionContext.ExecuteWithResults($AgentJobsServerSQL).Tables
        $AgentJobDetailInfo = $sourceserver.ConnectionContext.ExecuteWithResults($AgentJobsDetailSQL).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 $ServerName\$InstanceName" | Out-File -FilePath $FileName
            "===============================================================`n" | Out-File -FilePath $FileName -Append
            "General Information`n" | Out-File -FilePath $FileName -Append
            $InstanceListInfo | Format-Table -AutoSize | Out-File -FilePath $FileName -Append
            "Operating System Information from dbareports about $ServerName\$InstanceName `n" | Out-File -FilePath $FileName -Append
            $OSInfo | Out-File -FilePath $FileName -Append
            "SQL Instance Level Information from dbareports about $ServerName\$InstanceName `n" | Out-File -FilePath $FileName -Append
            $SQLInfo | Out-File -FilePath $FileName -Append
            "Database Information from dbareports about $ServerName\$InstanceName `n" | Out-File -FilePath $FileName -Append
            $DatabasesInfo | Out-File -FilePath $FileName -Append
            "Roll up Agent Job Information from dbareports about $ServerName\$InstanceName`n" | Out-File -FilePath $FileName -Append
            $AgentJobServerInfo | Format-Table -AutoSize | Out-File -FilePath $FileName -Append
            "Agent JOb Detail Information from dbareports about $ServerName\$InstanceName `n" | Out-File -FilePath $FileName -Append
            $AgentJobDetailInfo | Out-File -FilePath $FileName -Append
            Write-Output "Information written to $FileName"
        }
        
        if ($ToScreen)
        {
            Write-Output "Information from the dbareports about $ServerName\$InstanceName"
            Write-Output "===============================================================`n"
            Write-output "General Information`n"
            Write-output $InstanceListInfo | Format-Table -AutoSize
            Write-output "Operating System Information from dbareports about $ServerName\$InstanceName `n"
            Write-output $OSInfo
            Write-output "SQL Instance Level Information from dbareports about $ServerName\$InstanceName `n"
            Write-output $SQLInfo
            Write-output "Database Information from dbareports about $ServerName\$InstanceName `n"
            Write-output $DatabasesInfo
            Write-output "Roll up Agent Job Information from dbareports about $ServerName\$InstanceName`n"
            Write-output $AgentJobServerInfo | Format-Table -AutoSize
            Write-output "Agent Job Detail Information from dbareports about $ServerName\$InstanceName `n"
            Write-output $AgentJobDetailInfo
        }
    }
    
    END
    {
        $sourceserver.ConnectionContext.Disconnect()
        
    }
}