≡ Menu

Transforming Event Log Data

Several months ago I described a solution for Delegated SQL Server Administration with Powershell. In the solution, the SqlProxy module audits all security administration activity to a custom Windows Event log. In this blog post, I’ll described a process to transform and incrementally load the audit data into a SQL Server table for reporting purposes.

Writing to the Event Log

First a quick review of how  SqlProxy module writes messages to the event log. This is important because as we’ll see in a moment, how the message is constructed helps in extracting Event log data. In the SqlProxy module I use a standard template in each function for logging messages to the Event log:

$PSUserName = $PSSenderInfo.UserInfo.Identity.Name
$logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"})
write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage

The message is constructed using several built-in variables written as key/value pairs.

  1. The $PSSenderInfo variable is available inside of remote session and returns information about the user who started the PSSession. Since I’m using runas credentials I’ll grab the name of the person who is connected.
  2. $psBoundParameters contains a hashtable of the parameters and their values for the current function.
  3. This code may look a little odd, $eventID.”$($myinvocation.mycommand.name)”. I created hashtable called $eventid in the SqlProxy module to translate a function name from a an EventId. Since $myinvocation.mycommand.name returns the function name I’ll use this as the hashtable key as shown below:
$EventID = @{
"Add-SqlDatabaseRoleMember"=0
"Add-SqlLogin"=1
"Add-SqlServerRoleMember"=2
"Add-SqlUser"=3
"Remove-SqlDatabaseRoleMember"=4
"Remove-SqlLogin"=5
"Remove-SqlServerRoleMember"=6
"Remove-SqlUser"=7
"Rename-SqlLogin"=8
"Set-SqlLogin"=9
"Set-SqlLoginDefaultDatabase"=10
}

The write-sqlproxlog function is just a wrapper around write-eventlog as follows:

 

function Write-SqlProxyLog
{
    param(
    [Parameter(Position=0, Mandatory=$true)] $EventID,
    [Parameter(Position=1, Mandatory=$true)] $Message,
    [Parameter(Position=2, Mandatory=$false)] $EntryType='SuccessAudit'
    )
 
    write-eventlog -logname SqlProxy -source SqlProxy -eventID $eventID -message $message -EntryType $EntryType
 
} #Write-SqlProxyLog

A typical event log entry will look like this:

SqlProxEvtLog

Extracting Data from the Eventlog

In order to load the Eventlog data into a SQL Server table I created a module called SqlTools which is collection of functions I use frequently for querying and loading data. I’ve posted the module here.

The initial load script as shown below makes use of SqlTools module:

import-module SqlTools
 
$ComputerName = 'Z001'
$ServerInstance = 'Z002sql1'
$Database = 'SqlProxy'
 
