Windows Event Log writes to SQL Server and POWERBI statistical analysis

Here I have prepared 2 systems, a Windows Server R2 domain control server DC01, a SQL on CentOS7 SQL database server

First I use SQL Manager Studio to connect to the SQL database server to create a database that needs to hold the Windows forwarding event log "Eventcollections"

CREATE DATABASE eventcollections

GO

Use Eventcollections

GO

--The table name loosely relates to the name of my Win Event Subscription name

CREATE TABLE [dbo]. [Generalevents] (

[ID] [int] NULL,

[Leveldisplayname] [varchar] (255) NULL,

[LogName] [varchar] (255) NULL,

[MachineName] [varchar] (255) NULL,

[Message] [varchar] (max) Null

[ProviderName] [varchar] (255) NULL,

[RecordID] [bigint] Null

[Taskdisplayname] [varchar] (255) NULL,

[Timecreated] [smalldatetime] Null

)

--Create Unique Clustered Index with Ignore_dupe_key=on to avoid duplicates in Sqlbulk imports

CREATE UNIQUE CLUSTERED INDEX [Clusteredindex-eventcombo] on [dbo]. [Generalevents]

(

[RecordID] Asc

[MachineName] Asc

[LogName] Asc

) with (Ignore_dup_key = ON)

GO

To avoid duplication in the subsequent hourly import of log data, create a unique clustered index on recordid,machinename and logname using Ignore_dupe_key=on

Next go back to the DC Server configuration Event Service

First, you need to configure WinRM to display the available listeners

WinRM e Winrm/config/listener

Perform winrm get Winrm/config

Check

Allowremoteaccess = True

In the log source server (we only have a DC server, use this as both a source and a collection log server) to add the network Service to the event log readers Group

Then execute the following command on the log source server and the collection log server:

wevtutil sl Security/ca:o:bag:syd: (a;;0 xf0005;;; SY) (a;;0 X5;;; BA) (a;;0 x1;;; s-1-5-32-573) (a;;0 x1;;; S-1-5-20)

Next Open Event Viewer, click Subscribe, this is the prompt, whether to enable the Windows Event Collector service, click "Yes"

The HTTP port used between forwarding is 5985

Then create a new subscription, specify the computers that need to be collected, enter DC01 here

Select which logs to subscribe to, and here I choose System

Select the event level collected

In advanced, specify the account that collects the logs as the domain administrator account, and then determine

Click on user name password to enter

Normal: Every 15 minutes

Minimized bandwidth: every 6 hours

Minimized delay: every 30 seconds

Are you sure

This creates a subscription to the good one collection system log.

Create a subscription to the security log in the same way

If you want to execute the audit log of the command, you can turn on Group Policy in the following 2 locations and then view it through event ID4688

Computer Configuration > Policies > Windows Settings > Security Settings > Advanced Audit configuration > Detailed tracking > Audit creation Process

Administrative Templates \ System \ Audit process created \ include command line during event creation

Note: Microsoft does not recommend that you permanently enable command-line auditing. When this feature is enabled, any user who reads access to Windows security events will be able to read the command-line arguments of any successfully created process. Keep in mind that command-line commands may contain confidential information, including passwords and other user data

The security and system logs for our subscription appear in the Event Viewer's forwarded event after waiting 15 minutes.

Finally I execute the following PowerShell command on the DC to write the log of the forwarded event to SQL

• If SQL is Windows and domains, you can log in with integrated authentication and execute the following script

# While this script was intended to run in an hourly basis, the filter is set for going back minutes.

# This allows the script to run for 5 minutes without any missing any events. Because We setup the

# table using the Ignore_dupe_key = ON, duplicate entries is ignored in the database.

$xml = @ '

<QueryList>

<query id= "0" path= "forwardedevents" >

<select path= "forwardedevents" >*[system[timecreated[timediff (@SystemTime) &lt;= 3900000]]]</select >

</Query>

</QueryList>

‘@

$events = Get-winevent-filterxml $xml | Select-object ID, Leveldisplayname, LogName, MachineName, Message, ProviderName, RecordID, Taskdisplayname, timecreated

$connectionString = "Data source=sqlserver;integrated security=true;initial catalog=eventcollections;"

$bulkCopy = New-object ("Data.SqlClient.SqlBulkCopy") $connectionString

$bulkCopy. DestinationTableName = "Generalevents"

$dt = New-object "System.Data.DataTable"

# Build the DataTable

$cols = $events | Select-first 1 | Get-member-membertype Noteproperty | Select-expand Name

foreach ($col in $cols) {$null = $dt. Columns.Add ($col)}

foreach ($event in $events)

{

$row = $dt. NewRow ()

foreach ($col in $cols) {$row. Item ($col) = $event. $col}

$dt. Rows.Add ($row)

}

# Write to the database!

$bulkCopy. WriteToServer ($DT)

• If you are logged in with the SA account, do the following:

$xml = @ '

<QueryList>

<query id= "0" path= "forwardedevents" >

<select path= "forwardedevents" >*[system[timecreated[timediff (@SystemTime) &lt;= 3900000]]]</select >

</Query>

</QueryList>

‘@

$events = Get-winevent-filterxml $xml | Select-object ID, Leveldisplayname, LogName, MachineName, Message, ProviderName, RecordID, Taskdisplayname, timecreated

$connectionString = "Data source=sqlserver;user id=sa; [email protected];initial catalog=eventcollections;]

$bulkCopy = New-object ("Data.SqlClient.SqlBulkCopy") $connectionString

$bulkCopy. DestinationTableName = "Generalevents"

$dt = New-object "System.Data.DataTable"

# Build the DataTable

$cols = $events | Select-first 1 | Get-member-membertype Noteproperty | Select-expand Name

foreach ($col in $cols) {$null = $dt. Columns.Add ($col)}

foreach ($event in $events)

{

$row = $dt. NewRow ()

foreach ($col in $cols) {$row. Item ($col) = $event. $col}

$dt. Rows.Add ($row)

}

# Write to the database!

$bulkCopy. WriteToServer ($DT)

The above paragraph:

<QueryList>

<query id= "0" path= "forwardedevents" >

<select path= "forwardedevents" >*[system[timecreated[timediff (@SystemTime) &lt;= 3900000]]]</select >

</Query>

</QueryList>

Filter current log taken from forwarded event

XML content

After execution can go to SQL to check whether the log has been written into SQL

SELECT * FROM Generalevents

You can see that the log was successfully written to SQL.

Finally, it's a Windows scheduled task that automatically executes the PowerShell scripts above every 1 hours.

Save the successfully executed script above to the PS1 file and cut the file into the C packing directory

Open Task Scheduler and create a basic task

Next

Choose Daily

Next

Start the program

Select the startup path for PowerShell in the program C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Parameter add-command "." C:\event-into-sql.ps1 ' "

When you tick "but click Finish", open the dialog box for this task's properties and complete

Set the account to perform the scheduled task, and the permissions

Modify the daily as shown in the trigger

OK, create complete

We're done here. Now that the event log is written to SQL, you can use Powerbi desktop to read the SQL data for the event log analysis, such as:

Windows Event Log writes to SQL Server and POWERBI statistical analysis