user agent - how to find out - 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

SQL Server 2000 Forums

SQL Server Development (2000)

user agent - how to find out

Author

Topic

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-03-20 : 16:12:24

Ok, here are some user agent codes from the iis logs.
If I want to determine the webbrowser (IE,NETSCAPE,FIREFOX,OPERA etc + MOBILE browsers, Robots etc... + version)
does anyone know of a good resource for this?

I have googled quite a few places, but have nor come up with anything that I found very good & reliable. Most resources say it's tricky, and give partial answers

Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.0.3705;+.NET+CLR+1.1.4322;+Media+Center+PC+3.1;+HbTools+4.7.0)
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SC/5.60/1.01/FS-Internett;+SV1;+FunWebProducts;+.NET+CLR+1.0.3705;+HbTools+4.7.2;+MSN+6.1;+MSNbMSFT;+MSNmen-us;+MSNc00;+v5m)
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+Windows+98/2000/XP;+BWCH-BWA-de-3.1)
Mozilla/4.0+(compatible;+MSIE+5.5;+Windows+98;+MSNATLAS01.CZ)
Mozilla/4.0+(compatible;+Cerberian+Drtrs+Version-3.2-Build-0)+Odalizer(65.160.238.180)
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+acc=none)
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+Sgrunt|V109|301|S-795150108|dialno;+snprtz|S27590600000070|2600#Service+Pack+2#2#5#154321|isdn;+HbTools+4.7.3)
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+IBP)
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+Sgrunt|V109|69|S-1396645010|dial;+snprtz|T16329600043257|2600#Service+Pack+2#2#5#1)
Mozilla/4.0+(compatible;+MSIE+5.0;+Windows+98;+HbTools+4.7.1)
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+{60FFA44F-B1BB-4D85-813F-CD5C0C3B03D5};+ESB{D7C17FDE-6FF4-455F-A52E-8A18BAC4E1FC})
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98;+Win+9x+4.90;+snprtz|T04797913761727)
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+MRA+4.3+(build+01218);+snprtz|dialno;+.NET+CLR+1.1.4322)
Mozilla/4.0+(compatible;+MSIE+5.0;+Windows+95;+DigExt;+Alexa+Toolbar)
Mozilla/4.7+[en]+(Win95;+I)
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+Sgrunt|V109|80|S-2010769194|dial;+snprtz|S26800200043257|2600#Service+Pack+2#2#5#1;+HbTools+4.7.1)
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+HbTools+4.7.5)
Mozilla/5.0+(Windows;+U;+Windows+NT+5.1;+en-US;+rv:1.7.8)+Gecko/20050511
Mozilla/4.0+(compatible;+MSIE+5.01;+Windows+NT;+YComp+5.0.2.6)
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0;+FunWebProducts;+.NET+CLR+1.1.4322)

BTW, I used geoip for determining the country from ipadresses, and am very happy with that database.

And a follow up question:
Is there a "standard" way of measuring unique visitors. Atm, I use the ASPSESSIONID as the base (cookie based). If no sessionid is found I use the IP instead for the distinct count.

This is all very dodgy to me

rockmoose

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-20 : 16:52:04

Well, I can't answer your question(s)...but in case you wanted to know the magnitude of the UserAgent values out there. We started capturing UserAgent details about a year ago for login monitor reporting. We've accumulated about 5700 distinct UserAgent values. We're not google but we have a fairly large client base. Mostly US but also some international visitors as well. I only say this in case you are considering building/maintaining your own association table. Doesn't seem too unreasonable.

Be One with the Optimizer
TG

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-03-21 : 08:37:03

Why are you not using a standard application to analyze your weblogs like WebTrends or something? These kinds of software have loads of different reports and will save you months of hassle trying to understand how the he** this works. They are not that expensive either... I belive your method of counting unique visitors would be quite acurate.

http://www.google.no/search?hl=no&q=webserver+log+analyze&meta=

TG: the reason you have all these different UserAgents is that all kinds of different software are adding their own parameters and spyware are especially aggressive. Just by looking at rockmooses example gives you a handful of spyware...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Kristen
Test

22859 Posts

Posted - 2006-03-21 : 13:37:55

I use:

http://www.psychedelix.com/agents.html

to help me identify the BOTs.

Then we do loads of stuff like:


UPDATE U
SET
	wscBrowserType = 'MSIE'
FROM	#TEMP_USERAGENT AS U
WHERE	
	wscBrowserType IS NULL AND
	(
		   wscName like 'Mozilla/[1-5].[0-9] (compatible; MSIE [1-6].[0-9]%'
		OR wscName like 'Mozilla/[1-5].[0-9][0-9] (compatible; MSIE [1-6].[0-9][a-z]%'
		OR wscName like 'Mozilla/[1-5].[0-9] WebTV/[0-9].[0-9] (compatible; MSIE [1-6].[0-9]%'
		OR wscName like 'Mozilla/[1-5].[0-9](compatible;MSIE [1-6].[0-9]%'
		OR wscName like 'Mozilla/4.0(compatible; MSIE 5.0%'
		OR wscName like 'Mozilla/[1-5].[0-9][0-9] \[[a-z][a-z]] (Compatible; RISC OS [0-9].[0-9][0-9]; MSIE [1-6].[0-9]%' ESCAPE '\'
	) AND
	(
		wscName NOT like '%OPERA%' AND
		wscName NOT like '%NETSCAPE%' AND
		wscName NOT like '%[ ;]Nav)%'
	)
GO

and then after a stack of them, to get the Browser type, we then go for the Browser Version:


UPDATE U
SET
	wscBrowserVer = substring(wscName, patindex('%MSIE [0-9].[0-9];%', wscName)+5, 3)
FROM	#TEMP_USERAGENT AS U
WHERE	
	wscBrowserType = 'MSIE' AND
	wscBrowserVer IS NULL AND
	(
		wscName like '%MSIE [0-9].[0-9];%'
	)
GO

and so on for the Operating system type and version. I then display "most frequently occurring" values that didn't get matched, and from those revise the script further to catch new browsers etc.

For the more faint-hearted WebTrends is probably a better solution!

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-03-21 : 21:06:00

Thank you, thank you.
The # of user agent values is now > 18000...

We do use AW stats, it gives nice pictures and is very useful.
It failed us when we wanted special / detailed stats for some things. Ok, it could imc do them, but what happened was that the weblogs had to be reloaded and recalculated for the special cases.
Much too time-consuming and coarse.

Loading the data into sql-server with low aggregation gives us lot of possibilities to take out stats any way, any time.

Thanks for the script samples Kristen, hoary work!
I will probably try to manage 80% of the cases, the rest of the useragents will drown in a statistical torrent.

rockmoose

Kristen
Test

22859 Posts

Posted - 2006-03-22 : 01:34:47

"I will probably try to manage 80% of the cases, the rest of the useragents will drown in a statistical torrent"

You are not wrong there!

I'll send you a PM so you can let me have your Email address, and I'll email you the script we've got - I'll leave you to change the column names etc. as appropriate

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-03-22 : 02:26:26

Sounds great Kristen. Appreciate it.

rockmoose

Kristen
Test

22859 Posts

Posted - 2006-03-22 : 06:00:16

Did you get my Email via SQLTeam (members page)? No reply from you as yet

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-03-22 : 06:52:44

Yes, I did receive an email on my gmail account.
And I replied to a sqlteam.com@... adress . ( 8:25 am (4 hours ago) )

? nothing ?

rockmoose