用c#读取并分析sql2005日志

时间:2022-07-11 20:42:07
用过logExplorer的朋友都会被他强悍的功能吸引,我写过一篇详细的操作文档可以参考
http://blog.csdn.net/jinjazz/archive/2008/05/19/2459692.aspx

我们可以自己用开发工具来实现sql日志的读取,这个应用还是很酷的,具体思路

1、首先要了解一个没有公开的系统函数::fn_dblog,他可以读取sql日志,并返回二进制的行数据
2、然后要了解sql的二进制数据是如何存储的,这个可以参考我的blog文章
http://blog.csdn.net/jinjazz/archive/2008/08/07/2783872.aspx
3、用自己擅长的开发工具来分析数据,得到我们需要的信息

我用c#写了一个测试样例,分析了int,char,datetime和varchar的日志情况而且没有考虑null和空字符串的保存,希望感兴趣的朋友能和我一起交流打造属于自己的日志分析工具

详细的试验步骤以及代码如下:

1、首先建立sqlserver的测试环境,我用的sql2005,这个过程不能保证在之前的版本中运行
以下sql语句会建立一个dbLogTest数据库,并建立一张log_test表,然后插入3条数据之后把表清空
  1. use master
  2. go
  3. create database dbLogTest
  4. go
  5. use  dbLogTest
  6. go
  7. create table log_test(id int ,code char(10),name varchar(20),date datetime,memo varchar(100))
  8. insert into log_test select 100, 'id001','jinjazz',getdate(),'剪刀'
  9. insert into log_test select 65549,'id002','游客',getdate()-1,'这家伙很懒,没有设置昵称'
  10. insert into log_test select -999,'id003','这家伙来自火星',getdate()-1000,'a'
  11. delete from log_test
  12. --use master 
  13. --go
  14. --drop database dbLogTest
  15.  

2、我们最终的目的是要找到被我们删掉的数据

