读取iis日志到sql server
using Fasterflect; using System; using System.Collections.Generic; using System.Data.SqlClient; using System.IO; using System.Linq; using System.Reflection; using System.Text; using System.Threading.Tasks; namespace ConsoleApplication2 { // 记录iis日志到sql server数据库中,并进行查询 class Program_IISLogFile { static void Main(string[] args) { Console.WriteLine("开始行:"); var startIndex = Console.ReadLine(); Console.WriteLine("结束行:"); var endIndex = Console.ReadLine(); Read("D:\\job\\test\\tu_ex151203.log", int.Parse(startIndex), int.Parse(endIndex)); Console.WriteLine("over"); Console.ReadKey(); } // 数据库连接 const string connectionStr = "user id=sa;password=12345;data source=(local);initial catalog=test"; // 读取日志文件中的iis操作记录 public static void Read(string path, int startIndex, int endInex) { StreamReader sr = new StreamReader(path, Encoding.Default); String line; using (SqlConnection conn = new SqlConnection(connectionStr)) { using (SqlCommand cmd = new SqlCommand(connectionStr, conn)) { conn.Open(); int rows = 0; int statIndex = 0; string sql = string.Empty; while ((line = sr.ReadLine()) != null) { statIndex++; if (statIndex < 5 + startIndex) continue; if (endInex <= statIndex - 5) break; try { // Console.WriteLine(line.ToString()); string[] strs = line.ToString().Split(' '); string sql2 = ""; if (strs != null) { for (int i = 0; i < strs.Count(); i++) { sql2 += "'" + strs[i] + "'"; if ((i + 1) < strs.Count()) { sql2 += ","; } } } sql = string.Format(@"insert into [iislog] ([date], [time], [s-ip], [cs-method], [cs-uri-stem], [cs-uri-query], [s-port], [cs-username], [c-ip], [cs(User-Agent)], [cs(Referer)], [sc-status], [sc-substatus], [sc-win32-status], [time-taken]) values({0})", sql2); cmd.CommandText = sql; rows += cmd.ExecuteNonQuery(); } catch (System.Data.SqlClient.SqlException e) { //conn.Close(); Console.WriteLine("插入" + rows.ToString() + "条数据!" + e.Message + " sql:" + sql); //continue; } // Console.WriteLine("插入" + rows.ToString() + "条数据!"); } } } } #region 数据库相关 /* 数据库表创建语句 CREATE TABLE [dbo].[iislog]( [date] [varchar](50) NULL, [time] [varchar](50) NULL, [s-ip] [varchar](50) NULL, [cs-method] [varchar](5000) NULL, [cs-uri-stem] [varchar](5000) NULL, [cs-uri-query] [varchar](5000) NULL, [s-port] [varchar](5000) NULL, [cs-username] [varchar](5000) NULL, [c-ip] [varchar](5000) NULL, [cs(User-Agent)] [varchar](5000) NULL, [cs(Referer)] [varchar](5000) NULL, [sc-status] [varchar](5000) NULL, [sc-substatus] [varchar](5000) NULL, [sc-win32-status] [varchar](5000) NULL, [time-taken] [varchar](5000) NULL ) */ /* 查询请求次数较多的url select [cs-uri-stem] + '?' + [cs-uri-query] as '链接地址', [sc-status] '状态码', c '请求次数' from (select [cs-uri-stem], [cs-uri-query], [sc-status], count(1) c from iislog where PATINDEX('%.aspx%', [cs-uri-stem]) <> 0 group by [cs-uri-stem], [cs-uri-query], [sc-status]) tbl order by c desc, '链接地址' desc */ #endregion } }
最终效果:
欢迎在评论区留下你宝贵的意见,不论好坏都是我前进的动力(cnblogs 排名提升)!
如果喜欢,记得点赞、推荐、关注、收藏、转发 ... ;)