Formula to get browser type from IIS Logs

[KMX2]

I have searched the better part of the day looking for an answer - can't believe it doesn't exist, either that or I just couldn't find it.

I am looking to get the browser type from the useragent field of an IIS Log.

I have it working in SQL using the following:

MainBrowser = case substring(dbo.cf_GetSplit(csuseragent, ';',2),1,3)
when '+U' then dbo.cf_GetSplit (dbo.cf_GetSplit(dbo.cf_GetSplit(csuseragent, ';',6),'+',4),
'/',1)
when '+MS' then 'Microsoft Internet Explorer'
else 'Unknown'
end,

However when I replace substring with Mid, cf_GetSplit with Split, case with If statments and the csuseragent with the correct field name I get:"This array must be subscripted For example Array.

Thanks,
Kevin.

 

[dgillz]

Please show what the raw data looks like and which part of it you are trying to extract.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[KMX2]

Sorry made the common assumption that because I was familiar with it that everyone must be.

Raw Data (5 examples):
Mozilla/4.0+(compatible;+MSIE+4.01;+Windows+NT;+MS+Search+5.0+Robot)
Mozilla/4.0+ (compatible;MSIE+5.5;+Windows+2000+Server)
Mozilla/5.0+(X11;+U;+Linux+i686;+en-US;+rv:1.8.1.6)+Gecko/20071008+Ubuntu/7.10+(gutsy)+Firefox/2.0.0.6
Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.04506.648;+.NET+CLR+3.5.21022;+InfoPath.2)
Mozilla/5.0+(Windows;+U;+Windows+NT+5.1;+en-US;+rv:1.8.1.12)+Gecko/20080201+Firefox/2.0.0.12


The part I am trying to extract is the 'MSIE' where it exists, where there is a 'U' where the 'MSIE' should be then I want the 'Firefox' or whatever appears in that spot.

Thanks for the help.

Kevin.

 

[dgillz]

Will this always be between the 2nd and 3rd plus signs? The 2nd sample record has a semicolon but no plus sign before MSIE.

Assuming that record 3 2 is a typo and should have a plus sign, and that this is consistent, use this:

If split({YourField},"+")[3]="MSIE" then "Internet Explorer else
If split({YourField},"+")[3]="U;" then "Firefox"

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[GJParker]

It look s to me like you need to split at the first semicolon ';' and then chack the output to give you the results i.e.

If Count(Split({MyField},';')) >= 2 Then
   If Split({MyField},';')[2] Like '*MSIE*' Then
      'Internet Explorer'
   Else If Split({MyField},';')[2] Like '*U*' Then
      'Firefox'
   Else
      'Other'
Else
   'None'

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[KMX2]

Those will work great BUT the issue is there are more options available than just 'If U* then FireFox' I have found some more examples:

Mozilla/5.0+(X11;+U;+Linux+i686;+en-US;+rv:1.8.1.12)+Gecko/20080312+SeaMonkey/1.1.8
Mozilla/5.0+(Windows;+U;+Windows+NT+5.1;+en-US)+AppleWebKit/523.15+(KHTML,+like+Gecko)+Version/3.0+Safari/523.15
iTunes/7.7+(Windows;+U;+Microsoft+Windows+Vista+Professional+Service+Pack+1+(Build+6001))+DPI/96

It looks to me that 'If U* then I need to everything from the last '+' to the last '/' and display that, so in the examples above 'SeaMonkey', 'Safari', and 'DPI' would get returned.

Thanks,

Kevin.

 

[GJParker]

if there is no consistency in the layout of the strings and you can't tell which part of the string indicates the browser then unfortunately you're screwed.





Gary Parker
MIS Data Analyst
Manchester, England

 

[KMX2]

There is consistency - I think.

If after the first ';+' there is 'MS*" then 'Microsoft Internet Explorer',
if after the first ';+' there is 'U' then return everything from the last
'+' to the last '/'. And in any other case 'Unknown'


That's in English...now all I need is it converted into Crystal Syntax....

Thanks,
Kevin.

 

[GJParker]

Try This


If Count(Split({MyField},'+')) >= 2 Then
If Split({MyField},'+')[2] Like '*MSIE*' Then
'Internet Explorer'
Else If Split({MyField},';')[2] Like '*U*' Then
Split({MyField},'+')[Ubound(Split({MyField},'+')]
Else
'Other'
Else
'None'

Gary Parker
MIS Data Analyst
Manchester, England

 

[KMX2]

Thanks Gary,

That was close enough, I had to make some minor adjustments based on different data...end result:

IF Count(Split({MyField},';')) <= 2 then 'UNKNOWN TYPE'
else
If Count(Split({MyField},'+')) >= 3 Then
If Split({MyField},'+')[3] Like '*MS*' Then
'Microsoft Internet Explorer'
Else If Split({MyField},';')[2] Like '+U*' Then
SPLIT( Split({MyField},'+')[Ubound(Split({MyField},'+'))], '/')[1]
Else
'UNKNOWN TYPE'
Else
'UNKNOWN TYPE'

Thanks Again,
Kevin.