Get the usernames from IIS Cognos logs - Logparser sample 1

Logparser is the smartest tool I have ever used in parsing IIS log files and there are many interesting things I wanted to share with everyone one of the  real-world  scenario from my current project.

Let's get started with  scenario -
How do we get a username from IIS logs even when anonymous access is enabled.  In other words, IIS has enabled  with anonymous authentication, but we need to find the username who logged into our application.  

So the challenge is: based on Cognos - IIS logs to get the usernames who logged into the Cognos application(s) using the Logparser tool.  After continuous reviewing of the IIS logs, one interesting thing that surfaced was the data in the cs(cookie) field.

Below is an example of how cs(cookie) attribute looks like.

CRN_RS=G%3DNaN%3AU%3DProfessional%3Aan%3D1280%3Aao%3D717;+CRN=skin%3Dcorporate%26useAccessibilityFeatures%3Dfalse%26format%3DHTML%26timeZoneID%3DEST%26contentLocale%3Den-us%26showHiddenObjects%3Dfalse%26productLocale%3Den%26showWelcomePage%3Dtrue%26displayMode%3Dlist%26columnsPerPage%3D3%26showOptio
nSummary%3Dtrue%26linesPerPage%3D100%26listViewSeparator%3Dnone%26automaticPageRefresh%3D30%26;+cam_passport=101:d4341b12-e762-07b3-b9e0-bb074d00061c:0030457134;+cea-ssa=false;+usersessionid=AQgAAADO5LhKzuS4SgoAAABzDKXcIhrT3ZUuFAAAAIYtxvw69pWWTotWO9Z0rYKPXNBuFAAAAAmd6FikclP8xsXt5pkm8wRGCLBH;+userCap
abilities=7ded%3B6f%3Bff5f1efa%26ARQAAACGLcb8OvaVlk6LVjvWdK2Cj1zQbiJi9iUyjP1rIqfn5%2FHFt1N7Oo6U;+caf=CAFW000000f8Q0FGQTNjMDAwMDAwNmVGQUFBQUlZdHh2dzY5cFdXVG90V085WjByWUtQWE5CdTY3NXZyRDB1Z0ljVU40bm9iSmF6Mm1KZWRHb18zNDgwNjJ8MTAxOmM1OTk1ZWRhLTVkMmUtOTdkZS0xMmYzLWRiYmIwOGJkMTNmNjoxNjE0MDY1NTIyfDEwMTpkNDM
0MWIxMi1lNzYyLTA3YjMtYjllMC1iYjA3NGQwMDA2MWM6MDAzMDQ1NzEzNA__;+cc_session=s_cc:|s_conf:na|s_sch:td|s_hd:sa|s_serv:na|s_disp:na|s_set:|s_dep:na|s_dir:na|s_sms:dd|s_ct:sa|s_cs:sa|s_so:sa|e_hp:CAMID(*22default*3au*3auid*3dcognosadmin*22)|e_proot:Public*20Folders|prootid:i2296F5A9072E46DBBA704A9988D2BEE
8|e_mroot:My*20Folders|mrootid:iC490F46372FD46368A02BDBD66C403E9|e_mrootpath:CAMID(*22default*3au*3auid*3dcognosadmin*22)*2ffolder*5b*40name*3d*27My*20Folders*27*5d|e_user:Cognos*20admin|cl:en-us|dcid:i2296F5A9072E46DBBA704A9988D2BEE8|show_logon:false|uig:|ui:|write:true|eom:0|pp:0030457134;+cogbktb
sug_cr_rptstdtab=msrch


If you careful observe the attribute you will see that what I found was the username of the user who logged into Cognos exists.  I have underlined the username "cognosadmin" which I was interested in here to help you find it.  But how can I extract only cognosadmin or any other usernames from cs(cookie) through Logparser?  I fought with this almost 3 to 4 hours, working with various functions to get the usernames without any static indices on the attribute - here is the trick I came up with.

logparser "select EXTRACT_PREFIX(EXTRACT_TOKEN(cs(cookie),2,'auid*3d'),0,'*') AS test from c:\ex*.log where cs(cookie) like '%auid%'"
                      

Select all Open in new window

Once you run the above command, it will only display "cognosadmin" from cs(cookie) or what ever username is present in cs(cookie) because it is dynamic.



Download Log Parser 2.2
http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en