$dt = Get-EventLog -LogName SqlProxy -ComputerName $ComputerName -EntryType 'SuccessAudit' | % { $ht = ($_.Message -replace "\","/") |
    ConvertFrom-StringData; $xml = new-object psobject -Property $ht | ConvertTo-Xml -NoTypeInformation -As String;
    new-object psobject -Property @{'Index' = $_.Index; 'TimeGenerated'=$_.TimeGenerated;
    'EventId'=$_.EventId; 'MessageXml'=$xml} } | Out-DataTable
 
Add-SqlTable -ServerInstance $ServerInstance -Database $Database -TableName 'SqlProxyLog' -DataTable $dt -AsScript | clip

At this point I’ll paste the T-SQL script into SSMS, modify column data types, null/not null, add primary key and finally create the table:

 

CREATE TABLE [dbo].[SqlProxyLog](
	[EventId] [int] NOT NULL,
	[TimeGenerated] [datetime] NOT NULL,
	[MessageXml] [xml] NOT NULL,
	[Index] [int] NOT NULL,
 CONSTRAINT [PK_SqlProxyLog] PRIMARY KEY CLUSTERED
(
	[TimeGenerated] ASC,
	[Index] ASC
)
)

Then I’ll return to Powershell to execute the write-datatable function:

Write-DataTable -ServerInstance $ServerInstance -Database $Database -TableName 'SqlProxyLog' -Data $dt

In order to incrementally load only new events, I’ll modify the get-sqlproxylog.ps1 to first grab the max timegenerated or 1900-01-01 if its null and then use value for the -After param of the Get-Eventlog cmdlet:

import-module SqlTools
 
$ComputerName = 'Z001'
$ServerInstance = 'Z002sql1'
$Database = 'SqlProxy'
 
$query = "SELECT ISNULL(MAX(TimeGenerated),'1900-01-01') AS TimeGenerated FROM dbo.SqlProxyLog"
$maxDtm = invoke-sqlcmd2 -ServerInstance $ServerInstance -Database $Database -Query $query | select -ExpandProperty TimeGenerated
 
$dt = Get-EventLog -LogName SqlProxy -ComputerName $ComputerName -EntryType 'SuccessAudit' -After $maxDtm | % { $ht = ($_.Message -replace "\","/") |
    ConvertFrom-StringData; $xml = new-object psobject -Property $ht | ConvertTo-Xml -NoTypeInformation -As String;
    new-object psobject -Property @{'Index' = $_.Index; 'TimeGenerated'=$_.TimeGenerated;
    'EventId'=$_.EventId; 'MessageXml'=$xml} } | Out-DataTable
 
if ($dt) {
    Write-DataTable -ServerInstance $ServerInstance -Database $Database -TableName 'SqlProxyLog' -Data $dt
}

Some interesting points about this script:

  • Since the message data is stored as key/value pairs, the built-in ConvertFrom-StringData cmdlet is used to create the hashtable $ht
  • The hashtable is then used to create a psobject
  • The psobject is converted into XML using ConverTo-Xml.

One minor issue I ran into is with unenclosed backslashes. If backslashes are enclosed in quotes its fine, if not it causes an error with ConvertFrom-StringData, so I replace them with forward slashes.

Data Is Loaded, Now What?

After I’ve loaded the data I’ll use XQuery to shred the message XML column into a relational data set. I created a function and view in SQL Server for this purpose:

CREATE FUNCTION [dbo].[ufn_GetEventMessage] (@MessageXml XML)
RETURNS @Message TABLE
(
	 ChangeOrder VARCHAR(128) NULL
	,dbname  VARCHAR(128) NULL
	,PSUserName   VARCHAR(255) NULL
	,name   VARCHAR(128) NULL
	,rolename   VARCHAR(128) NULL
	,[Description]   VARCHAR(128) NULL
	,sqlserver   VARCHAR(128) NULL
	,loginame   VARCHAR(128) NULL
	,[login]   VARCHAR(128) NULL
	,DefaultDatabase VARCHAR(128) NULL
)
AS
BEGIN
	INSERT @Message (ChangeOrder,dbname,PSUserName,name,rolename,Description,sqlserver,loginame,[login],DefaultDatabase)
	SELECT
	 Objects.Object.query('Property[@Name="ChangeOrder"]').value('.', 'varchar(128)') AS ChangeOrder
	,Objects.Object.query('Property[@Name="dbname"]').value('.', 'varchar(128)') AS dbname
	,Objects.Object.query('Property[@Name="PSUserName"]').value('.', 'varchar(128)') AS PSUserName
	,Objects.Object.query('Property[@Name="name"]').value('.', 'varchar(128)') AS name
	,Objects.Object.query('Property[@Name="rolename"]').value('.', 'varchar(128)') AS rolename
	,Objects.Object.query('Property[@Name="Description"]').value('.', 'varchar(128)') AS [Description]
	,Objects.Object.query('Property[@Name="sqlserver"]').value('.', 'varchar(128)') AS sqlserver
	,Objects.Object.query('Property[@Name="loginame"]').value('.', 'varchar(128)') AS loginame
	,Objects.Object.query('Property[@Name="login"]').value('.', 'varchar(128)') AS [login]
	,Objects.Object.query('Property[@Name="DefaultDatabase"]').value('.', 'varchar(128)') AS DefaultDatabase
	FROM @MessageXml.nodes('/Objects/Object') AS Objects(Object)
RETURN
END;
GO
 
CREATE VIEW [dbo].[vw_SqlProxyLog]
AS
SELECT l.*, m.*
FROM dbo.SqlProxyLog l
CROSS APPLY dbo.ufn_GetEventMessage(l.MessageXml) m
GO

The function requires I define all the possible attributes as columns. Here’s an example of parsing a typical message:

DECLARE @MessageXml XML
SET @MessageXml =
'<Objects><Object><Property Name="DefaultDatabase">master</Property>
<Property Name="PasswordPolicyEnforced">False</Property>
<Property Name="Description" />
<Property Name="sqlserver">[Z002/SQL1]</Property>
<Property Name="password">System.Security.SecureString</Property>
<Property Name="name">test3</Property>
<Property Name="PasswordExpirationEnabled">False</Property>
<Property Name="ChangeOrder">1234</Property>
<Property Name="logintype">SqlLogin</Property>
<Property Name="PSUserName">Z002/u00</Property>
</Object></Objects>'
 
SELECT * FROM dbo.ufn_GetEventMessage(@MessageXMl)
ChangeOrder PSUserName name sqlserver DefaultDatabase
1234 Z002/u00 test3 [Z002/SQL1] master

Finally I’ll add reference table for the SqlProxyEventType:

CREATE TABLE [dbo].[SqlProxyEventType](
	[EventName] [varchar](50) NOT NULL,
	[EventId] [smallint] NOT NULL,
 CONSTRAINT [PK_SqlProxyEventType] PRIMARY KEY CLUSTERED
(
	[EventId] ASC
)
);
 
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Add-SqlDatabaseRoleMember', 0)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Add-SqlLogin', 1)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Add-SqlServerRoleMember', 2)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Add-SqlUser', 3)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Remove-SqlDatabaseRoleMember', 4)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Remove-SqlLogin', 5)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Remove-SqlServerRoleMember', 6)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Remove-SqlUser', 7)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Rename-SqlLogin', 8)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Set-SqlLogin', 9)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Set-SqlLoginDefaultDatabase', 10)

