Extracting just the SessionID - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Extracting just the SessionID

Author

Topic

joblenis
Starting Member

29 Posts

Posted - 2007-04-26 : 13:15:11

I am trying to extract just the session id from some iislogs, and was curious the best way to go about doing this. The session ids will all be like "ASP.NET_SessionId=1wtdqn241f0ews55n5hsipib" with 42 chars in length, however the sessionid can be anywhere in the column, sometimes by itself, sometimes at the beginning and sometimes near the end. I guess the best way to do this is search for %ASP.NET_% but im not sure how to pull just the sessionid out.

does anyone know how I should approach this or where this topic has been brought up in the forums so I could get an idea. I tried searching by instr and sessionid and few others.

Thanks

SAMPLES WHAT THE DATA MIGHT LOOK LIKE
ASP.NET_SessionId=3mhnrf45wwiypi45polih5ey

ASP.NET_SessionId=ejsfrueqay4tuo45iuszsy45;+.RELYFORMSAUTH=C81CBCADBC5DEB801A3D4EBBC187A063CFCB70D678C1419BBFBE49B06CEE0039EBC85A9592882C15E47B69BD2FBE82A570B3F93011EA95B905301FC547DE67E68D3B5EB0E53CA824EC271A0B4EF8092E

.OASIS7FORMSAUTH=34720D24F742723CD786480BC252E8ABE392B2EC146FC1474501C3BC2A8126FF0539AD35BB14D0B259F366289611B1C696724932DE0C792AAD60B3867430EB8D3540F7B36991A851924F465A5E00CC2B;+ASP.NET_SessionId=3mhnrf45wwiypi45polih5ey

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-26 : 13:47:43

[code]
DECLARE @s1 varchar(500), @s2 varchar(500), @s3 varchar(500)

SELECT @s1 = 'ASP.NET_SessionId=3mhnrf45wwiypi45polih5ey',
@s2 = '.OASIS7FORMSAUTH=34720D24F742723CD786480BC252E8ABE392B2EC146FC1474501C3BC2A8126FF0539AD35BB14D0B259F366289611B1C696724932DE0C792AAD60B3867430EB8D3540F7B36991A851924F465A5E00CC2B;+ASP.NET_SessionId=3mhnrf45wwiypi45polih5ey',
@s3 = 'ASP.NET_SessionId=ejsfrueqay4tuo45iuszsy45;+.RELYFORMSAUTH=C81CBCADBC5DEB801A3D4EBBC187A063CFCB70D678C1419BBFBE49B06CEE0039EBC85A9592882C15E47B69BD2FBE82A570B3F93011EA95B905301FC547DE67E68D3B5EB0E53CA824EC271A0B4EF8092E'

SELECT SUBSTRING(@s1, PATINDEX('%ASP.NET_SessionId=%', @s1) + 18, 24)
SELECT SUBSTRING(@s2, PATINDEX('%ASP.NET_SessionId=%', @s2) + 18, 24)
SELECT SUBSTRING(@s3, PATINDEX('%ASP.NET_SessionId=%', @s3) + 18, 24)

[/code]

Tara Kizer
http://weblogs.sqlteam.com/tarad/

joblenis
Starting Member

29 Posts

Posted - 2007-04-26 : 14:35:47

I would need to do this without defining what to search for (other than 'ASP.NET_SessionID') since every line is pretty much different, those 3 were just examples. How would I do this without defining what to search for (ie: @s1/s2/s3) The column that the information is in is 'csCookie' inside the table.

Thanks

joblenis
Starting Member

29 Posts

Posted - 2007-04-26 : 14:49:54

I got it, I just took out the declarations and used

SUBSTRING(csCookie, PATINDEX('%ASP.NET_SessionId=%', csCookie) + 0, 42)

Thanks for the help :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-26 : 16:57:13

@s1,@s2,@s3 were used so that I could test.

We have to build sample data on our machines in order to come up with a solution for you.

We don't usually just come up with this stuff if we free type it in here!

Tara Kizer
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 17:00:45

Do you really need to add zero to the patindex function?

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-26 : 17:07:07

It should be "+ 18, 24)". I'm not sure why he switched it to "+ 0, 42)". There aren't 42 characters in the example session ids. I counted 24. I had assumed that the original post had a typo since 42 didn't make sense.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 17:12:04

Ah, my question were intended for joblenis.
The only reason for changing to {0, 42} would be to include the name of session variable, ASP.NET_SessionID.

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-26 : 17:20:20

Ah yes, 18+24=42, so that makes sense. Not sure why you'd want to pass that back though as you already know you are searching for that info.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources