開發API用Log Parser來讀取IIS Log

2009-11-06

想寫個API把IIS的Log匯到資料庫裡去,直接讀取Log檔再去拆文解字的匯入,也是一個方式,但微軟還有個工具Log Parser就不用那麼麻煩了,它可以直接用command line的方式去做讀取與匯入的動作,支援的匯入及匯出格式很多,下的查詢方式,可以像SQL Script一樣的便利.

最近有個需求,要去把IIS的Log匯到資料庫裡去,直接讀取Log檔再去拆文解字的匯入,也是一個方式,但微軟還有個工具Log Parser就不用那麼麻煩了,它可以直接用command line的方式去做讀取與匯入的動作,支援的匯入及匯出格式很多,下的查詢方式,可以像SQL Script一樣的便利.

Log Parser可以到微軟的網站下載,安裝完後,就會有command line的執行程式LogParser.exe,供API使用的LogParser.dll及說明文件LogParser.chm,裡面還會有一些Sample Code可以供參考.

Log Parser可以支援的格式很多,以匯入部份如下:

而匯出的部份也不少.

不只可以直接到DB,也可以匯出統計圖或是DataGrid的顯示方式.

*以上兩個圖表來源就是LogParser.chm,裡面還有更詳細的說明.

Command Line的做法就不多加著墨了,現在已經有很多參考的資料可以看.

FYI :

Log Parser (分析多種 Log 格式的超強工具)

使用 Log Parser 將 IIS LOG 轉入 SQL 2005 Express

現在要玩的是,怎麼拿LogParser.dll來開發更適合的API,其實Command Line的做法就可以滿足大部份的需求,但有時有時特殊的判斷,在command line就有難度了,比如說,我們設定的目錄是有義意的,需要使用目錄名稱去資料庫查出相對資料...等,所以這時用API就方便很多.

這次要分析的Sample IIS Log如下,這個是FTP的Log,而我們所要取得的,只是其中下載成功的部份即可.


