Hello people
Alright?
In this post, I would like to show you how to read and write events in the Windows Event Viewer using CLR (C #). To learn more about CLR, this powerful feature that lets you create and execute code written using the C # programming language and the .NET Framework to extend SQL Server functionality, visit the post. Introduction to SQL Common Language Runtime (CLR) in SQL Server.
In many situations, having this information available for database queries can be very useful for creating internal monitoring or for creating web applications that consume this information and help the development team identify application and service issues, as well as allow them to can write custom event messages within Stored Procedures of the SQL Server database, according to certain situations.
Another legal example for this post, is here in the company where I work, where there is a service that monitors the Event Viewer on a server, looking for an event where the application is "XPTO" and the type is "Error" . When an event is generated using this application, a monitoring team that works on a 24 × 7 basis makes the telephone call for a certain number to take the necessary actions, especially for highly critical routines that cannot fail or stop. This event is recorded by the CLR, when a certain situation in the database occurs.
How to record events in Event Viewer through SQL Server
To write events to the Windows Event Viewer from SQL Server, simply run the StpEvent_Viewer_Store Procedure from your CLR library.
Parameters:
- @Ds_Server: Name of the server you want to record the event.
- @Ds_EventType: Type of event you want to record (Error, FailureAudit, Warning, SuccessAudit, Success or Information)
- @Ds_Fonte: Name you want to set as the event source (Usually, the system or application name)
- @Ds_Message: Message you want to write to the event
- @Id_Evento: Code of the error or message you want to generate for this event (free choice of yours)
View source
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
using System.Diagnostics; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpEvent_Viewer_Gravar(string Ds_Servidor, string Ds_Tipo_Evento, string Ds_Fonte, string Ds_Mensagem, int Id_Evento) { var tipoEvento = Ds_Tipo_Evento; var enumTipoEvento = EventLogEntryType.Error; switch (tipoEvento) { case "Error": enumTipoEvento = EventLogEntryType.Error; break; case "FailureAudit": enumTipoEvento = EventLogEntryType.FailureAudit; break; case "Information": enumTipoEvento = EventLogEntryType.Information; break; case "SuccessAudit": case "Success": enumTipoEvento = EventLogEntryType.SuccessAudit; break; case "Warning": enumTipoEvento = EventLogEntryType.Warning; break; default: return; } if (!EventLog.SourceExists(Ds_Fonte)) { EventLog.CreateEventSource(Ds_Fonte, "Application"); } using (var ev = new EventLog("Application", Ds_Servidor)) { ev.Source = Ds_Fonte; ev.WriteEntry(Ds_Mensagem, enumTipoEvento, Id_Evento, 1); } } }; |
Example of use
How to record events in Event Viewer through SQL Server (No CLR)
Is it possible to record events in Event Viewer by SQL Server without using the CLR? It is, although it is not so "elegant", since it is not possible to specify the source (Source) of the event.
One way to do this is to use the SQL Agent's “Write to the Windows Application event log” option:
The other way is by using the undocumented xp_logevent procedure, which I had even published about it in the post The undocumented SQL Server extended procedures:
|
1 |
EXEC master..xp_logevent 50001, 'Teste de mensagem no Event Viewer', 'INFORMATIONAL' -- INFORMATIONAL, WARNING, ERROR |
Example of use:
How to read events in Event Viewer from SQL Server
To read events in the Windows Event Viewer from SQL Server, I will share two ways to accomplish this goal, one using a Stored Procedure and one using a Table-valued function, which gives us more flexibility to work with the data.
View stpEvent_Viewer_Listar source
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
using System; using System.Data; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Diagnostics; public partial class StoredProcedures { [SqlProcedure] public static void stpEvent_Viewer_Listar(string Ds_Servidor, string Ds_Tipo_Log, SqlDateTime Dt_Inicio, SqlDateTime Dt_Fim) { var logType = Ds_Tipo_Log; var dtInicio = Dt_Inicio.Value; var dtFim = Dt_Fim.Value; using (var ev = new EventLog(logType, Ds_Servidor)) { var lastLogToShow = ev.Entries.Count; if (lastLogToShow <= 0) return; var pipe = SqlContext.Pipe; var colunas = new SqlMetaData[5]; colunas[0] = new SqlMetaData("Id_Evento", SqlDbType.BigInt); colunas[1] = new SqlMetaData("Fl_Evento", SqlDbType.NVarChar, 1024); colunas[2] = new SqlMetaData("Ds_Fonte", SqlDbType.NVarChar, 1024); colunas[3] = new SqlMetaData("Ds_Messagem", SqlDbType.NVarChar, 4000); colunas[4] = new SqlMetaData("Dt_Evento", SqlDbType.DateTime); var linhaSql = new SqlDataRecord(colunas); pipe?.SendResultsStart(linhaSql); for (var i = ev.Entries.Count - 1; i >= 0; i--) { try { var currentEntry = ev.Entries[i]; if ((currentEntry.TimeGenerated >= dtInicio) && (currentEntry.TimeGenerated <= dtFim)) { linhaSql.SetSqlInt64(0, new SqlInt64(currentEntry.InstanceId)); linhaSql.SetSqlString(1, new SqlString(currentEntry.EntryType.ToString())); linhaSql.SetSqlString(2, new SqlString(currentEntry.Source)); linhaSql.SetSqlString(3, new SqlString(currentEntry.Message.Substring(0, (currentEntry.Message.Length) > 4000 ? 3999 : currentEntry.Message.Length))); linhaSql.SetSqlDateTime(4, new SqlDateTime(currentEntry.TimeGenerated)); pipe?.SendResultsRow(linhaSql); } else if (currentEntry.TimeGenerated < dtInicio) break; } catch (Exception e) { // ignore } } pipe?.SendResultsEnd(); } } }; |
View fncEvent_Viewer_Listar source code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
using System; using System.Collections; using System.Data.SqlTypes; using System.Diagnostics; public partial class UserDefinedFunctions { private class EventViewerListar { public SqlInt64 Id_Evento; public SqlDateTime Dt_Evento; public SqlString Tp_Evento; public SqlString Ds_Fonte; public SqlString Ds_Mensagem; public EventViewerListar(SqlInt64 idEvento, SqlDateTime dtEvento, SqlString tpEvento, SqlString dsFonte, SqlString dsMensagem) { Id_Evento = idEvento; Dt_Evento = dtEvento; Tp_Evento = tpEvento; Ds_Fonte = dsFonte; Ds_Mensagem = dsMensagem; } } [Microsoft.SqlServer.Server.SqlFunction( FillRowMethodName = "FillRowEventViewer", TableDefinition = "Id_Evento BIGINT, Dt_Evento DATETIME, Tp_Evento nvarchar(50), Ds_Fonte nvarchar(50), " + "Ds_Mensagem NVARCHAR(MAX)" )] public static IEnumerable fncEvent_Viewer_Listar(string Ds_Servidor, string Ds_Tipo_Log, DateTime Dt_Inicio, DateTime Dt_Fim) { var eventViewerListarCollection = new ArrayList(); try { var logType = Ds_Tipo_Log; using (var ev = new EventLog(logType, Ds_Servidor)) { var lastLogToShow = ev.Entries.Count; if (lastLogToShow > 0) { for (var i = lastLogToShow - 1; i >= 0; i--) { var currentEntry = ev.Entries[i]; if ((currentEntry.TimeGenerated >= Dt_Inicio) && (currentEntry.TimeGenerated <= Dt_Fim)) { eventViewerListarCollection.Add(new EventViewerListar( currentEntry.InstanceId, currentEntry.TimeGenerated, currentEntry.EntryType.ToString(), currentEntry.Source, currentEntry.Message.Substring(0, (currentEntry.Message.Length) > 4000 ? 3999 : currentEntry.Message.Length) )); } else if (currentEntry.TimeGenerated < Dt_Inicio) break; } } } } catch (Exception e) { // ignored } return eventViewerListarCollection; } protected static void FillRowEventViewer(object objEventViewerListar, out SqlInt64 idEvento, out SqlDateTime dtEvento, out SqlString tpEvento, out SqlString dsFonte, out SqlString dsMensagem) { var eventViewerListar = (EventViewerListar)objEventViewerListar; idEvento = eventViewerListar.Id_Evento; dtEvento = eventViewerListar.Dt_Evento; tpEvento = eventViewerListar.Tp_Evento; dsFonte = eventViewerListar.Ds_Fonte; dsMensagem = eventViewerListar.Ds_Mensagem; } } |
Usage examples
Listing events using SP
Listing events using the function
That's it folks!
I hope you enjoyed this post and please do not confuse Event Viewer events with SQL Server log events .. rs
Regards and see you next post.