The last thing I’ll do is setup a schedule SQL Agent job to run the get-sqlproxylog.ps1 script:

–Setup SQL Job with CmdExec Job Step:

–C:WindowsSystem32WindowsPowerShellv1.0powershell.exe -NoProfile -Command c:scriptsget-sqlproxylog.ps1

Summary

This post demonstrated extracting information from a specially crafted message in an event log and loading the data into a SQL Server table. Its kind of interesting to think about how easy it is to transform data from one thing to the next using Powershell. In this case the data was converted six times:

  1. To an Event log message data stored as key/value pairs using Write-Eventlog/Get-Eventlog
  2. To a hashtable using ConvertFrom-StringData
  3. To a psobject using New-Object -property hashtable
  4. To a DataTable using Out-DataTable
  5. Into a SQL Server table using Write-DataTable
  6. Out of a SQL Server table XML column to relational data set using XQuery

That said, next time I should probably just write the damn thing directly to a database to begin with.

Comments on this entry are closed.

  • Paul March 28, 2012, 4:20 am

    It would be worthwhile here to comment on the consideration that there is an event trigger available to do all of this and more in Windows 2008. It just seems that everyone is trying to find a use for the OTT Powershell API 🙂

    • Chad Miller March 28, 2012, 8:17 am

      Sure there are Event Log triggers in Windows 2008, but it that doesn’t handle writing Event Log data to a SQL Server table which is the whole point of the blog post. Nor does it handle parsing out a specially crafted message into something more meaningful.

%d bloggers like this: