SQL Server – Listowanie ostatnich N zdarzeń z dziennika zdarzeń (CLR)

Niedawno pisałem o tym, jak można czytać dzienniki zdarzeń systemu Windows za pomocą funkcji napisanej dla SQL Servera w CLR (patrz tutaj). Jednak podana funkcja ma podstawową wadę – czyta cały dziennik, a to oczywiście może trwaaaaaać :-)

Dlatego spróbowałem nieco zmienić kod C#, by dawało się określić, ile ostatnich wpisów ze wskazanego dziennika chcemy przeczytać. Powstało mi na razie coś takiego (pewnie za moment będzie ewoluować):

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Diagnostics;

public partial class UserDefinedFunctions
{
  [Microsoft.SqlServer.Server.SqlFunction(
      Name = "ufn_clr_GetEventLog",
      FillRowMethodName = "FillEventLogRow",
      TableDefinition = "EntryType nvarchar(50), " +
                        "TimeWritten datetime, " +
                        "Source nvarchar(4000), " +
                        "InstanceId bigint, " +
                        "Category nvarchar(255), " +
                        "Message nvarchar(max)"
  )]
  public static IEnumerable ReadLog(
    SqlString LogName,
    SqlString MachineName,
    SqlInt32 HowMany
  )
  {
    EventLog log = new EventLog(LogName.Value, MachineName.Value);
    int size =
      (log.Entries.Count - HowMany.Value) >= 0 ?
      HowMany.Value :
      log.Entries.Count;
    EventLogEntry[] entries = new EventLogEntry[size];
    int index =
      (log.Entries.Count - HowMany.Value) >= 0 ?
      (log.Entries.Count - HowMany.Value) :
      0;
    for (int i = index; i <= log.Entries.Count - 1; i++)
    {
      entries[i-index] = log.Entries[i];
    }
    return entries;
  } 

  public static void FillEventLogRow(
    Object Obj,
    out SqlString EntryType,
    out SqlDateTime TimeWritten,
    out SqlString Source,
    out SqlInt64 InstanceId,
    out SqlString Category,
    out SqlString Message
  )
  {
    EventLogEntry entry = (EventLogEntry)Obj;
    EntryType = entry.EntryType.ToString();
    TimeWritten = new SqlDateTime(entry.TimeWritten);
    Source = entry.Source;
    InstanceId = new SqlInt64(entry.InstanceId);
    Category = entry.Category;
    Message = entry.Message;
  }
};

Doszedł trzeci parametr funkcji – HowMany, który określa, ile ostatnich wpisów chcemy zwrócić. Reszta to prosta matematyka i liczenie indeksów tablic (mam nadzieję, że nic nie pokpiłem). Zapewne da się to napisać prościej, ale nie jestem programistą .NET, więc poradziłem sobie, jak umiałem :-) Spróbuję jeszcze wydumać, jak zwracać zdarzenia pomiędzy zadanymi datami lub od zadanej daty, ale to już będzie dla mnie nie lada wyzwanie ;-)

Bez projektu Database Project po skompilowaniu do pliku .dll i wczytaniu owego pliku jako assembly (polecenie CREATE ASSEMBLY z opcją PERMISSION_SET ustawioną na UNSAFE) do bazy danych funkcję tworzymy tak:

CREATE FUNCTION [dbo].[ufn_clr_GetEventLog](
  @LogName [nvarchar](4000),
  @MachineName [nvarchar](4000),
  @HowMany [int])
RETURNS  TABLE (
    [EntryType] [nvarchar](50) NULL,
    [TimeWritten] [datetime] NULL,
    [Source] [nvarchar](4000) NULL,
    [InstanceId] [bigint] NULL,
    [Category] [nvarchar](255) NULL,
    [Message] [nvarchar](max) NULL
)
AS
EXTERNAL NAME [DBAToolbox].[UserDefinedFunctions].[ReadLog];
GO

DBAToolbox to nazwa assembly podana w poleceniu CREATE ASSEMBLY.

Przykładowe wywołanie funkcji:

SELECT * FROM dbo.ufn_clr_GetEventLog('System','MojSerwer', 2000);

Przyjemnego przeglądania dzienników życzę :-)

[EDYCJA | 2010-07-21]
A jeżeli chesz posortować rekordy zwracane przez funkcję ufn_clr_GetEventLog malejąco po dacie (od najświeższych wpisów do najstarszych), zastąp linijkę:

entries[i - index] = log.Entries[i];

odpowiednikiem:

entries[size - 1 - i + index] = log.Entries[i];

SQL Server – Listowanie dzienników zdarzeń Windows (CLR)

Jeden z czytelników mojego bloga (Michał, pozdrawiam) napisał mi w mailu, że chciałby mieć funkcję w SQL Server dzięki której mógłby przeglądać dzienniki zdarzeń Windows. Co prawda w Management Studio można przeglądać dzienniki zdarzeń Windows razem z logiem samego SQL Servera. Ale jeżeli ktoś chce działać kodem T-SQL lub móc z jakichś powodów przerzucać dane z dzienników zdarzeń do tabel w bazach danych, przydałaby się funkcja, która umożliwi dostęp do wybranego dziennika na wybranej maszynie.

Kod takiej funkcji może wyglądać tak:

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Diagnostics;
public partial class UserDefinedFunctions
{
  [Microsoft.SqlServer.Server.SqlFunction(
      Name = "ufn_clr_GetEventLog",
      FillRowMethodName = "FillEventLogRow",
      TableDefinition = "EntryType nvarchar(50), " +
                        "TimeWritten datetime, " +
                        "Source nvarchar(4000), " +
                        "InstanceId bigint, " +
                        "Category nvarchar(255), " +
                        "Message nvarchar(max)"
  )]
  public static IEnumerable ReadLog(SqlString LogName, SqlString MachineName)
  {
    EventLog log = new EventLog();
    return new EventLog(LogName.Value, MachineName.Value).Entries;
  }
  public static void FillEventLogRow(
    Object Obj,
    out SqlString EntryType,
    out SqlDateTime TimeWritten,
    out SqlString Source,
    out SqlInt64 InstanceId,
    out SqlString Category,
    out SqlString Message
  )
  {
    EventLogEntry entry = (EventLogEntry)Obj;
    EntryType = entry.EntryType.ToString();
    TimeWritten = new SqlDateTime(entry.TimeWritten);
    Source = entry.Source;
    InstanceId = new SqlInt64(entry.InstanceId);
    Category = entry.Category;
    Message = entry.Message;
  }
};

 Szybki komentarz do kodu:

• jest to klasyczna funkcja tabelaryczna CLR (zwraca kolekcję zgodną z interfejsem IEnumerable),
• w atrybucie SqlFunction definiujemy schemat tabeli zwracanej przez funkcji,
• metoda FillEventLogRow jest typowym „wypełniaczem” pojedynczego wiersza kolekcji zwracanej przez funkcję.

Kod wklejamy w Visual Studio do pliku .cs w projekcie opartym o szablon Database Project (C#). Wrzucamy assembly (PERMISSION_SET = UNSAFE)  i funkcję do bazy danych (w Visiaul Studio w menu głównym kliknij Build – Deploy <nazwa_projektu>). Wywołanie tej funkcji może wyglądać tak (oczywiście, tu trzeba zadbać, by konto Windows, w kontekście którego działa SQL Server, miało możliwość podlgądania wskazanego dziennika zdarzeń):

SELECT * FROM dbo.ufn_clr_GetEventLog('Application', 'MojSerwer');

Przykładowy wynik:

Oczywiście, nie polecam za często uruchamiać tej funkcji na logach zawierających dużo wpisów. Lepiej z jej pomocą zaimportować wpisy zdarzeń do bazy danych do zaindeksowanej tabeli i później już zapytaniami filtrować, listować, grupować i co tam jeszcze przychodzi komu do głowy.

Uff, dwa posty w ciągu jednego dnia? Chyba oszalałem ;-) Obiecuję już tak nie robić więcej. I chwilowo dość pisania o CLR. Miłego weekendu.