Automating Daily Checks with Powershell

At my company, I am required to produce evidence that I have checked every database backup on every server to ensure that the most recent backup is not older than it should be. Different databases have different schedules, so “older than it should be” is different depending on the day of the week. Lack of a "backup failed" email isnt good enough- we need an affirmative check. After all, the SMTP server could be down or some unlikely circumstance could prevent me from being alerted. To top it off, I am also required to affirm that I have reviewed the error logs daily, of all 70-somethng servers.

All of this takes me about 10 minutes to complete thanks to an initial investment of a few hours tinkering with PowerShell.

Using PowerShell, I produce a backup status report in html format that is easy to take a quick look at and see what's OK (Green), what's not (RED), and what is OK, but not quite perfect(yellow). Deepening upon your network and the size of your Errorlogs, generating the reports takes about 1 second per server.

We’ll dig into the specifics later to help you understand exactly what that means, but to give you an idea of what we are going for, the completed backup report looks like this:

In the same script, I grab the past 72 hours of error logs from all of my servers, filter out some mundane entries and condense them into one Excel sheet for quick review. This is not quite as pretty as the html report, but it’s fast and the data is easy to work with (i.e filter) in Excel. The excel output ends up looking a lot like your error logs, but it's one stop shopping:

If I have your attention and this sounds like something you could use, keep reading. Don't be intimated by the length of this article or the number of steps. I am going to assume you have zero PowerShell experience, like I did just a short time ago, and I have broken down the procedure into very simple steps to leave little room for confusion. While we proceed through each step of the implementation procedure, I'll explain what we are doing, how the code works and how the component parts work together.

At a high level, the solution consists of five parts:

A single table which we’ll use to hold our list of servers and information about our requirements for each. The table contains servernames, descriptions, a classification of environment (Dev, Stage, Prod), a bit indicating whether the server is on a special “ignore list”, and Thresholds for each day of the week.
A couple of stored procedures which we will call from powershell as we check each server
A text file which contains T-SQL that read the Errorlog with xp_readerrorlog and selects out interesting results. This is where we can define what we want to bring back and what we want to filter out of our Errorlogs.
A batch file which we’ll call from powershell run the T-SQL to return data from the Errorlogs of each of our servers, and output it to .csv and combine them into one spreadsheet
The Powershell script which contains the bulk of the logic. At a functional level the script does the following:
- Extracts two lists from our table- production servers and dev/staging servers.
- Extracts a third list of “ignored” servers
- Runs a select statement against each server in the production and the dev/stage list to determine the age of the backup of every database on each server.
- Compares the ages with the threshold stored in our table for each server on a particular day of the week. So if today is Monday, we’ll compare the age of the backup to the Monday threshold in our table.
- Returns a table, in HTLM format, listing all the databases on every server, the date of the most recent backup for that database, the age of that backup and the threshold. Each row is color coded based on the age of the backup- Green means the backup is newer than the threshold. Red means the database backup is older than the threshold and the server is not designated as on the “ignore list”. Yellow means the backup is older than the threshold, but the server is designated on the “ignore list”. Essentially these are servers where we have been told “don’t worry about it”, but I still want them to show up on my report.
- Calls a very simple batch file which runs a sql statement against every server listed in our table, to parse the Errorlogs and combine the results into one .csv file.

Now that you have an understanding of what we are going to accomplish, let’s dig in and talk about how we accomplish it.

If you have never used PowerShell before, there is one Prerequisite we need to get out of the way. Your PC probably has PowerShell script execution restricted- it’s the default for security reasons- but we need to enable it, so that’s our first step.

To enable PowerShell script execution:

Launch the Windows PowerShell Integrated Scripting Environment (Start> All Programs > Accessories > Windows PowerShell> Windows PowerShell ISE.

In the command pane of the PowerShell ISE, enter the command "Set-ExecutionPolicy Unrestricted".

3.) Select "YES" at the warning.

4.) Select File>Exit to quit the PowerShell ISE.

Once we have that prerequisites out of the way, we can get started.

We need a working folder to hold a few files like our scripts and our output. I used

C:\Data\Powershell\HTMLReports\working\

but you can use whatever you like. Create a directory which I'll refer to from here on as "your working directory".

Next, let’s create the table to hold our list of servers and requirements for each. Open up management studio, connect to a server and create the table in the database of your choice. The DDL is:

CREATE TABLE [dbo].[HTMLReports](
 [servername] [sysname] NOT NULL,
 [description] [varchar](150) NULL,
 [serverid] [int] IDENTITY(1,1) NOT NULL,
 [ProdDevStage] [nchar](10) NULL,
 [mondaythreshold] [int] NULL,
 [tuesdaythreshold] [int] NULL,
 [wednesdaythreshold] [int] NULL,
 [thursdaythreshold] [int] NULL,
 [fridaythreshold] [int] NULL,
 [saturdaythreshold] [int] NULL,
 [sundaythreshold] [int] NULL,
 [ignorelist] [int] NULL
) ON [PRIMARY]

I think most of the columns are self explanatory, but to leave no room for ambiguity,I’ll briefly describe each

[servername]: Name of the instance- this is the SQL Server that the backup checker will connect to. So for named instances, use servername\instance.

[description]: Description of the instance which is included on the backup report.

[serverid]: a unique identifier, because we are good DBAs

[ProdDevStage]: Indicates whether the server is Production, Staging or Development. The report separates Production from Dev or stage servers (use values "Prod", "Dev", or "Stage")

[ignorelist]: As discussed above, some servers we want to appear on our list, but they don't need to glow red if a backup is missed. Set this value to 1 if that’s the case.

Thresholds: These columns hold the threshold for the maximum acceptable age in days of a backup on that server for each day of the week. For example, maybe your databases on one server are not backed up on Sundays, so the Monday Threshold is 2 but every other day is 1.

While we have Management Studio open, we ought to go ahead and create the stored procedures. We’ll call these from PowerShell to bring back data from our table.

First, a proc to bring back a server list based on the environment, pretty simple:

CREATE PROCEDURE [dbo].[getserverlisthtml]
 -- Add the parameters for the stored procedure here
 @environment varchar(20)
AS
BEGIN
SELECT rtrim(ltrim(servername)) 
   from msdb.dbo.htmlreports where proddevstage=@environment
END
GO

Along similar lines, someone thought it would be nice to include descriptions with each instance, so we need to bring that out of our table:

CREATE PROCEDURE [dbo].[Getdeschtmlreports]
 -- Add the parameters for the stored procedure here
 @servername varchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT description 
 from msdb.dbo.htmlreports where servername=@servername
END
GO

Next, we need a procedure to return the threshold for that server depending on what day of the week it is. Basically we pass in the server name, look up what day of the week today is, and return the threshold for today:

CREATE PROCEDURE [dbo].[GetThresholdhtmlreports]
 -- Add the parameters for the stored procedure here
 @servername sysname
