SQL日志卫士 源码公开
序言
SQL日志卫士是本人开发的SQL备份专家，附属软件，现在正式源码公开。免费获取地址在文章最后。
软件的原理是读取SQLserver 数据库 的日志内容，从中检索出需要管理员认真处理的警告和异常。例如，sa非法登录的错误记录，常常sa的被暴力密码攻击登录，每次登录失败都会在日志中记录。因为SQLserver没有附带真观的日志查错入口，管理员没有日志管理习惯，则会没有感知每天是否有这样的攻击发生。
SQL日志卫士
一、GDI+ 重绘菜单
软件的原理是读取SQLserver 数据库的日志内容，从中检索出需要管理员认真处理的警告和异常。例如，sa非法登录的错误记录，常常sa的被暴力密码攻击登录，每次登录失败都会在日志中记录。因为SQLserver没有附带真观的日志查错入口，管理员没有日志管理习惯，则会没有感知每天是否有这样的攻击发生。
private void initSwitchCtxMenu()
{
ContextMenu _ctxSwitch = null;
MenuItem _mnuCtsItem = null;
Rectangle _RectMenuItem; //画菜单使用变量
Pen _pen1 = new Pen(Color.White); //DrawBevel事件中画菜单使用变量
Pen _pen2 = new Pen(Color.Gray); //DrawBevel事件中画菜单使用变量
Color _ctxMenuBgColor; //切换菜单背景色
_ctxSwitch = new ContextMenu();
_mnuCtsItem = new MenuItem();
_mnuCtsItem.Index = 0;
_mnuCtsItem.OwnerDraw = true;
_mnuCtsItem.Text = "通行证会员";
_mnuCtsItem.Click += new EventHandler(mnuCtsItem_Click);
_mnuCtsItem.DrawItem += new DrawItemEventHandler(mnuCtsItem_DrawItem);
_mnuCtsItem.MeasureItem += new MeasureItemEventHandler(_mnuCtsItem_MeasureItem);
_ctxSwitch.MenuItems.Add(_mnuCtsItem);
_mnuCtsItem = new MenuItem();
_mnuCtsItem.Index = 1;
_mnuCtsItem.OwnerDraw = true;
_mnuCtsItem.Text = "检查更新";
_mnuCtsItem.Click += new EventHandler(mnuCtsItem_Click);
_mnuCtsItem.DrawItem += new DrawItemEventHandler(mnuCtsItem_DrawItem);
_mnuCtsItem.MeasureItem += new MeasureItemEventHandler(_mnuCtsItem_MeasureItem);
_ctxSwitch.MenuItems.Add(_mnuCtsItem);
_mnuCtsItem = new MenuItem();
_mnuCtsItem.Index = 2;
_mnuCtsItem.OwnerDraw = true;
_mnuCtsItem.Text = "产品特性";
_mnuCtsItem.Click += new EventHandler(mnuCtsItem_Click);
_mnuCtsItem.DrawItem += new DrawItemEventHandler(mnuCtsItem_DrawItem);
_mnuCtsItem.MeasureItem += new MeasureItemEventHandler(_mnuCtsItem_MeasureItem);
_ctxSwitch.MenuItems.Add(_mnuCtsItem);
_mnuCtsItem = new MenuItem();
_mnuCtsItem.Index = 4;
_mnuCtsItem.OwnerDraw = true;
_mnuCtsItem.Text = "建议反馈";
_mnuCtsItem.Click += new EventHandler(mnuCtsItem_Click);
_mnuCtsItem.DrawItem += new DrawItemEventHandler(mnuCtsItem_DrawItem);
_mnuCtsItem.MeasureItem += new MeasureItemEventHandler(_mnuCtsItem_MeasureItem);
_ctxSwitch.MenuItems.Add(_mnuCtsItem);
_mnuCtsItem = new MenuItem();
_mnuCtsItem.Index = 5;
_mnuCtsItem.OwnerDraw = true;
_mnuCtsItem.Text = "联系我们";
_mnuCtsItem.Click += new EventHandler(mnuCtsItem_Click);
_mnuCtsItem.DrawItem += new DrawItemEventHandler(mnuCtsItem_DrawItem);
_mnuCtsItem.MeasureItem += new MeasureItemEventHandler(_mnuCtsItem_MeasureItem);
_ctxSwitch.MenuItems.Add(_mnuCtsItem);
_mnuCtsItem = new MenuItem();
_mnuCtsItem.Index = 6;
_mnuCtsItem.OwnerDraw = true;
_mnuCtsItem.Text = "商务合作";
_mnuCtsItem.Click += new EventHandler(mnuCtsItem_Click);
_mnuCtsItem.DrawItem += new DrawItemEventHandler(mnuCtsItem_DrawItem);
_mnuCtsItem.MeasureItem += new MeasureItemEventHandler(_mnuCtsItem_MeasureItem);
_ctxSwitch.MenuItems.Add(_mnuCtsItem);
_mnuCtsItem = new MenuItem();
_mnuCtsItem.Index = 7;
_mnuCtsItem.OwnerDraw = true;
_mnuCtsItem.Text = "关于";
_mnuCtsItem.Click += new EventHandler(mnuCtsItem_Click);
_mnuCtsItem.DrawItem += new DrawItemEventHandler(mnuCtsItem_DrawItem);
_mnuCtsItem.MeasureItem += new MeasureItemEventHandler(_mnuCtsItem_MeasureItem);
_ctxSwitch.MenuItems.Add(_mnuCtsItem);
}
GDI+重绘菜单事件是每个菜单项的 _mnuCtsItem.DrawItem，代码如下：
View Code
重绘菜单左侧图标图案 DrawIconMenu(sender, e) 代码如下：
private void DrawIconMenu(object sender, DrawItemEventArgs e)
{
if (((MenuItem)sender).Index == 0)
{
e.Graphics.DrawImage(icoPerson, e.Bounds.X + 2, e.Bounds.Y + 2, 22, 22);
}
if (((MenuItem)sender).Index == 1)
{
e.Graphics.DrawImage(icoUpdate, e.Bounds.X + 2, e.Bounds.Y + 2, 22, 22);
}
if (((MenuItem)sender).Index == 2)
{
e.Graphics.DrawImage(icoWindow, e.Bounds.X + 2, e.Bounds.Y + 2, 22, 22);
}
if (((MenuItem)sender).Index == 3)
{
//e.Graphics.DrawImage(icoSearch, e.Bounds.X + 2, e.Bounds.Y + 2, 22, 22);
e.Graphics.DrawImage(icoPodcast, e.Bounds.X + 2, e.Bounds.Y + 2, 22, 22);
}
if (((MenuItem)sender).Index == 4)
{
//e.Graphics.DrawImage(icoPodcast, e.Bounds.X + 2, e.Bounds.Y + 2, 22, 22);
e.Graphics.DrawImage(icoCloud, e.Bounds.X + 2, e.Bounds.Y + 2, 22, 22);
}
if (((MenuItem)sender).Index == 5)
{
//e.Graphics.DrawImage(icoCloud, e.Bounds.X + 2, e.Bounds.Y + 2, 22, 22);
e.Graphics.DrawImage(icoBagRed, e.Bounds.X + 2, e.Bounds.Y + 2, 22, 22);
}
if (((MenuItem)sender).Index == 6)
{
//e.Graphics.DrawImage(icoBagRed, e.Bounds.X + 2, e.Bounds.Y + 2, 22, 22);
e.Graphics.DrawImage(icoInfo, e.Bounds.X + 2, e.Bounds.Y + 2, 22, 22);
}
if (((MenuItem)sender).Index == 7)
{
e.Graphics.DrawImage(icoInfo, e.Bounds.X + 2, e.Bounds.Y + 2, 22, 22);
}
}
e.Graphics.DrawImage(）第一个参数就是bitmap对象。这个对象是作为资源添加项目中，作为Properties资源，如下:
private Bitmap icoPerson
{
get
{
return Properties.Resources.mPerson;
}
}
private Bitmap icoUpdate
{
get
{
return Properties.Resources.mUpdate;
}
}
private Bitmap icoWindow
{
get
{
return Properties.Resources.mWin;
}
}
private Bitmap icoSearch
{
get
{
return Properties.Resources.mSearch;
}
}
private Bitmap icoPodcast
{
get
{
return Properties.Resources.mPodcast;
}
}
private Bitmap icoCloud
{
get
{
return Properties.Resources.mCloud;
}
}
private Bitmap icoBagRed
{
get
{
return Properties.Resources.mBagred;
}
}
private Bitmap icoInfo
{
get
{
return Properties.Resources.mInfo;
}
}
1 /// <summary>
2 /// 菜单分割条
3 /// </summary>
4 /// <param name="rect"></param>
5 /// <param name="canvas"></param>
6 /// <param name="Bevel"></param>
7 private void DrawBevel(Rectangle rect, Graphics canvas, bool Bevel)
8 {
9
10 if (Bevel == true)
11 {
12 _pen1.Color = Color.FromArgb(209, 208, 212);
13 _pen2.Color = Color.Gray;
14 }
15 else
16 {
17 _pen1.Color = Color.FromArgb(255, 0, 0);
18 _pen2.Color = Color.FromArgb(10, 142, 207); // _pen2.Color = Color.Yellow;//
19 }
20 canvas.DrawLine(_pen1, rect.X, rect.Y, rect.Right, rect.Y);
21 canvas.DrawLine(_pen1, rect.X, rect.Y, rect.X, rect.Bottom);
22 canvas.DrawLine(_pen2, rect.Right, rect.Bottom, rect.Left, rect.Bottom);
23 canvas.DrawLine(_pen2, rect.Right, rect.Bottom, rect.Right, rect.Y);
24 }
25
26 private void _mnuCtsItem_MeasureItem(object sender, MeasureItemEventArgs e)
27 {
28 e.ItemWidth = 150; //菜单宽度
29 if (((MenuItem)sender).Text == "-")
30 e.ItemHeight = 5;//分隔条宽度
31 else
32 e.ItemHeight = 30;//有字菜单宽度
33 }
二、扫描核心代码
如何把下图中，在Visual Studio设计器中丑陋的贴图Form运行起来又实用又漂亮呢？
此处放一个PANEL，它里面放入，扫描，登录异常，其它错误，扫描结束，这个PANEL隐藏，直接显示GRIDVIEW。当然这个GRIDVIEQW是代码美化的
View Code以上代码的窗体加载时，初始化GRIDVIEW 并异步读取SQLSERVER日志，以UI上用户则会看到扫描进度，和错误信息计数，如图：
重绘GridView代码：
1 //来源 https://www.cnblogs.com/alexywt/p/6706459.html
2 //原文是VB代码，本人将其它改了C#
3 public class CGridHelper
4 {
5 private DataGridView mGrid;
6 public DataGridView Grid
7 {
8 get { return mGrid; }
9 set
10 {
11 mGrid = value;
12 mGrid.BorderStyle = BorderStyle.None;
13 mGrid.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Single;
14 mGrid.ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False;
15 mGrid.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing;
16 mGrid.RowHeadersBorderStyle = DataGridViewHeaderBorderStyle.Single;
17 mGrid.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.DisableResizing;
18 mGrid.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;
19 mGrid.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
20 mGrid.EnableHeadersVisualStyles = false;
21 mGrid.AllowUserToResizeRows = false;
22 mGrid.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
23 mGrid.ReadOnly = true;
24 mGrid.AllowUserToAddRows = false;
25 mGrid.AllowUserToDeleteRows = false;
26
27 for (int i = 0; i < mGrid.Columns.Count - 1; i++)
28 {
29 mGrid.Columns[i].SortMode = DataGridViewColumnSortMode.NotSortable;
30 }
31 }
32 }
33
34
35 private Color mOddRowBackColor;
36 public Color OddRowBackColor
37 {
38 get { return mOddRowBackColor; }
39 set
40 {
41 mOddRowBackColor = value;
42 if (mGrid.Rows.Count != 0)
43 {
44 for (int i = 0; i < mGrid.Rows.Count - 1; i++)
45 {
46 mGrid.Rows[i].DefaultCellStyle.BackColor = value;
47 }
48 }
49
50 }
51
52 }
53
54
55 private Color mHeaderBackColor;
56 public Color HeaderBackColor
57 {
58 get { return mHeaderBackColor; }
59 set
60 {
61 mHeaderBackColor = value;
62
63 mGrid.ColumnHeadersDefaultCellStyle.BackColor = value;
64
65
66 }
67 }
68
69
70 private Color mEvenRowBackColor;
71 public Color EvenRowBackColor
72 {
73 get { return mEvenRowBackColor; }
74 set
75 {
76 mEvenRowBackColor = value;
77
78 if (mGrid.Rows.Count != 0)
79 {
80 for (int i = 1; i < mGrid.Rows.Count - 1; i++)
81 {
82 mGrid.Rows[i].DefaultCellStyle.BackColor = value;
83 }
84
85 }
86 }
87 }
88
89 private Color mGridColor;
90 public Color GridColor
91 {
92 get { return mGridColor; }
93 set
94 {
95 mGridColor = value;
96
97 mGrid.GridColor = value;
98
99
100
101 }
102 }
103
104
105 private string mColumnWidth;
106
107 public string ColumnWidth
108 {
109 get { return mColumnWidth; }
110 set
111 {
112 mColumnWidth = value;
113
114 int mColCount = mGrid.ColumnCount;
115
116 string[] mWidths = value.Split(';');
117
118 for (int i = 0; i < mWidths.Length ; i++)
119 {
120
121
122 if (int.Parse(mWidths[i]) <= 0)
123 {
124 mGrid.Columns[i].Visible = false;
125 }
126 else
127 {
128 mGrid.Columns[i].Width = int.Parse(mWidths[i]);
129 }
130
131 }
132
133 }
134 }
135
136 private string mColumnAlignment;
137
138 public string ColumnAlignment
139 {
140 get { return mColumnAlignment; }
141 set
142 {
143 mColumnAlignment = value;
144
145
146 int mColCount = mGrid.ColumnCount;
147
148 string[] mAlignments = value.Split(';');
149
150
151 for (int i = 0; i < mAlignments.Length - 1; i++)
152 {
153 if (i + 1 > mColCount)
154 { break; }
155
156 switch (mAlignments[i])
157 {
158 case "L":
159 mGrid.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
160 break;
161 case "R":
162 mGrid.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
163 break;
164 case "M":
165 mGrid.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
166 break;
167 default:
168 mGrid.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
169 break;
170
171 }
172 }
173
174
175
176 }
177 }
178
179
180 private Font mHeaderFont;
181 public Font HeaderFont
182 {
183 get { return mHeaderFont; }
184 set
185 {
186 mHeaderFont = value;
187 mHeaderFont = value;
188 mGrid.ColumnHeadersDefaultCellStyle.Font = value;
189
190 }
191 }
192
193
194 private int mHeaderHeight;
195 public int HeaderHeight
196 {
197 get { return mHeaderHeight; }
198 set
199 {
200
201 mHeaderHeight = value;
202 mGrid.ColumnHeadersHeight = value;
203
204 }
205 }
206
207
208 private Color mHeaderForeColor;
209 public Color HeaderForeColor
210 {
211 get { return mHeaderForeColor; }
212 set
213 {
214 mHeaderForeColor = value;
215
216 mGrid.ColumnHeadersDefaultCellStyle.ForeColor = value;
217
218 }
219 }
220
221
222 private int mRowHeaderWidth;
223 public int RowHeaderWidth
224 {
225 get { return mRowHeaderWidth; }
226 set
227 {
228 mRowHeaderWidth = value;
229
230 mGrid.RowHeadersWidth = value;
231
232
233 }
234 }
235
236 private int mRowHeight;
237
238 public int RowHeight
239 {
240 get { return mRowHeight; }
241 set
242 {
243 mRowHeight = value;
244
245 for (int i = 0; i < mGrid.Rows.Count - 1; i++)
246 {
247 mGrid.Rows[i].Height = value;
248 }
249
250
251 }
252 }
253
254
255 private Font mRowFont;
256 public Font RowFont
257 {
258 get { return mRowFont; }
259 set
260 {
261 mRowFont = value;
262
263 mGrid.RowsDefaultCellStyle.Font = value;
264
265
266 }
267 }
268
269
270 private Color mBackColor;
271
272 public Color BackColor
273 {
274 get { return mBackColor; }
275 set
276 {
277 mBackColor = value;
278
279
280 mGrid.BackgroundColor = value;
281
282 }
283 }
284
285
286 public CGridHelper(
287 DataGridView TargetGrid, Color BackColor, Font HeaderFont, int HeaderHeight,
288 Color HeaderBackColor, Color HeaderForeColor, int RowHeaderWidth,
289 Font RowFont, int RowHeight, Color OddRowBackColor, Color EvenRowBackColor,
290 Color GridColor, string ColumnWidth, string ColumnAlignment
291
292 )
293 {
294 this.Grid = TargetGrid;
295 this.BackColor = BackColor;
296
297 this.HeaderFont = HeaderFont;
298 this.HeaderHeight = HeaderHeight;
299 this.HeaderBackColor = HeaderBackColor;
300 this.HeaderForeColor = HeaderForeColor;
301 this.RowHeaderWidth = RowHeaderWidth;
302
303
304 this.RowFont = RowFont;
305 this.RowHeight = RowHeight;
306 this.OddRowBackColor = OddRowBackColor;
307 this.EvenRowBackColor = EvenRowBackColor;
308
309 this.GridColor = GridColor;
310 this.ColumnWidth = ColumnWidth;
311 this.ColumnAlignment = ColumnAlignment;
312 }
313
314 public void ReSetStyle()
315 {
316 ReSetStyle(
317 this.BackColor, this.HeaderFont, this.HeaderHeight,
318 this.HeaderBackColor, this.HeaderForeColor, this.RowHeaderWidth,
319 this.RowFont, this.RowHeight, this.OddRowBackColor, this.EvenRowBackColor, this.GridColor, this.ColumnWidth, this.ColumnAlignment);
320 }
321
322
323 public void ReSetStyle(
324 Color BackColor, Font HeaderFont, int HeaderHeight,
325 Color HeaderBackColor, Color HeaderForeColor, int RowHeaderWidth,
326 Font RowFont, int RowHeight, Color OddRowBackColor, Color EvenRowBackColor,
327 Color GridColor, string ColumnWidth, string ColumnAlignment
328 )
329 {
330
331 this.BackColor = BackColor;
332
333 this.HeaderFont = HeaderFont;
334 this.HeaderHeight = HeaderHeight;
335 this.HeaderBackColor = HeaderBackColor;
336 this.HeaderForeColor = HeaderForeColor;
337 this.RowHeaderWidth = RowHeaderWidth;
338
339 this.RowFont = RowFont;
340 this.RowHeight = RowHeight;
341 this.OddRowBackColor = OddRowBackColor;
342 this.EvenRowBackColor = EvenRowBackColor;
343
344 this.GridColor = GridColor;
345 this.ColumnWidth = ColumnWidth;
346 this.ColumnAlignment = ColumnAlignment;
347 }
348 }
读取SQLSERVER日志，以DATATABLE方式读取到日志，再以面向对象C# 编程思维，转换成Entity_xp_readerrorlog 这个实体类
1 public class Entity_xp_readerrorlog
2 {
3 public string LogDate { get; set; }
4
5 public string ProcessInfo { get; set; }
6
7 public string Text { get; set; }
8 }
9
10 private void ReadRawData()
11 {
12 CoreComm.Core.DBfun dd = new CoreComm.Core.DBfun();
13
14 try
15 {
16 _tbRawData = dd.xp_readerrorLog(
17 "0",
18 DateTime.Now.Subtract(new TimeSpan(1, 0, 0, 0)).ToString("yyyyMMdd HH:mm"),
19 DateTime.Now.ToString("yyyyMMdd HH:mm")
20 );
21 }
22 catch (Exception ex)
23 {
24 BeginInvoke(new delegdoUI(ShowMsgbox), ex.Message);
25
26 BeginInvoke(new delegdoUI(ScanedOver), "");
27 return;
28 }
29
30 BeginInvoke(new delegdoUI(changeExplain), "正在扫描日志");
31 int pos = 0;
32 foreach (DataRow row in _tbRawData.Rows)
33 {
34 if (_appAbort)
35 return;
36
37 BeginInvoke(new delegdoUI(changeExplain), row[2].ToString());
38
39 BeginInvoke(new delegLblCnt(addlabelCnt), lblScaned); //lblScaned.Text = (int.Parse(lblScaned.Text) + 1).ToString();
40
41
42
43 //自定义实体类，将DATATABLE 每行转成 每个类对像字段，后续读取这个类实体信息，就获取对应的数据
44 Entity_xp_readerrorlog logRow = new Entity_xp_readerrorlog();
45 logRow.LogDate = row[0].ToString();
46 logRow.ProcessInfo = row[1].ToString();
47 logRow.Text = row[2].ToString();
48
49 switch (FindLogProblem.TrueIsProblemInLog(row[2].ToString()))//根据SQL日志中的错误文本来划分错误类型，扩展这个方法可以新增更多异常提醒
50 {
51
52 case EnumSQLProblemInLog.LoginError:
53 dicErr.Add(pos.ToString(), logRow);
54 BeginInvoke(new delegLblCnt(addlabelCnt), lblLoginFailed); //UI上异常登录数增加
55 break;
56 case EnumSQLProblemInLog.WarningAttention:
57 dicWarn.Add(pos.ToString(), logRow);
58 BeginInvoke(new delegLblCnt(addlabelCnt), lblWarning);//UI上其它错误显示数增加
59 break;
60 default:
61 break;
62 }
63
64 pos++;
65 //UI进度条变化
66 BeginInvoke(new delegdoUI(ProgressBarGo),
67 Convert.ToInt32((Convert.ToDouble(pos) / Convert.ToDouble(_tbRawData.Rows.Count) * 100))
68 .ToString()
69 );
70
71
72 Thread.Sleep(100);
73 }
74
75
76 //错误信息塞入 自定义GRIDVIEW
77 BeginInvoke(new delegDict(PutInGridView), dicErr, 1);
78 //警告信息塞入 自定义GRIDVIEW
79 BeginInvoke(new delegDict(PutInGridView), dicWarn, 2);
80
81
82 BeginInvoke(new delegdoUI(ScanedOver), "");
83
84 }
上述代码中 new delegLblCnt(addlabelCnt) 异步实现UI上的登录异常，扫描过程的计数
private void addlabelCnt(Label lbl)
{
lbl.Text = (int.Parse(lbl.Text) + 1).ToString();
}
读取到的日志，是对象，不是DATATABLE，如何放入gridview的呢？代码如下：
private void PutInGridView(Dictionary<string, Entity_xp_readerrorlog> dic, int typ)
{
if (typ == 1)
{
if (dic.Count > 0)
{
gvData.Rows.Add(
new object[] { "异常登录：", "", "" });
}
}
else
{
if (dic.Count > 0)
{
gvData.Rows.Add(
new object[] { "其它错误：","",""
});
}
}
foreach (string key in dic.Keys)
{
gvData.Rows.Add(
new object[] {
dic[key].LogDate,
dic[key].ProcessInfo ,
dic[key].Text.Substring(0,dic[key].Text .Length/2 ) + "......",
"详情"
});
gvData.Rows[gvData.Rows.Count - 1].Cells[3].Tag = dic[key].Text;
}
}
最终效果：
源码免费获取方式：
搜索微信公众号ID：canbloom，在公众号输入： SQL日志卫士源码
浙公网安备 33010602011771号