t-SQL - How to parse entry for specific text

Question

I have a database that I need to search that is full of windows event log entries.

Specifically, I need to return only a portion of the event message ('Account Name: John' in the example below). Unfortunately, this must be done with SQL, and there is not a set character that the string would start or end at and the 'John' portion could be any name in active directory. This seems a little more like a job for Regex, but I was hoping there might be an alternative that I am missing.

A user account was locked out.
Subject:
   Security ID:  SYSTEM
   Account Name:  WIN-R9H529RIO4Y$
   Account Domain:  WORKGROUP
   Logon ID:  0x3e7
Account That Was Locked Out:
   Security ID:  WIN-R9H529RIO4Y\John
   Account Name:  John
Additional Information:
Caller Computer Name: WIN-R9H529RIO4Y

---

Thoughts?

Answer 1

This is probably not the most efficient solution to the problem, but it does seem to work.

I've left it verbose on purpose so that it can be understood, but you could easily condense this down into a single statement if you wanted to:

declare @string varchar(max) = 
'A user account was locked out.
Subject:
   Security ID:  SYSTEM
   Account Name:  WIN-R9H529RIO4Y$
   Account Domain:  WORKGROUP
   Logon ID:  0x3e7
Account That Was Locked Out:
   Security ID:  WIN-R9H529RIO4Y\John
   Account Name:  John
Additional Information:
Caller Computer Name: WIN-R9H529RIO4Y';

declare @AccountStartIndex int = 
    len(@string) - charindex(reverse('Account Name:  '), reverse(@string));
declare @AccountEndIndex int = 
    charindex(char(13) + char(10), @string, @AccountStartIndex);

select substring(
    @string, 
    @AccountStartIndex + 2, 
    @AccountEndIndex - @AccountStartIndex - 1);

It works by finding the last occurrence of Account Name: in the string and then working out the position of the newline following it. With these two pieces of information we can substring John out.