將 IIS Log匯入資料庫

1. 下載 Log Parser 2.2
    
2. script LogParser to Database
   

   "C:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT * INTO IISLogs FROM C:\inetpub\logs\LogFiles\W3SVC2145945211\u_ex*.log" -i:W3C -o:SQL -server:MORTIZ-HY -database:Logs -driver:"SQL Server" -username:moz -password:moz123 -createTable:ON

   
   

   "C:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT TO_LOCALTIME(TO_TIMESTAMP([date], [time])), [s-sitename], [s-computername], [s-ip], [cs-method], [cs-uri-stem], [cs-uri-query], [s-port], [cs-username], [c-ip], [cs-version], [cs(User-Agent)], [cs(Cookie)], [cs(Referer)], [cs-host], [sc-status], [sc-substatus], [sc-win32-status], [sc-bytes], [cs-bytes], [time-taken] INTO IISLogs FROM C:\inetpub\logs\LogFiles\W3SVC2145945211\u_ex130222.log " -o:SQL -server:MORTIZ-HY -database:Logs -driver:"SQL Server" -username:moz -password:moz123 -createTable:ON

   
   

   "C:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT * INTO IISLogs FROM C:\inetpub\logs\LogFiles\SelectedLogs\u_ex*.log" -i:W3C -o:SQL -server:210.65.10.173 -database:Logs -driver:"SQL Server" -username:sa -password:SQLpw1064MS -createTable:ON

   
   
3. 清掉 IISLogs無用的 data
   

   DELETE Logs.dbo.IISLogs
   WHERE 
    csUriStem LIKE '%txt'
      OR csUriStem LIKE '%jpg'
      OR csUriStem LIKE '%png'
      OR csUriStem LIKE '%css'
      OR csUriStem LIKE '%js'
      OR csUriStem LIKE '%ico'
      OR csUriStem LIKE '%gif'
      OR csUriStem LIKE '/Scripts%'
      OR csUriStem LIKE '/TrendgoAdmin%'
   GO

4. temp