AS
BEGIN
declare @dayname varchar(10)
declare @threshold int
--declare @servername sysname
/**
select
 @DayName =
   CASE (DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7
    WHEN 1 THEN 'Sunday'
    WHEN 2 THEN 'Monday'
    WHEN 3 THEN 'Tuesday'
    WHEN 4 THEN 'Wednesday'
    WHEN 5 THEN 'Thursday'
    WHEN 6 THEN 'Friday'
    WHEN 0 THEN 'Saturday'
end **/
if (((DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7)= 0)
 begin
  select @threshold = saturdaythreshold 
    from htmlreports 
    where servername=@servername
 end
if (((DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7)= 1)
 begin
  select @threshold = sundaythreshold 
   from htmlreports 
   where servername=@servername
 end
if (((DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7)= 2)
 begin
  select @threshold = mondaythreshold 
   from htmlreports 
   where servername=@servername
 end
if (((DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7)= 3)
 begin
  select @threshold = Tuesdaythreshold 
   from htmlreports 
   where servername=@servername
 end
if (((DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7)= 4)
 begin
  select @threshold = Wednesdaythreshold 
   from htmlreports 
   where servername=@servername
 end
if (((DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7)= 5)
 begin
  select @threshold = Thursdaythreshold 
   from htmlreports 
   where servername=@servername
 end
if (((DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7)= 6)
 begin
  select @threshold = Fridaythreshold 
   from htmlreports 
   where servername=@servername
 end
select @threshold
return @threshold
END
GO

Once our procedures are created, we can put away Management Studio for a bit while we create the T-SQL script file and batch file we use to parse our errorlogs. In your working directory, create a text file called "parseerrorlog.txt". Paste the following into that file and save it :

SET NOCOUNT ON
GO
---------------------
-- The following block parses error log and produces interesting results
--
----------------------
CREATE TABLE #ErrorLog ( 
 LogDate DATETIME, 
 ProcessInfo NVARCHAR(255), 
 LogText NVARCHAR(MAX) 
); 
GO 
INSERT INTO #ErrorLog ( 
 [LogDate], 
 [ProcessInfo], 
 [LogText] 
) 
EXEC xp_readerrorlog
--select * from #ErrorLog 
--where ProcessInfo !='Logon' 
--and LogText not like 'Database backed up%'
select left(@@servername,20) as server,"|", left(LogDate,20),"|",[LogText] 
 from #ErrorLog 
--where ProcessInfo !='Logon' 
 where LogText not like 'Database backed up%'
 and LogText not like 'Log was backed up%'
 and LogText not like '%found 0 errors%'
 --and LogText not like 'This instance of SQL Server has been using a process ID%'
 and LogText not like 'Configuration option ''user options'' changed from 0 to 0. Run the RECONFIGURE statement to install.'
 and LogText not like 'Microsoft SQL Server 200%'
 and LogText not like '(c) %'
 and LogText not like 'All rights rese%'
 and LogText not like 'Server process ID is%'
 and LogText not like 'System Manufacturer:%'
 and LogText not like 'Authentication mode is %'
 and LogText not like 'Logging SQL Server messages in file%'
 --and LogText not like 'This instance of SQL Server last reported using a processID o%'
 and LogText not like 'Registry startup parameters:%'
 and LogText not like 'SQL Server is starting at normal priority base%'
 and LogText not like 'Detected % CPUs. This is an informational messag%'
 and LogText not like 'Using locked pages for buffer pool%'
 and LogText not like 'Using dynamic lock allocation.%'
 and LogText not like 'Node configuration: node 0: CPU mask%'
 and LogText not like 'Setting database option COMPATIBILITY_LEVEL to 100%'
 and LogText not like 'Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.'
 and LogText not like 'Server is listening on %'
 and LogText not like 'Server local connection provider is ready to accept connection on%'
 and LogText not like 'The SQL Server Network Interface library successfully registered the Service Principal Name%'
 and LogText not like 'Service Broker manager has started.'
 and LogText not like 'Starting up database%'
 and LogText not like 'CHECKDB for database % finished without errors on %'
 and LogText not like 'FILESTREAM: effective level = 0, configured level = 0%'
 and LogText not like 'AppDomain % unloaded.'
 --change the 72 below to alter timeframe of log read.
 and LogDate> DATEADD(hh, - 72, GETDATE()) 

drop table #ErrorLog 
-----END PARSEERRORLOG.TXT

We can make changes to the where clause to increase or decrease the time frame we are interested in or to include or exclude different results.

Next, create parserrorlog.bat in your working directory,and paste the following code into it. Remember to change "yourworkingdirectory" to be your actual working directory:

@echo "checking %1"

@echo off

sqlcmd -S %1 -h-1 -i "DRIVELETTER\YOURWORKINGDIRECTORY\parseerrorlog.txt" -o "DRIVELETTER\YOURWORKINGDIRECTORY\workingerrorlog.txt" -W

type workingerrorlog.txt>>ErrorlogCombined.txt

del workingerrorlog.txt

We’ll call this batch file from our Powershell script, passing in each servername in our lists. The batch file executes our T-SQL against each server and outputting to a working file. Which it then adds to a combined file.

Once all our dependencies are in place, we can create the Powershell Script. In your working directory, create a file called htmlreports.ps1 and save it. Open in notepad and paste the following code in the file. The code is heavily commented, so have a look and see how it works.

At a very minimum, REMEMBER TO CHANGE THE FIRST THREE LINES TO HAVE YOUR WORKING DIRECTORY, SERVER, and DATABASE where you created the table and procs.

#SET SOME VARIABLES

$workingdir="C:\Data\Powershell\HTMLReports"

#This should be “your working directory”

$hostserver="SERVERNAME\INSTANCE"

$hostdb="DATABASENAME"

#The NEXT BLOCK OF CODE LOADS SOME ASSEMBLIES REQUIRED BY POWERSHELL

[System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null

$ErrorActionPreference = "continue"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")

{

throw "SQL Server Provider is not installed."

}

else

{

$item = Get-ItemProperty $sqlpsreg

$sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)

}

Push-Location -ea 0

cd $sqlpsPath -ea 0

Add-PSSnapin SqlServerCmdletSnapin100 -ea 0

Add-PSSnapin SqlServerProviderSnapin100 -ea 0

Update-TypeData -PrependPath SQLProvider.Types.ps1xml -ea 0

update-FormatData -prependpath SQLProvider.Format.ps1xml -ea 0

Pop-Location -ea 0

#THE FOLLOWING FUNCTION IS USED TO TRIM LEADING AND TRAILING SPACES FROM

#OUR SERVERLISTS WHEN OUTPUT TO FILES

FUNCTION TRIMFILE ($filetotrim)

{

$content = "";

$file = $filetotrim;

$count = 0;

$lines = Get-Content -Path $file;

$percent_complete = 0;

# trim line by line

foreach($line in $lines)

{

$line = $line.TrimEnd();

$content += "$line`n" # Add a newline

$count++;

}

$content | Set-Content -Path $filetotrim;

}

#WE WANT TO KNOW IF WE FAIL TO CONNECT TO ANY OF OUR SERVERS, SO WE USE THIS VARIABLE #TO HOLD OUR ERRORS, IF WE HAVE ANY WE’LL INCLUDE THIS IN OUR REPORT

$connecterrors="<H2>ENCOUNTERED ERRORS CONNECTING TO THE FOLLOWING: </H2>"

#BUILD THE PROD SERVER LISTS AND COUNT THEM – CALL THE PROC WE CREATED

#OUTPUT IT TO A FILE

Invoke-sqlcmd -ServerInstance $hostserver -Database $hostdb -Query "exec getserverlisthtml prod"|out-file $workingdir\Prodserverlist.txt

#CLEANUP OUR FILE

$prodfile="$workingdir\Prodserverlist.txt"

trimfile $prodfile

(get-content $prodfile) | where {$_ -ne ""} | out-file $prodfile

(get-content $prodfile) | where {$_ -notlike "---*"} | out-file $prodfile

(get-content $prodfile) | where {$_ -notlike "colum*"} | out-file $prodfile

$countservers=0

#START A LOOP TO COUNT THE SERVERS- WAS USING FOR A PROGRESS INDICTOR

#BUT IT RUNS TO FAST FOR IT TO BE WORTHWHILE- this could be removed.

foreach ($instance in get-content “$workingdir\Prodserverlist.txt”)

{$countservers++}

#BUILD THE DEV/STAGE SERVER LISTS– not using the proc, just querying it.

#OUTPUT IT TO A FILE

Invoke-sqlcmd -ServerInstance $hostserver -Database $hostdb -Query "Select servername from $hostdb.dbo.htmlreports where proddevstage in ('Dev','Stage') " |out-file $workingdir\devstageserverlist.txt

#CLEANUP OUR FILE

$devfile="$workingdir\devstageserverlist.txt"

trimfile $devfile

(get-content $devfile) | where {$_ -ne ""} | out-file $devfile

(get-content $devfile) | where {$_ -notlike "---*"} | out-file $devfile

(get-content $devfile) | where {$_ -notlike "serverna*"} | out-file $devfile

#BUILD OR IGNORELIST AND CLEANUP THE FILE

Invoke-sqlcmd -ServerInstance $hostserver -Database $hostdb -Query "Select servername from $hostdb.dbo.htmlreports where ignorelist='1' " |out-file $workingdir\ignorelist.txt

$ignorelist="$workingdir\ignorelist.txt"

trimfile $ignorelist

(get-content $ignorelist) | where {$_ -ne ""} | out-file $ignorelist

(get-content $ignorelist) | where {$_ -notlike "---*"} | out-file $ignorelist

(get-content $ignorelist) | where {$_ -notlike "serverna*"} | out-file $ignorelist

$databasestoskip=get-content “$workingdir\ignorelist.txt”

#BUILD A FUNCTION TO CHECK IF OUR SERVER IS IN THE IGNORELIST

function ignorecheck

{

#$instancecheck="["+$instance+"]"

$instancecheck=$instance

#"$instancecheck is $$instancecheck"

if ($databasestoskip -contains $instancecheck)

{#"$instancecheck is on ignorelist"

#"databases to skip is $databasestoskip"

$ignorevalue="1"

}

else

{#"$instance is NOT on ignorelist"

#"databases to skip is $databasestoskip"

$ignorevalue="0"

}

return $ignorevalue

}

#END OF THAT FUNCTION

#COUNTING THE DEV SERVERS, WE’RE NOT USING THIS ANYMORE, COULD BE REMOVED

foreach ($instance in get-content “$workingdir\devstageserverlist.txt”)

{$countservers++}

#"there are $countservers servers"

#$results=""

# START A LOOP- LOOP THROUGH EACH PROD SERVER

foreach ($instance in get-content “$workingdir\prodserverlist.txt”)

{

#CHECK IF IT IS ON THE INORELIST

$ignorevalue=ignorecheck

#GET THE THRESHOLD FOR TODAY’s DAY OF WEEK FOR THIS SERVER- CALLING OUR PROC

$threshold=Invoke-sqlcmd -ServerInstance $hostserver -Database $hostdb -Query "exec GetThresholdhtmlreports '$instance' "

$thresholdvalue=$threshold[0]|out-string

$desc=Invoke-sqlcmd -ServerInstance $hostserver -Database $hostdb -Query "exec Getdeschtmlreports '$instance' "

$desc=$desc[0]

#FOLLOWING IS OUR MAIN QUERY- BRING BACK THE BACKUPAGES AND CLASSIFY AS OK

#OR NOT OK,ETC

$results5=$results5+(Invoke-sqlcmd -ServerInstance $instance -Database master -Query "SELECT

@@servername as Hostname,

'$desc' as Description,

T1.Name as DatabaseName,

COALESCE(Convert(varchar(30), MAX(T2.backup_finish_date), 120),'Not Yet Taken') as LastBackUpTaken,

datediff(day,MAX(T2.backup_finish_date),getdate()) as BackupAgeDays,

$thresholdvalue as BackupThresholddays,

BackupStatus=

CASE

WHEN T1.Name ='tempdb' THEN 'Check OK- Tempdb can not be backed up'

WHEN T1.Name in (Select name from sys.sysdatabases where status ='66568') THEN 'Check OK- This is a snapshot whic can not be backed up'

WHEN (datediff(day,MAX(T2.backup_finish_date),getdate())) <= $thresholdvalue THEN 'Check OK '

WHEN ($ignorevalue = 1 ) AND (datediff(day,MAX(T2.backup_finish_date),getdate())) IS NULL THEN 'Not backed up but server is on ignorelist'

WHEN ($ignorevalue = 1 ) AND (datediff(day,MAX(T2.backup_finish_date),getdate())) > $thresholdvalue THEN'Backed up is old but server is on ignorelist'

WHEN (datediff(day,MAX(T2.backup_finish_date),getdate())) IS NULL THEN 'DBA REVIEW'

WHEN (datediff(day,MAX(T2.backup_finish_date),getdate())) > $thresholdvalue THEN 'DBA REVIEW'

ELSE 'unexpected result'

END

FROM sys.sysdatabases T1 LEFT OUTER JOIN $hostdb.dbo.backupset T2

ON T2.database_name = T1.name

GROUP BY T1.Name

ORDER BY T1.Name

" -ErrorVariable Err -ErrorAction SilentlyContinue

)

if($err)

{

#THIS BLOCK RUNS IF COULDNT CONNECT TO A SERVER TO RETURN ERRORS

write-host "FAILED TO CONNECT TO INSTANCE $INSTANCE"

$connecterrors=$connecterrors+"<h3> FAILED TO CONNECT TO INSTANCE: $INSTANCE Is it online?</h3>"

$connecterrors.tostring() |out-file $workingdir\BackupReport.html -append

}

#REPEAT OUR SAME LOOP, BUT FOR DEV AND STAGE SERVERS

foreach ($instance in get-content “$workingdir\devstageserverlist.txt”)

{

$ignorevalue=ignorecheck

$threshold=Invoke-sqlcmd -ServerInstance $hostserver -Database $hostdb -Query "exec GetThresholdhtmlreports '$instance' "

$thresholdvalue=$threshold[0]|out-string

$desc=Invoke-sqlcmd -ServerInstance $hostserver -Database $hostdb -Query "exec Getdeschtmlreports '$instance' "

$desc=$desc[0]

$results6=$results6+(Invoke-sqlcmd -ServerInstance $instance -Database master -Query "SELECT

@@servername as Hostname,

'$desc' as Description,

T1.Name as DatabaseName,

COALESCE(Convert(varchar(30), MAX(T2.backup_finish_date), 120),'Not Yet Taken') as LastBackUpTaken,

datediff(day,MAX(T2.backup_finish_date),getdate()) as BackupAgeDays,

$thresholdvalue as BackupThresholddays,

BackupStatus=

CASE

WHEN T1.Name ='tempdb' THEN 'Check OK- Tempdb can not be backed up'

WHEN T1.Name in (Select name from sys.sysdatabases where status ='66568') THEN 'Check OK- This is a snapshot whic can not be backed up'

WHEN (datediff(day,MAX(T2.backup_finish_date),getdate())) <= $thresholdvalue THEN 'Check OK '

WHEN ($ignorevalue = 1 ) AND (datediff(day,MAX(T2.backup_finish_date),getdate())) IS NULL THEN 'Not backed up but server is on ignorelist'

WHEN ($ignorevalue = 1 ) AND (datediff(day,MAX(T2.backup_finish_date),getdate())) > $thresholdvalue THEN'Backed up is old but server is on ignorelist'

WHEN (datediff(day,MAX(T2.backup_finish_date),getdate())) IS NULL THEN 'DBA REVIEW'

WHEN (datediff(day,MAX(T2.backup_finish_date),getdate())) > $thresholdvalue THEN 'DBA REVIEW'

ELSE 'unexpected result'

END

FROM sys.sysdatabases T1 LEFT OUTER JOIN $hostdb.dbo.backupset T2

ON T2.database_name = T1.name

GROUP BY T1.Name

ORDER BY T1.Name

" -ErrorVariable Err -ErrorAction SilentlyContinue

)

if($err)

{

#THIS BLOCK RUNS IF COULDNT CONNECT TO A SERVER

write-host "FAILED TO CONNECT TO INSTANCE $INSTANCE"

$connecterrors=$connecterrors+"<h3> FAILED TO CONNECT TO INSTANCE: $INSTANCE Is it online?</h3>"

$connecterrors.tostring() |out-file $workingdir\BackupReport.html -append

}

}

## Try to get errorlogs

THIS BLOCK CALLS THE BATCH FILE TO GET THE ERRORLOGS FOR EACH SERVER

foreach ($instance in get-content “$workingdir\prodserverlist.txt”)

{

./parseerrorlog.bat $instance

}

foreach ($instance in get-content “$workingdir\devstageserverlist.txt”)

{

./parseerrorlog.bat $instance

}

#SET PARAMETER FOR HEADER OF HTML FILES

$a = "<style>"

$a = $a + "BODY{background-color:peachpuff;}"

$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"

$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"

$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}"

$a = $a + "</style>"

#OUTPUT BACKUP REPORTS TO HTML, AND COLOR CODE BASED ON VALUES

$results5 |convertto-html -Title "Backup Status" -Head $a -Body "<H2> Prod Backup Status </H2>" -Property Hostname, Description, Databasename, LastBackupTaken, BackupAgeDays, BackupThreshold,BackupThresholddays,BackupStatus|

foreach {if($_ -like "*Check OK*"){$_ -replace "<tr>", "<tr bgcolor=lightgreen>"}elseif($_ -like "*<td>DBA REVIEW</td>*"){$_ -replace "<tr>", "<tr bgcolor=Red>"}else{$_}} |out-file $workingdir\BackupReport.html -append

$results6 |convertto-html -Title "Backup Status" -Head $a -Body "<H2>Dev and Stage Backup Status </H2>" -Property Hostname, Description, Databasename, LastBackupTaken, BackupAgeDays, BackupThreshold,BackupThresholddays,BackupStatus|

import-csv $workingdir\errorlogcombined.txt -delimiter "|" |export-csv $workingdir\Errorlogs-past72hrs.csv

#remove-item $workingdir\ignorelist.txt

remove-item $workingdir\prodserverlist.txt

remove-item $workingdir\errorlogcombined.txt

"Backup Status" -Head $a -Body "<H2> ERRORSLIST </H2>"|out-file $workingdir\errors.html

#CREATE A FUNCTION TO RENAME THE FILES BUT ADDING A DATESTAMP

FUNCTION renamewithdatestamp ($filename)

{

# Check the file exists

if (-not(Test-Path $workingdir\$fileName)) {break}

# Display the original name

#"Original filename: $fileName"

$fileObj = get-item $fileName

# Get the date

$DateStamp = get-date -uformat "%Y-%m-%d"

$extOnly = $fileObj.extension

$nameOnly = $fileObj.Name.Replace( $fileObj.Extension,'')

#if the name already exists with today's date, delete it

if (Test-Path "$workingdir\$nameOnly-$DateStamp$extOnly")

{

Remove-Item "$workingdir\$nameOnly-$DateStamp$extOnly" -Force

}

#"Display the filename: $workingdir\$nameOnly-$DateStamp$extOnly"

if ($extOnly.length -eq 0) {

$nameOnly = $fileObj.Name

rename-item "$fileObj" "$nameOnly-$DateStamp"

}

else {

$nameOnly = $fileObj.Name.Replace( $fileObj.Extension,'')

rename-item "$fileName" "$nameOnly-$DateStamp$extOnly"

}

#END THAT FUNCTION

#RENAME THE FILES

renamewithdatestamp backupreport.html

renamewithdatestamp Errorlogs-past72hrs.csv

Once you save it, you need only to right click and "run with powershell". Your HTML report of database backups and .csv errorlog report will be created in the working directory.

That's it... populate the table with your server list, set some thresholds, then right click the powershell script and select "run with powershell" to generate the reports.

Automating Daily Checks with Powershell

Rate

Share

Categories

Share

Rate

Automating Daily Checks with Powershell

Rate

Share

Categories

Share

Rate

Related content

Perform Routine Server Checks Using PowerShell

The PoSh DBA - Reading and Filtering Errors

Working with SQL Agent Durations

Disk Space Monitoring and Early Warning with PowerShell

Moving Indexes with Powershell and SMO