CODEWITHSUNDEEP
sql-serverpowershelloutlook
Aaron Finch
Aaron Finch

Reputation: 29

Export Power-shell result with Multiple Output into Single Email

I am running this simple Powershell script to fetch SQL Server services status,drive status, db status and Job status. I know I can use Out-file but I need all the results in one email body. Below is my script and the result

    $DestInstance = "servername"
    $DestDatabase = "master"
    $smtpServer = "forwarder.mail.xerox.com"
    $supportContact = "[email protected]"


  $O1  = Get-WmiObject -Class Win32_Service -ComputerName $DestInstance | Where-Object {$_.Displayname -in "SQL Server (MSSQLSERVER)","SQL Server Agent (MSSQLSERVER)"} | Format-Table -Property Displayname, state -AutoSize

    
    $O2=
Get-WmiObject -Class Win32_logicaldisk -Filter "DriveType = '3'" | 
Select-Object -Property DeviceID, VolumeName,
@{L="Capacity";E={"{0:N2}" -f ($_.Size/1GB)}}, 
@{L='FreeSpaceGB';E={"{0:N2}" -f ($_.FreeSpace /1GB)}},
@{L='FreeSpacePerCent';E={"{0:N2}" -f (100*$_.FreeSpace /$_.size)}}|Format-table -Autosize


$O3=
Invoke-Sqlcmd -Database $DestDatabase -ServerInstance "$DestInstance" -Query "SELECT name as Database_Name, state_desc as Database_Status
FROM   sys.databases where name in ('Core01_Prod',
'EquipmentCatalogue',
'iL_Exposure_PRODUCTION',
'iL_Exposure_STAGING',
'LookUpTables',
'ReportServer',
'ReportServerTempDB'
) order by name" |Format-table -Autosize

$O4=
Invoke-Sqlcmd -Database $DestDatabase -ServerInstance "$DestInstance" -Query ";WITH CTE_MostRecentJobRun AS 
 ( 
 -- For each job get the most recent run (this will be the one where Rnk=1) 
 SELECT job_id,run_status,run_date,run_time 
 ,RANK() OVER (PARTITION BY job_id ORDER BY run_date DESC,run_time DESC) AS Rnk 
 FROM MSDB.dbo.sysjobhistory 
 WHERE step_id=0 
 ) 
SELECT  
  name  AS [Job Name]
 ,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */ 
  +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */ 
  + (run_time - (run_time/100) * 100)  /* secs */, 
  CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Last Run]
 ,CASE WHEN enabled=1 THEN 'Enabled' 
     ELSE 'Disabled' 
  END [Job Status]
FROM     CTE_MostRecentJobRun MRJR 
JOIN     MSDB.dbo.sysjobs SJ 
ON       MRJR.job_id=sj.job_id 
WHERE    Rnk=1 
AND      run_status=1 -- 
ORDER BY [Last Run] desc " | Format-table -Autosize

The Output of this scripts looks like this :

Displayname                    state  
-----------                    -----  
SQL Server (MSSQLSERVER)       Running
SQL Server Agent (MSSQLSERVER) Running



DeviceID VolumeName Capacity FreeSpaceGB FreeSpacePerCent
-------- ---------- -------- ----------- ----------------
C:       OS         80.00    16.62       20.77           
D:       Logs-D     300.01   288.04      96.01           
E:       TempDB-E   40.00    5.23        13.07           
F:       Database-F 1,000.06 206.31      20.63           
G:       SnapInfo   349.92   212.48      60.72           
H:       Backup-H   800.16   521.45      65.17           
I:       SystemDB-I 9.97     4.06        40.69           



Database_Name          Database_Status
-------------          ---------------
Core01_Prod            ONLINE         
EquipmentCatalogue     ONLINE         
iL_Exposure_PRODUCTION ONLINE         
iL_Exposure_STAGING    ONLINE         
LookUpTables           ONLINE         
ReportServer           ONLINE         
ReportServerTempDB     ONLINE         



PS C:\PS> C:\PS\Server_Info.ps1

Displayname                    state  
-----------                    -----  
SQL Server (MSSQLSERVER)       Running
SQL Server Agent (MSSQLSERVER) Running



DeviceID VolumeName Capacity FreeSpaceGB FreeSpacePerCent
-------- ---------- -------- ----------- ----------------
C:       OS         80.00    16.62       20.77           
D:       Logs-D     300.01   288.04      96.01           
E:       TempDB-E   40.00    5.23        13.07           
F:       Database-F 1,000.06 206.31      20.63           
G:       SnapInfo   349.92   212.48      60.72           
H:       Backup-H   800.16   521.45      65.17           
I:       SystemDB-I 9.97     4.06        40.69           



Database_Name          Database_Status
-------------          ---------------
Core01_Prod            ONLINE         
EquipmentCatalogue     ONLINE         
iL_Exposure_PRODUCTION ONLINE         
iL_Exposure_STAGING    ONLINE         
LookUpTables           ONLINE         
ReportServer           ONLINE         
ReportServerTempDB     ONLINE         



PS C:\PS> C:\PS\Server_Info.ps1

Displayname                    state  
-----------                    -----  
SQL Server (MSSQLSERVER)       Running
SQL Server Agent (MSSQLSERVER) Running



DeviceID VolumeName Capacity FreeSpaceGB FreeSpacePerCent
-------- ---------- -------- ----------- ----------------
C:       OS         80.00    16.62       20.77           
D:       Logs-D     300.01   288.04      96.01           
E:       TempDB-E   40.00    5.23        13.07           
F:       Database-F 1,000.06 206.31      20.63           
G:       SnapInfo   349.92   212.48      60.72           
H:       Backup-H   800.16   521.45      65.17           
I:       SystemDB-I 9.97     4.06        40.69           



Database_Name          Database_Status
-------------          ---------------
Core01_Prod            ONLINE         
EquipmentCatalogue     ONLINE         
iL_Exposure_PRODUCTION ONLINE         
iL_Exposure_STAGING    ONLINE         
LookUpTables           ONLINE         
ReportServer           ONLINE         
ReportServerTempDB     ONLINE         



Job Name                                   Last Run            Job Status
--------                                   --------            ----------
Rapport Logon History Purge - Core01_Prod  Sep 23 2021 12:30AM Enabled   
Bring iLease Databases Online              Sep 23 2021 12:20AM Enabled   
Take iLease Databases Offline              Sep 23 2021 12:00AM Enabled   
Rapport Session Timeout - Core01_Prod      Sep 23 2021 10:34AM Enabled   
Rapport Match Criteria Purge - Core01_Prod Sep 23 2021 10:30AM Enabled   
Xerox Legacy Data Update                   Sep 23 2021  2:00AM Enabled   
syspolicy_purge_history                    Sep 23 2021  2:00AM Enabled   
AgentJob_PopulateCurrentExposure           Sep 22 2021  6:00PM Enabled   
Shrink Core01_Prod Log Files               Sep 19 2021 11:30AM Enabled   
MaintenancePlan.Subplan_1                  Sep 19 2021  1:30AM Enabled

How can I email the whole output in one single email? I am unable to bring all the results in One email using Out-file

Upvotes: 0

Views: 79

Answers (1)

Aaron Finch
Aaron Finch

Reputation: 29

I resolved it by updating code as belows :

    $DestInstance = "servername"
    $DestDatabase = "master"
    $smtpServer = "x.y.z.com"
    $supportContact = "[email protected]"


  $O1  = Get-WmiObject -Class Win32_Service -ComputerName $DestInstance | Where-Object {$_.Displayname -in "SQL Server (MSSQLSERVER)","SQL Server Agent (MSSQLSERVER)"}

    
    $O2=
Get-WmiObject -Class Win32_logicaldisk -Filter "DriveType = '3'" | 
Select-Object -Property DeviceID, VolumeName,
@{L="Capacity";E={"{0:N2}" -f ($_.Size/1GB)}}, 
@{L='FreeSpaceGB';E={"{0:N2}" -f ($_.FreeSpace /1GB)}},
@{L='FreeSpacePerCent';E={"{0:N2}" -f (100*$_.FreeSpace /$_.size)}}


$O3=
Invoke-Sqlcmd -Database $DestDatabase -ServerInstance "$DestInstance" -Query "SELECT name as Database_Name, state_desc as Database_Status
FROM   sys.databases where name in ('Core01_Prod',
'EquipmentCatalogue',
'iL_Exposure_PRODUCTION',
'iL_Exposure_STAGING',
'LookUpTables',
'ReportServer',
'ReportServerTempDB'
) order by name" 

$O4=
Invoke-Sqlcmd -Database $DestDatabase -ServerInstance "$DestInstance" -Query ";WITH CTE_MostRecentJobRun AS 
 ( 
 -- For each job get the most recent run (this will be the one where Rnk=1) 
 SELECT job_id,run_status,run_date,run_time 
 ,RANK() OVER (PARTITION BY job_id ORDER BY run_date DESC,run_time DESC) AS Rnk 
 FROM MSDB.dbo.sysjobhistory 
 WHERE step_id=0 
 ) 
SELECT  
  name  AS [Job Name]
 ,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */ 
  +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */ 
  + (run_time - (run_time/100) * 100)  /* secs */, 
  CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Last Run]
 ,CASE WHEN enabled=1 THEN 'Enabled' 
     ELSE 'Disabled' 
  END [Job Status]
FROM     CTE_MostRecentJobRun MRJR 
JOIN     MSDB.dbo.sysjobs SJ 
ON       MRJR.job_id=sj.job_id 
WHERE    Rnk=1 
AND      run_status=1 -- 
ORDER BY [Last Run] desc " 





$style = "<style>BODY{font-family: Ariel, font-size: 12pt;}"
          $style = $style + "TABLE{border: 1px solid Green; border-collapse: collapse;}"
          $style = $style + "TH{border: 1px solid Red; background: #dddddd; padding: 5px;}"
          $style = $style + "TD{border: 1px solid Red; padding: 5px;}"
          $style = $style + "</style>"
          $b1 = ($O1 | Select-Object -Property Displayname, State | ConvertTo-HTML -Head $style)
          $b2 = ($O2 | Select-Object -Property DeviceID,VolumeName,Capacity,FreeSpaceGB,FreeSpacePerCent | ConvertTo-HTML -Head $style)
          $b3 = ($O3 | Select-Object -Property 'Database_Name','Database_Status' | ConvertTo-HTML -Head $style)
          $b4 = ($O4 | Select-Object -Property 'Job Name','Last Run','Job Status' | ConvertTo-HTML -Head $style)
          $Now = Get-Date -uformat "%a %d %b %Y"
#$O=($b | ConvertTo-HTML -Head $style | Out-String )
Send-MailMessage -To $supportContact -From "[email protected]" -Subject "Test Subject" -Body "$b1$b2$b3$b4" -SmtpServer "$smtpServer" -BodyAsHtml -Priority High

Upvotes: 2

Related Questions