192.168.5.99, TestUser1, 2009/11/5, 10:06:52, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 331, 0, [659035]USER, TestUser1, -,
192.168.5.99, TestUser1, 2009/11/5, 10:06:53, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 1109, 0, 0, 230, 0, [659035]PASS, -, -,
192.168.5.99, TestUser1, 2009/11/5, 10:06:57, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 250, 0, [659035]CWD, TEST, -,
192.168.5.99, TestUser1, 2009/11/5, 10:06:57, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 250, 0, [659035]CWD, Cartron, -,
192.168.5.99, TestUser1, 2009/11/5, 10:07:07, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 331, 0, [659042]USER, TestUser1, -,
192.168.5.99, TestUser1, 2009/11/5, 10:07:08, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 1000, 0, 0, 230, 0, [659042]PASS, -, -,
192.168.5.99, TestUser1, 2009/11/5, 10:07:08, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 250, 0, [659042]CWD, /TEST/Cartron, -,
192.168.5.99, TestUser1, 2009/11/5, 10:07:08, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 16, 0, 12329, 226, 0, [659042]sent, /TEST/Cartron/不想再拍黑白照的熊貓.htm, -,
192.168.5.99, TestUser1, 2009/11/5, 10:07:21, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 16, 0, 68413, 226, 0, [659042]sent, /TEST/Cartron/不想當黑手的企鵝.GIF, -,
192.168.5.99, TestUser1, 2009/11/5, 10:07:25, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 16, 0, 14848, 226, 0, [659042]sent, /TEST/Cartron/得香港腳的蜈蚣.htm, -,
192.168.5.99, TestUser1, 2009/11/5, 10:07:32, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 250, 0, [659035]CWD, /TEST/Cartron, -,
192.168.5.99, TestUser1, 2009/11/5, 10:07:32, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 16, 0, 0, 550, 5, [659035]MKD, 新資料夾, -,
192.168.10.38, TestUser2, 2009/11/5, 11:18:19, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 331, 0, [660753]USER, TestUser2, -,
192.168.10.38, TestUser2, 2009/11/5, 11:18:20, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 1031, 0, 0, 230, 0, [660753]PASS, -, -,
192.168.10.38, TestUser2, 2009/11/5, 11:20:03, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 102579, 0, 0, 421, 121, [660753]closed, -, -,
192.168.5.99, TestUser2, 2009/11/5, 11:32:28, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 331, 0, [661092]USER, TestUser2, -,
192.168.5.99, TestUser2, 2009/11/5, 11:32:29, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 1000, 0, 0, 230, 0, [661092]PASS, -, -,
192.168.5.99, TestUser2, 2009/11/5, 11:32:32, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 250, 0, [661092]CWD, .., -,
192.168.5.99, TestUser2, 2009/11/5, 11:32:32, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 250, 0, [661092]CWD, .., -,
192.168.5.99, TestUser2, 2009/11/5, 11:32:34, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 250, 0, [661092]CWD, TEST, -,
192.168.5.99, TestUser2, 2009/11/5, 11:32:36, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 250, 0, [661092]CWD, Carton, -,
192.168.5.99, TestUser2, 2009/11/5, 11:32:38, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 16, 0, 22, 226, 0, [661092]sent, /TEST/Carton/大稻埕煙火最佳拍攝位置-電視前.zip, -,
192.168.5.99, TestUser2, 2009/11/5, 11:37:06, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 331, 0, [661205]USER, TestUser2, -,
192.168.5.99, -, 2009/11/5, 11:37:06, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 47, 0, 0, 530, 1326, [661205]PASS, -, -,
192.168.5.99, TestUser2, 2009/11/5, 11:37:13, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 331, 0, [661208]USER, TestUser2, -,
192.168.5.99, TestUser2, 2009/11/5, 11:37:14, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 1000, 0, 0, 230, 0, [661208]PASS, -, -,
192.168.5.99, TestUser2, 2009/11/5, 11:37:14, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 550, 2, [661208]sent, /TEST/Carton, -,
192.168.5.99, TestUser2, 2009/11/5, 11:37:14, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 426, 2, [661208]sent, /TEST/Carton, -,
192.168.5.99, TestUser2, 2009/11/5, 11:37:14, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 250, 0, [661208]CWD, /TEST/Carton, -,
192.168.5.99, anonymous, 2009/11/5, 11:37:28, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 331, 0, [661215]USER, anonymous, -,
192.168.5.99, -, 2009/11/5, 11:37:28, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 530, 1326, [661215]PASS, IEUser@, -,
192.168.5.99, TestUser2, 2009/11/5, 11:37:28, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 250, 0, [661208]CWD, /, -,
192.168.5.99, anonymous, 2009/11/5, 11:37:33, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 331, 0, [661218]USER, anonymous, -,
192.168.5.99, -, 2009/11/5, 11:37:33, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 530, 1326, [661218]PASS, IEUser@, -,
192.168.5.99, TestUser2, 2009/11/5, 11:37:33, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 250, 0, [661208]CWD, /TEST, -,
192.168.5.99, anonymous, 2009/11/5, 11:37:51, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 331, 0, [661227]USER, anonymous, -,
192.168.5.99, -, 2009/11/5, 11:37:51, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 530, 1326, [661227]PASS, IEUser@, -,
192.168.5.99, TestUser2, 2009/11/5, 11:37:51, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 250, 0, [661208]CWD, /TEST/Carton, -,
192.168.5.99, anonymous, 2009/11/5, 11:37:58, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 331, 0, [661230]USER, anonymous, -,
192.168.5.99, -, 2009/11/5, 11:37:58, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 0, 530, 1326, [661230]PASS, IEUser@, -,
192.168.5.99, TestUser2, 2009/11/5, 11:37:58, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 22, 226, 0, [661208]sent, /TEST/Carton/跟貓沒?係的貓頭鷹.zip, -,
192.168.5.99, TestUser2, 2009/11/5, 11:38:10, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 22, 1526, 0, [661208]sent, /TEST/Carton/一生為脫皮所苦的蛇.zip, -,
192.168.5.99, TestUser2, 2009/11/5, 11:38:23, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 22, 1826, 0, [661208]sent, /TEST/Carton/隆鼻失敗的大象.zip, -,
192.168.5.99, TestUser2, 2009/11/5, 11:38:31, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 0, 0, 22, 1826, 0, [661208]sent, /TEST/Carton/鯨魚的大便.jpg, -,
192.168.5.99, TestUser2, 2009/11/5, 11:39:03, MSFTPSVC113964092, TEST-FTP-01, 192.168.3.8, 65281, 0, 0, 421, 121, [661208]closed, -, -,

