[工具]使用LogParser分析使用者瀏覽器種類
紀錄一下步驟
問題描述
我們想要知道網站使用者的瀏覽器使用種類及分布狀況，當作日後系統升級或者調整的依據。
前置作業
1. 安裝LogParser。
2. 將IIS log搬移到測試環境。
3. 準備好要匯入的測試環境資料庫(這裡使用test)。
解決方式
1. 在資料庫中執行以下SCRIPT產生所需資料表：
[BrowserN] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[csUsername] [nvarchar](255) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[cIp] [nvarchar](255) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[csUserAgent] [nvarchar](255) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[FireFoxData](
[BrowserN] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[csUsername] [nvarchar](255) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[cIp] [nvarchar](255) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[csUserAgent] [nvarchar](255) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[IEData](
[BrowserN] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[csUsername] [nvarchar](255) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[cIp] [nvarchar](255) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[csUserAgent] [nvarchar](255) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OtherData](
[BrowserN] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[csUsername] [nvarchar](255) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[cIp] [nvarchar](255) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[csUserAgent] [nvarchar](255) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO3. 匯入IIS記錄檔將，欲匯入的IIS記錄檔存至目錄中(C:\IISLog)，以下CMD會將C:\IISLog\ex*.log 資料匯入test 資料庫中
匯入資料CMD 請依實際情況置換紅字部份
CMD 情況假設 :
a. Log檔存放位置 : C:\IISLog\
b. 匯入DB名稱 : test
c. Username : logimport
d. Password : logimport
CMD 如下 :
3. 在test資料庫中執行以下SCRIPT，將Log資料整合至相對應資料表中
INSERT INTO [dbo].[IEData]
([BrowserN],[csUsername],[cIp],[csUserAgent])
SELECT BrowserN, csUsername, cIp, csUserAgent
FROM
(
SELECT 'IE' AS BrowserN
,ISNULL([csUsername], '') AS csUsername
,ISNULL([cIp], '') AS cIp
,REPLACE( ISNULL([csUserAgent], ''), '+', ' ') AS [csUserAgent]
FROM [dbo].[IISLog]
WHERE
[csUserAgent] like '%MSIE%'
GROUP BY [csUsername] ,[cIp] ,[csUserAgent]
) AS S1
WHERE
NOT EXISTS (
SELECT [BrowserN],[csUsername],[cIp],[csUserAgent]
FROM [dbo].[IEData] AS S2
WHERE S1.[BrowserN] = S2.[BrowserN]
AND S1.[csUsername] = S2.[csUsername]
AND S1.[cIp] = S2.[cIp]
AND S1.[csUserAgent] = S2.[csUserAgent]
)
/*FireFox*/
INSERT INTO [dbo].[FireFoxData]
([BrowserN],[csUsername],[cIp],[csUserAgent])
SELECT BrowserN, csUsername, cIp, csUserAgent
FROM
(
SELECT 'FireFox' AS BrowserN
,ISNULL([csUsername], '') AS csUsername
,ISNULL([cIp], '') AS cIp
,REPLACE( ISNULL([csUserAgent], ''), '+', ' ') AS [csUserAgent]
FROM [dbo].[IISLog]
WHERE
[csUserAgent] like '%firefox%'
GROUP BY [csUsername] ,[cIp] ,[csUserAgent]
) AS S1
WHERE
NOT EXISTS (
SELECT [BrowserN],[csUsername],[cIp],[csUserAgent]
FROM [dbo].[FireFoxData] AS S2
WHERE S1.[BrowserN] = S2.[BrowserN]
AND S1.[csUsername] = S2.[csUsername]
AND S1.[cIp] = S2.[cIp]
AND S1.[csUserAgent] = S2.[csUserAgent]
)
/*Chrome*/
INSERT INTO [dbo].[ChromeData]
([BrowserN],[csUsername],[cIp],[csUserAgent])
SELECT BrowserN, csUsername, cIp, csUserAgent
FROM
(
SELECT 'Chrome' AS BrowserN
,ISNULL([csUsername], '') AS csUsername
,ISNULL([cIp], '') AS cIp
,REPLACE( ISNULL([csUserAgent], ''), '+', ' ') AS [csUserAgent]
FROM [dbo].[IISLog]
WHERE
[csUserAgent] like '%chrome%'
GROUP BY [csUsername] ,[cIp] ,[csUserAgent]
) AS S1
WHERE
NOT EXISTS (
SELECT [BrowserN],[csUsername],[cIp],[csUserAgent]
FROM [dbo].[ChromeData] AS S2
WHERE S1.[BrowserN] = S2.[BrowserN]
AND S1.[csUsername] = S2.[csUsername]
AND S1.[cIp] = S2.[cIp]
AND S1.[csUserAgent] = S2.[csUserAgent]
)
/*不屬於IE, FireFox, Chrome的連線*/
INSERT INTO [dbo].[OtherData]
([BrowserN],[csUsername],[cIp],[csUserAgent])
SELECT BrowserN, csUsername, cIp, csUserAgent
FROM
(
SELECT 'Other' AS BrowserN
,ISNULL([csUsername], '') AS csUsername
,ISNULL([cIp], '') AS cIp
,REPLACE( ISNULL([csUserAgent], ''), '+', ' ') AS [csUserAgent]
FROM [dbo].[IISLog]
WHERE
[csUserAgent] not like '%MSIE%' and [csUserAgent] not like '%chrome%' and [csUserAgent] not like '%firefox%'
GROUP BY [csUsername] ,[cIp] ,[csUserAgent]
) AS S1
WHERE
NOT EXISTS (
SELECT [BrowserN],[csUsername],[cIp],[csUserAgent]
FROM [dbo].[OtherData] AS S2
WHERE S1.[BrowserN] = S2.[BrowserN]
AND S1.[csUsername] = S2.[csUsername]
AND S1.[cIp] = S2.[cIp]
AND S1.[csUserAgent] = S2.[csUserAgent]
)
4. 在test資料庫中執行以下SCRIPT 統計已匯入User 數量：
SELECT COUNT(*)
FROM
(
SELECT csUsername, cIp
FROM dbo.ChromeData
UNION
SELECT csUsername, cIp
FROM dbo.FireFoxData
UNION
SELECT csUsername, cIp
FROM dbo.IEData
UNION
SELECT csUsername, cIp
FROM dbo.OtherData
) S1
5. 重覆2-4繼續匯入其他Log記錄，直到取樣數足夠。
結果畫面
執行下列SQL可以分析瀏覽器種類來源：
-- IE
select BrowserN as [瀏覽器種類],COUNT(1) as [總數] FROM dbo.IEData group by BrowserN
-- Chrome
union
select BrowserN as [瀏覽器種類],COUNT(1) as [總數] FROM dbo.ChromeData group by BrowserN
-- FireFox
union
select BrowserN as [瀏覽器種類],COUNT(1) as [總數] FROM dbo.FireFoxData group by BrowserN
-- Other
union
select BrowserN as [瀏覽器種類],COUNT(1) as [總數] FROM dbo.OtherData group by BrowserN
) [瀏覽器種類分析] order by [總數] desc
執行下列SQL可以分析User Agent：
select BrowserN as [瀏覽器種類],max(csUserAgent) as [UserAgent] FROM dbo.IEData group by csUserAgent, BrowserN
-- Chrome
select BrowserN as [瀏覽器種類],max(csUserAgent) as [UserAgent] FROM dbo.ChromeData group by csUserAgent, BrowserN
-- FireFox
select BrowserN as [瀏覽器種類],max(csUserAgent) as [UserAgent] FROM dbo.FireFoxData group by csUserAgent, BrowserN
-- Other
select BrowserN as [瀏覽器種類],max(csUserAgent) as [UserAgent] FROM dbo.OtherData group by csUserAgent, BrowserN
參考資料