0

How could I search file with below text and only redirect output where there is a "Msg 208" followed on the 3rd line by a "# name not found"?

File Text:

[
DBCC upgrade_object: DEFAULT dc_1527463 upgraded successfully.
DBCC upgrade_object: Upgrading PROCEDURE tran_33
DBCC upgrade_object: There's a difference between the objectname tran_33 and the name tran_33 used in syscomments.
DBCC upgrade_object: Maybe the object was renamed using sp_rename.
Msg 208, Level 16, State 1:
Server 'DEV_RP', Procedure 'dbo.itxxn_33', Line 40:
#old_33 not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
Msg 207, Level 16, State 4:
Server 'DEV_RP', Line 3:
Invalid column name 'eryCmt'.
Msg 208, Level 16, State 1:
Server 'DEV_RP', Procedure 'dbo.yftran_33', Line 40:
bat not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
Msg 208, Level 16, State 1:
Server 'DEV_RP', Procedure 'dbo.yftran_33', Line 40:
#wbat not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
]

Example - valid and should be captured in output:

{
Msg 208, Level 16, State 1:
Server 'DEV_RP', Procedure 'dbo.itxxn_33', Line 40:
#old_33 not found. Specify owner.objectname or use sp_help to check whether the object exists 
}

Example - not valid and should not be captured in output:

{
Msg 208, Level 16, State 1:
Server 'DEV_RP', Procedure 'dbo.yftran_33', Line 40:
bat not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
}
2
  • 1. are there really blank lines between each input line, or is that something you added while editing? please edit again and copy-paste the exact input text (anonymised if necessary to obscure anything confidential but without changing the pattern/structure of the text). 2. are the open- and close- square brackets on lines by themselves? or also mangled by editing? 3. where did the curly braces come from in your sample output? there are none in the input.
    – cas
    Jul 26, 2022 at 2:09
  • Shouldn't the 3 lines starting with Msg 208 and ending with #wbat not found also be present in the output?
    – Ed Morton
    Jul 26, 2022 at 21:16

3 Answers 3

1

Assuming that the empty lines are bogus editing artifacts:

NOTE: input2.txt is an edited copy of your sample input with the extra newlines deleted.

$ sed -E -e 's/^(Msg |\])/\n\1/; s/\[/[\n/' input2.txt  |
    perl -00 -l -n -e 'print if /^Msg 208.*^#.*not found/ms'
Msg 208, Level 16, State 1:
Server 'DEV_RP', Procedure 'dbo.itxxn_33', Line 40:
#old_33 not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

Msg 208, Level 16, State 1:
Server 'DEV_RP', Procedure 'dbo.yftran_33', Line 40:
#wbat not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

This uses sed to convert the input into paragraphs at each line which begins with Msg and each instance of either [ or ]. The output of sed is then piped into perl which reads the input (-n) in paragraph mode (-00) and prints the paragraph iff it matches the regex ^Msg 208.*^#.*not found (using perl's m modifier to treat each paragraph as a multi-line string AND the s modifier to allow . to match newlines).

A "paragraph" is one or more lines of text separated by one or more newline characters.

For more info on perl command line options, see man perlrun and for more on perl regular expressions, see man perlre. For a tutorial on using regexes in perl scripts, see man perlretut.

From man perlre:

m Treat the string being matched against as multiple lines. That is, change ^ and $ from matching the start of the string's first line and the end of its last line to matching the start and end of each line within the string.

s Treat the string as single line. That is, change . to match any character whatsoever, even a newline, which normally it would not match.

Used together, as /ms, they let the . match any character whatsoever, while still allowing ^ and $ to match, respectively, just after and just before newlines within the string.

BTW, this could have been done entirely within perl but it was simpler, easier, and faster to just use sed to transform the input into something that perl's paragraph mode could handle easily.

Also BTW, if you want the curly braces in the output, change the print if ... line to print "{\n$_\n}\n" if ...