接下來就直接帶到Coding的部份,要使用LogParser.dll,當然是要先把它加入參考後,才能使用.

之後我們要瞭解這次所會用到的三個主角 :

LogQueryClassClass //執行Query動作的Object

Input Format Object //匯入資料的格式 ex : COMIISIISInputContextClassClass (IIS Log格式)

Output Format Object //匯出資料的格式 ex : COMXMLOutputContextClassClass (XML格式,但這個範例不會用到)

ILogRecordset //存放的資料集


using (DataTable dt = new DataTable())
{
	try
	{
		//Query
		MSUtil.LogQueryClassClass oLogQuery = new MSUtil.LogQueryClassClass();
		//來源資料格式
		MSUtil.COMIISIISInputContextClassClass InputFormat = new MSUtil.COMIISIISInputContextClassClass();

		//查詢字串
		StringBuilder query = new StringBuilder("SELECT ");
		if (txtRecord.Text.Trim().Length > 0)
		{
			int rec = 0;
			if (int.TryParse(txtRecord.Text.Trim(), out rec))
			{
				if (rec > 0)
				{//如果有值,且為數字大於0,再依其數字取得Top n
					query.Append(" TOP ");
					query.Append(rec);
				}
			}
		}
		if (btnLocalTime.Checked)
		{
			query.Append("UserIP,Username,TO_LOCALTIME(TO_TIMESTAMP(date,time)) as LocalTime");
		}
		else
		{
			query.Append("UserIP,Username,TO_TIMESTAMP(date,time) as LocalTime");
		}

		query.Append(",ServiceInstance,HostName,ServerIP,TimeTaken,BytesSent,bytesReceived,StatusCode" +
					 ",Win32StatusCode,RequestType,Target,Parameters " +
					 "FROM ");
		query.Append(openFileDialog1.FileName);
		if (txtWhere.Text.Trim().Length > 0)
		{
			query.Append(" Where ");
			query.Append(txtWhere.Text.Trim());
		}

		//執行查詢
		MSUtil.ILogRecordset oRecordSet = oLogQuery.Execute(query.ToString(), InputFormat);

		//取得所有的欄位名稱
		for (int i = 0; i < oRecordSet.getColumnCount(); i++)
		{
			dt.Columns.Add(oRecordSet.getColumnName(i));
		}

		//取得所有的資料
		for (; !oRecordSet.atEnd(); oRecordSet.moveNext())
		{
			DataRow dr = dt.NewRow();
			for (int i = 0; i < oRecordSet.getColumnCount(); i++)
			{
				dr[oRecordSet.getColumnName(i)] = oRecordSet.getRecord().getValue(oRecordSet.getColumnName(i));
			}
			dt.Rows.Add(dr);
		}

		//關閉查詢
		oRecordSet.close();
	}
	catch (System.Runtime.InteropServices.COMException exc)
	{
		MessageBox.Show(exc.Message);
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message);
	}
	dataGridView1.DataSource = dt;
}

這段Code就是把Log讀進來,並用DataGrid去顯示,如果IIS Log裡的時間不是當地時間,還可用TO_LOCALTIME做時間轉換,看這Script,還可以用 Select Top n from 檔案路徑,這裡有一點要注意,路徑不能有中文名稱,Query有那些可以用,可以參考說明文件的Query Syntax章節.

這次Sample UI如下 :

讀進全部的Log,但這些Log不見得全部是我們要的,這次所要的只有下載成功的部份,那麼就在Where的部份,下個查詢條件StatusCode='226'

這樣就可以得到所有下載成功的記錄,當然也可以下like '226%',這部份就待各位的需求去玩~

因為LogParser.dll是需要註冊的,如果沒有註冊,是會跳出錯誤訊息,註冊的方式也很簡單,在Command Line下 : C:\LogParser>regsvr32 LogParser.dll

Log Parser : 下載

原始碼 : LogParse.zip

.Net

回首頁