CODEWITHSUNDEEP
powershell
FilamentUnities
FilamentUnities

Reputation: 644

Parsing only the last section of a text file

I have a process that does my SQL Server database backups via powershell, which logs to a single text file as it iterates through the databases. When we get errors I'd like to log those to a table, however native T-SQL prevents getting backup error messages in Try-Catch blocks, so I'd like to pull this information from the text file.

I'd like to only grab the messages from the most recent database. Thus my question.

Given the text sample below:

12:31:32.310 #############
12:31:32.326 # Database1 #
12:31:32.326 #############

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5901, Level 16, State 1, Server Server1, Line 1
One or more recovery units belonging to database 'Database1' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.
Msg 9002, Level 17, State 4, Server Server1, Line 1
The transaction log for database 'Database1' is full due to 'ACTIVE_TRANSACTION'. 
Msg 3013, Level 16, State 1, Server Server1, Line 1
BACKUP DATABASE is terminating abnormally.
Msg 3201, Level 16, State 2, Server Server1, Line 4
Cannot open backup device 'D:\Backups\Database1\Database1_201408260031_backup_Native_DIFF.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Server Server1, Line 4
VERIFY DATABASE is terminating abnormally.

12:31:31.435 #############
12:31:31.435 # Database2 #
12:31:31.435 #############

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
BACKUP DATABASE [Database2] TO DISK = N'D:\Backups\Database2\Database2_backup_Native_FULL.bak' WITH STATS=10,INIT,COMPRESSION; 
43 percent processed.
86 percent processed.
99 percent processed.
Processed 296 pages for database 'Database2', file 'Database2' on file 1.
100 percent processed.
Processed 2 pages for database 'Database2', file 'Database2_log' on file 1.
BACKUP DATABASE successfully processed 298 pages in 0.180 seconds (12.893 MB/sec).
The backup set on file 1 is valid.

12:31:32.310 #############
12:31:32.326 # Database3 #
12:31:32.326 #############

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5901, Level 16, State 1, Server Server1, Line 1
One or more recovery units belonging to database 'Database3' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.
Msg 9002, Level 17, State 4, Server Server1, Line 1
The transaction log for database 'Database3' is full due to 'ACTIVE_TRANSACTION'. 
Msg 3013, Level 16, State 1, Server Server1, Line 1
BACKUP DATABASE is terminating abnormally.
Msg 3201, Level 16, State 2, Server Server1, Line 4
Cannot open backup device 'D:\Backups\Database3\Database3_201408260031_backup_Native_DIFF.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Server Server1, Line 4
VERIFY DATABASE is terminating abnormally.

I would only want the following output for just Database3:

One or more recovery units belonging to database 'Database3' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.
The transaction log for database 'Database3' is full due to 'ACTIVE_TRANSACTION'. 
BACKUP DATABASE is terminating abnormally.
Cannot open backup device 'D:\Backups\Database3\Database3_201408260031_backup_Native_DIFF.bak'. Operating system error 2(The system cannot find the file specified.).
VERIFY DATABASE is terminating abnormally.

I've got this far, but need to filter out any errors above Database3.

Get-Content ("C:\SampleError.txt") | Select-String -SimpleMatch "Msg" -Context 0, 1 | %{ ($_.Context.PostContext);  }

I basically need to filter the Get-Content on only the last block (# Database3 #) but I'm having trouble finding the right way to do it (LastIndexOf?). I'd like to avoid regex for supportability reasons but am open to suggestions. Thanks for any help!

Upvotes: 0

Views: 272

Answers (2)

TheMadTechnician
TheMadTechnician

Reputation: 36297

Ok, let's do this. Find the last line that's 12:34:56.789 ############# and then Get-Content file.txt | select -skip however many lines, minus 3. I'm also grabbing the time and database name, and creating an array of custom objects to things look clean. Obviously if you don't want to do that you can skip line 2, and the ForEach on line 3.

$LinesToSkip = select-string -Path C:\temp\input.txt -Pattern "(\d{2}:\d{2}:\d{2}\.\d{3}\s+?\#+)"|select -last 1 -ExpandProperty linenumber
$Failure = GC C:\temp\input.txt | Where{$_ -Match "(\d{2}:\d{2}:\d{2}\.\d{3})\s+?\#\s+?([^#]+)\s"}|Select -Last 1 |ForEach{$Matches}
$Log = Get-Content C:\temp\input.txt | Select -skip ($LinesToSkip - 3) | Select-String -SimpleMatch "Msg" -Context 0, 1 | Select -ExpandProperty Context | Select -ExpandProperty PostContext | ForEach{[PSCustomObject][Ordered]@{'Database'=$Failure[2];'Failure Time'=$Failure[1];'Message'=$_}}
$Log|FT -AutoSize
$Log|Export-Csv c:\output.csv -NoTypeInformation

Output will go to a CSV file, and on screen will look like:

Database  Failure Time Message                                                                      
--------  ------------ -------                                                                      
Database3 12:31:32.326 One or more recovery units belonging to database 'Database3' failed to gen...
Database3 12:31:32.326 The transaction log for database 'Database3' is full due to 'ACTIVE_TRANSA...
Database3 12:31:32.326 BACKUP DATABASE is terminating abnormally.                                   
Database3 12:31:32.326 Cannot open backup device 'D:\Backups\Database3\Database3_201408260031_bac...
Database3 12:31:32.326 VERIFY DATABASE is terminating abnormally.

Upvotes: 2

TechDude
TechDude

Reputation: 111

Can't you use a RegEx to filter for the ones in "Database3"? That's what I usually do.

Here's a link with a script that does exactly what you're looking for--"...if you use the -ShowMatchedLines switch with the script, the summary will not be shown, but every line from the log which matched at least one pattern will be outputted...": http://cyber-defense.sans.org/blog/2009/06/23/powershell-script-to-search-logs-with-regular-expressions

Direct link for script zip file.

Upvotes: 0

Related Questions