select part of a line of text

Question

Post reply

select part of a line of text

PHXHoward

SSCrazy Eights

Points: 8389
More actions
November 9, 2010 at 10:16 am

#231219

Good morning. I have a query that reads the SQL Server log and displays successful logins. The output is like this:
Login succeeded for user 'abc'. Connection made using SQL Server authentication. [CLIENT: xxx.xxx.xxx.xxx]
It would be much better if it only listed the login name. Can I use a combination of substring and some other functions to select just what is inside the ' ' ? It seems like it should be possible but I just can't seems to grasp it this morning.
I think that this information will be useful to collect in order to track which accounts are not being used, when someone last logged in, and for auditing purposes.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

nigelrivett SSCertifiable Points: 5472 More actions · Answer 1

select substring(strtloc+1, charindex('''',str,strtloc+1) - strtloc - 1)

from

(select str, strtloc=charindex('''',str) from tbl) a

Cursors never.
DTS - only when needed and never to control.

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 2

nigelrivett (11/9/2010)
select substring(strtloc+1, charindex('''',str,strtloc+1) - strtloc - 1)
from
(select str, strtloc=charindex('''',str) from tbl) a

Thanks for responding. I'm trying to impliment this but have not successed yet.

What I think I need to do is a substring with a starting position of the first ' + 1 and an ending position of the second ' -1.

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 3

I've worked on this for hours and am completely stumped.

Thought that this would work:

declare @text varchar(250)

set @text = 'Login succeeded for user ''domain\user''. Connection made using Windows authentication. [CLIENT: xxx.xxx.xxx.xxx]'

select SUBSTRING(@text,charindex('''',@text) +1,CHARINDEX('.',@text,-2))

but instead of stopping two characters before the first period, it counts the length from the first spot then subtracts 2.

Seems that there should be a way to do this.

GSquared SSC Guru Points: 260824 More actions · Answer 4

I broke it down into pieces. See if this helps:

declare @text varchar(250)

set @text = 'Login succeeded for user ''domain\user''. Connection made using Windows authentication. [CLIENT: xxx.xxx.xxx.xxx]'

SELECT @text,

CHARINDEX('''', @text, 0),

CHARINDEX('.', @text, 0),

CHARINDEX('.', @text, 0)- CHARINDEX('''', @text, 0),

SUBSTRING(@text, CHARINDEX('''', @text, 0), CHARINDEX('.', @text, 0)- CHARINDEX('''', @text, 0)),

REPLACE(SUBSTRING(@text, CHARINDEX('''', @text, 0), CHARINDEX('.', @text, 0)- CHARINDEX('''', @text, 0)), '''', '');

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Cory E. SSCertifiable Points: 7076 More actions · Answer 5

similar result, with out the replace function

DECLARE @text VARCHAR(250)

SET @text = 'Login succeeded for user ''domain\user''. Connection made using Windows authentication. [CLIENT: xxx.xxx.xxx.xxx]'

SELECT SUBSTRING(@text, CHARINDEX('''', @text) + 1, CHARINDEX('''', @text, CHARINDEX('''', @text) + 1)-CHARINDEX('''', @text) -1)

-- Cory

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 6

Thanks both of you for helping. What I was not understanding was the part where the first charindex is subtracted from the second.

I'll definitely be saving this example to refer to until it becomes second nature.

Thanks again!!!

GSquared SSC Guru Points: 260824 More actions · Answer 7

You're welcome.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 8

Hope everyone had a nice weekend.

If I may, how can I handle lines that do not have '<username>' in them? The query has been modified to find failed logins and occasionally there is a failed login that does not list the user name. The query is looking for "Login Failed" so these comes up and it trys to substring it.

For example:

Login failed. The login is from an untrusted domain and cannot be used...

I get this:

Invalid length parameter passed to the LEFT or SUBSTRING function.

Can there be error handling in the select such as if the substring does not exist then 'No User' would be displayed?

Ron McCullough SSC Guru Points: 63877 More actions · Answer 9

Have you considered the use of the IF ELSE construct? For example

DECLARE @text VARCHAR(250)

SET @text = 'Login failed. The login is from an untrusted domain and cannot be used...'

IF CHARINDEX('''', @text)+ 1 < 2

BEGIN

SELECT 'No user'

END

ELSE

SELECT SUBSTRING(@text, CHARINDEX('''', @text) + 1,

CHARINDEX('''', @text, CHARINDEX('''', @text) + 1)-CHARINDEX('''', @text) -1)

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 10

Ron, that'll work if you're processing one row at a time.

If you want to query a set of rows of data, add a Where clause that checks that the patindex for quotes containing text is greater than 0.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Ron McCullough SSC Guru Points: 63877 More actions · Answer 11

GSquared (11/15/2010)
Ron, that'll work if you're processing one row at a time.
If you want to query a set of rows of data, add a Where clause that checks that the patindex for quotes containing text is greater than 0.

Thanks, I should pay more attention to my own signature line

If everything seems to be going well, you have obviously overlooked something.

Hope the OP follows up on your post...

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]