To print the opposite of what you originally asked for (i.e. print paragraphs containing a line starting with Msg that don't contain ^Msg 208.*^#.*not found) change the if ... statement to if (/^Msg /ms && ! /^Msg 208.*^#.*not found/ms). i.e. (with extra linefeed and indentation for readability):

sed -E -e 's/^(Msg |\])/\n\1/; s/\[/[\n/' input2.txt  |
    perl -00 -l -n -e 'print if (/^Msg /ms && 
                                 ! /^Msg 208.*^#.*not found/ms)'
5
  • Thank you so much .. the empty lines are bogus as I could not get it to format correctly on this page otherwise. This pretty much is what I was looking for, except I mixed up what I needed. I want to capture every Msg error aside from Msg 208 and where Msg 208 then line 3 without #object on 3rd line. Can you please help inverse the solution you provided? { Msg 208, Level 16, State 1: Server 'DEV_RP', Procedure 'dbo.yftran_33', Line 40: bat not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output). }
    – spatel
    Jul 26, 2022 at 3:35
  • change it to print if (/^Msg / && ! /^Msg 208.*^#.*not found/ms) - that will print paragraphs with ^Msg that don't contain ^Msg 208.*^#.* not found.
    – cas
    Jul 26, 2022 at 3:55
  • You can use the {} icon to format selected text as "code" (i.e. pre-formatted text). or put a line with three backtick characters (```) before AND after your pre-formatted text. See the site's formatting help for details. I've edited your question to show how.
    – cas
    Jul 26, 2022 at 3:57
  • Thank you .. this has been very helpful.
    – spatel
    Jul 26, 2022 at 4:55
  • @spatel If one of the answers here solved your issue, please take a moment and accept it by clicking on the checkmark on the left. That is the best way to express your thanks on the Stack Exchange sites. Also, if you want the opposite of what your question is asking for (print unless it matches Msg 208 and old_33 and not print if it matches), please edit your question to make that clear!
    – terdon
    Jul 26, 2022 at 9:55
0

Using any awk in any shell on every Unix box:

$ cat tst.awk
/^#[^ ]+ not found/ && (p2 ~ /^Msg 208,/) {
    print "{" ORS p2 ORS p1 ORS $0 ORS "}"
}
{ p2=p1; p1=$0 }

$ awk -f tst.awk file
{
Msg 208, Level 16, State 1:
Server 'DEV_RP', Procedure 'dbo.itxxn_33', Line 40:
#old_33 not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
}
{
Msg 208, Level 16, State 1:
Server 'DEV_RP', Procedure 'dbo.yftran_33', Line 40:
#wbat not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
}

The above is assuming that the expected output in the question is wrong and that the block including #wbat should be included in the output, and it's further assuming that each output block should be wrapped in {...} which is trivial to change if that's a fals assumption.

-1

You can extract those lines with a one-pass sed like this:

sed -n '1N;N;/Msg 208.*\n.*\n#[[:alnum:]_]* not found/p;D' 
  • -n option to suppress normal outout
  • the N;D scheme to process always two lines together; the 1N adds a third line at the beginning of the file
  • the pattern Msg 208.*\n.*\n#[[:alnum:]_]* not found describes the triplets you want to print

If you want to surround each match with braces, do it like that:

sed -n '1N;N;h;s/Msg 208.*\(\n\).*\n#[[:alnum:]_]* not found.*/{\1&\1}/p;g;D'

The substitute command adds the braces and extra newlines (captured by \(\n\) and reused as \1 because \n is not defined in the replacement). Of course, we need to save the original contents in h old space and later restore it with g.

1
  • This is a valid answer, solving the problem and explaining how it works. Whoever sees a problem in this answer, please write it in a comment instead of silently downvoting. I'm willing to improve it, but I have no idea why this answer is considered »not useful«.
    – Philippos
    Jul 28, 2022 at 6:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.