3、分析日志的c#代码:我已经尽量详细的写了注释
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4. namespace ConsoleApplication21
  5. {
  6.     class Program
  7.     {
  8.         /// <summary>
  9.         /// 分析sql2005日志,找回被delete的数据,引用请保留以下信息
  10.         /// 作者:jinjazz (csdn的剪刀)
  11.         /// 作者blog:http://blog.csdn.net/jinjazz
  12.         /// </summary>
  13.         /// <param name="args"></param>
  14.         static void Main(string[] args)
  15.         {
  16.             using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection())
  17.             {
  18.                 conn.ConnectionString = "server=localhost;uid=sa;pwd=sqlgis;database=dbLogTest";
  19.                 conn.Open();
  20.                 using (System.Data.SqlClient.SqlCommand command = conn.CreateCommand())
  21.                 {
  22.                     //察看dbo.log_test对象的sql日志
  23.                     command.CommandText = @"SELECT allocunitname,operation,[RowLog Contents 0] as r0,[RowLog Contents 1]as r1 
  24.                                 from::fn_dblog (nullnull)   
  25.                                 where allocunitname like 'dbo.log_test%'and
  26.                                 operation in('LOP_INSERT_ROWS','LOP_DELETE_ROWS')";
  27.                     System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();
  28.                     //根据表字段的顺序建立字段数组
  29.                     Datacolumn[] columns = new Datacolumn[]
  30.                         {
  31.                             new Datacolumn("id", System.Data.SqlDbType.Int),
  32.                             new Datacolumn("code", System.Data.SqlDbType.Char,10),
  33.                             new Datacolumn("name", System.Data.SqlDbType.VarChar),
  34.                             new Datacolumn("date", System.Data.SqlDbType.DateTime),
  35.                             new Datacolumn("memo", System.Data.SqlDbType.VarChar)
  36.                         };
  37.                     //循环读取日志
  38.                     while (reader.Read())
  39.                     {
  40.                         byte[] data = (byte[])reader["r0"];
  41.                         
  42.                         try
  43.                         {
  44.                             //把二进制数据结构转换为明文
  45.                             TranslateData(data, columns);
  46.                             Console.WriteLine("数据对象{1}的{0}操作:", reader["operation"], reader["allocunitname"]);
  47.                             foreach (Datacolumn c in columns)
  48.                             {
  49.                                 Console.WriteLine("{0} = {1}", c.Name, c.Value);
  50.                             }
  51.                             Console.WriteLine();
  52.                         }
  53.                         catch
  54.                         {
  55.                             //to-do...
  56.                         }
  57.                         
  58.                     }
  59.                     reader.Close();
  60.                 }
  61.                 conn.Close();
  62.             }
  63.             Console.WriteLine("************************日志分析完成");
  64.             Console.ReadLine();
  65.         }
  66.         //自定义的column结构
  67.         public class Datacolumn
  68.         {
  69.             public string Name;
  70.             public System.Data.SqlDbType DataType;
  71.             public short Length = -1;
  72.             public object Value = null;
  73.             public Datacolumn(string name, System.Data.SqlDbType type)
  74.             {
  75.                 Name = name;
  76.                 DataType = type;
  77.             }
  78.             public Datacolumn(string name,System.Data.SqlDbType type,short length)
  79.             {
  80.                 Name = name;
  81.                 DataType = type;
  82.                 Length = length;
  83.             }
  84.         }
  85.         /// <summary>
  86.         /// sql二进制结构翻译,这个比较关键,测试环境为sql2005,其他版本没有测过。
  87.         /// </summary>
  88.         /// <param name="data"></param>
  89.         /// <param name="columns"></param>
  90.         static void TranslateData(byte[] data, Datacolumn[] columns)
  91.         {
  92.             //我只根据示例写了Char,DateTime,Int三种定长度字段和varchar一种不定长字段,其余的有兴趣可以自己补充
  93.             //这里没有暂时没有考虑Null和空字符串两种情况,以后会补充。
  94.             //引用请保留以下信息:
  95.             //作者:jinjazz 
  96.             //sql的数据行二进制结构参考我的blog
  97.             //http://blog.csdn.net/jinjazz/archive/2008/08/07/2783872.aspx
  98.             //行数据从第5个字节开始
  99.             short index = 4;
  100.             //先取定长字段
  101.             foreach (Datacolumn c in columns)
  102.             {
  103.                 switch (c.DataType)
  104.                 {
  105.                     case System.Data.SqlDbType.Char:
  106.                         //读取定长字符串,需要根据表结构指定长度
  107.                         c.Value = System.Text.Encoding.Default.GetString(data,index,c.Length);
  108.                         index += c.Length;
  109.                         break;
  110.                     case System.Data.SqlDbType.DateTime:
  111.                         //读取datetime字段,sql为8字节保存
  112.                         System.DateTime date = new DateTime(1900, 1, 1);
  113.                         //前四位1/300秒保存
  114.                         int second = BitConverter.ToInt32(data, index);
  115.                         date = date.AddSeconds(second/300);
  116.                         index += 4;
  117.                         //后四位1900-1-1的天数
  118.                         int days = BitConverter.ToInt32(data, index);
  119.                         date=date.AddDays(days);
  120.                         index += 4;
  121.                         c.Value = date;
  122.                         break;
  123.                     case System.Data.SqlDbType.Int:
  124.                         //读取int字段,为4个字节保存
  125.                         c.Value = BitConverter.ToInt32(data, index);
  126.                         index += 4;
  127.                         break;
  128.                    default:
  129.                        //忽略不定长字段和其他不支持以及不愿意考虑的字段
  130.                         break;
  131.                 }
  132.             }
  133.             //跳过三个字节
  134.             index += 3;
  135.             //取变长字段的数量,保存两个字节
  136.             short varColumnCount = BitConverter.ToInt16(data, index);
  137.             index += 2;
  138.             //接下来,每两个字节保存一个变长字段的结束位置,
  139.             //所以第一个变长字段的开始位置可以算出来
  140.             short startIndex =(short)( index + varColumnCount * 2);
  141.             //第一个变长字段的结束位置也可以算出来
  142.             short endIndex = BitConverter.ToInt16(data, index);
  143.             //循环变长字段列表读取数据
  144.             foreach (Datacolumn c in columns)
  145.             {
  146.                 switch (c.DataType)
  147.                 {
  148.                     case System.Data.SqlDbType.VarChar:
  149.                         //根据开始和结束位置,可以算出来每个变长字段的值
  150.                         c.Value =System.Text.Encoding.Default.GetString(data, startIndex, endIndex - startIndex);
  151.                         //下一个变长字段的开始位置
  152.                         startIndex = endIndex;
  153.                         //获取下一个变长字段的结束位置
  154.                         index += 2;
  155.                         endIndex = BitConverter.ToInt16(data, index);
  156.                         break;
  157.                     default:
  158.                         //忽略定长字段和其他不支持以及不愿意考虑的字段
  159.                         break;
  160.                 }
  161.             }
  162.             //获取完毕
  163.         }
  164.     }
  165. }
4、更改你的sql连接字符串后运行以上代码,会看到如下输出信息:
  1. 数据对象dbo.log_test的LOP_INSERT_ROWS操作:
  2. id = 100
  3. code = id001
  4. name = jinjazz
  5. date = 2008-8-7 18:14:03
  6. memo = 剪刀
  7. 数据对象dbo.log_test的LOP_INSERT_ROWS操作:
  8. id = 65549
  9. code = id002
  10. name = 游客
  11. date = 2008-8-6 18:14:03
  12. memo = 这家伙很懒,没有设置昵称
  13. 数据对象dbo.log_test的LOP_INSERT_ROWS操作:
  14. id = -999
  15. code = id003
  16. name = 这家伙来自火星
  17. date = 2005-11-11 18:14:03
  18. memo = a
  19. 数据对象dbo.log_test的LOP_DELETE_ROWS操作:
  20. id = 100
  21. code = id001
  22. name = jinjazz
  23. date = 2008-8-7 18:14:03
  24. memo = 剪刀
  25. 数据对象dbo.log_test的LOP_DELETE_ROWS操作:
  26. id = 65549
  27. code = id002
  28. name = 游客
  29. date = 2008-8-6 18:14:03
  30. memo = 这家伙很懒,没有设置昵称
  31. 数据对象dbo.log_test的LOP_DELETE_ROWS操作:
  32. id = -999
  33. code = id003
  34. name = 这家伙来自火星
  35. date = 2005-11-11 18:14:03
  36. memo = a
  37. ************************日志分析完成

试验成功~~

相关文档:
SQL2005数据行的二进制结构
LogExplore的一个详细操作手册