Posts Tagged With: PATINDEX

[T-SQL] 從資料記錄裡面抽出 IP


在自訂的事件紀錄裡面,由程式碼觸發的自動紀錄,原先只是當作備查用,就如同 Windows 內建的事件檢視器,資料庫欄位有 事件類別、事件代碼、事件時間、事件內容等,並沒有把 IP 當作獨立欄位拉出來,IP 會出現在事件內容的任何地方。

最近覺得系統效能不太正常,想確認是否有異常攻擊,打算依照 IP 來做排序計算,就變成一個困擾。

好在,不用管 IPv6 或是 domain name ,原先全部都有轉換成 IP 。

檢視資料紀錄後,發現 IP 大概有幾種紀錄格式存在事件內容中,有可能出現在一開始、中間或最末行:

IP=xx.xx.xx.xx
IP:xx.xx.xx.xx
IP: xx.xx.xx.xx
yy=xx.xx.xx.xx
yy:xx.xx.xx.xx

T-SQL 語法中並沒有正規運算式可用,所以組出來很麻煩,我組出來大概是這樣:

'%[:=]%[0-9]%.[0-9]%.[0-9]%.[0-9]%'
項目說明
%[:=]%先找出 yy: 或 yy= ,並包含可能有空白的項目
[0-9]%. IP 由數字開頭,1~3位數,後接小數點
[0-9]% 第四碼 IP 後方不知道還有多長
條件項目說明

測試的 T-SQL 語法為:

SELECT *, LEFT(cl, PATINDEX('%[^.0-9]%', cl) - 1) AS cIP	
FROM (
	SELECT *, (TRIM(SUBSTRING(cs, PATINDEX('%[:=]%[0-9]%.[0-9]%.[0-9]%.[0-9]%', cs) + 1, 17)) + ' ') AS cl
	FROM (
		SELECT ' IP: 172.16.1.1 User' AS cs
	) AS ts
) AS ts

因為條件式只能抓到滿足的 事件內容 ,我的重點是要抽出 IP ,所以用 SubString 抓出中間字串,利用 PatIndex 抓到符合的開始位置,因為不確定 IP 的具體長度,先抓 17 個長度下來。

接下來要把 IP 尾端無效內容去除,由於 IP 由數字 0 ~ 9 跟小數點組成,所以比較單純,直接把其他字元抓出來砍。由於 IP 可能會出現在最後,所以在前一段先加一個空白字元,確保這段可以找到非 IP 的字元進行運算。

會這麼繞,是真的不知道 T-SQL 可以怎樣抽 IP 出來,只能先抓開頭,再減尾巴,最後把 IP 剪出來,如果有更好的方法,也請不吝分享。

Categories: 工作點滴, 技術分享 | 標籤: | 發表留言

在WordPress.com寫網誌.

%d 位部落客按了讚: