Generate report to identify the occurence of certain event ID in Windows Servers

Background

IT audit findings in my organisation discovered the occurence of event ID 4625 in some of our Windows Servers. Event ID 4625 is a security event that is generated when an account fails to log on. It is important to identify what, when and how this event was generated as it may refer to a potential case of unauthorised access. Additionally, investigating such events helps in identifying local accounts of the Windows Server system that need a change of password.

Script

The following script is scheduled to run daily, hence, only events of the past 24 are considered. The functionality of the script can be listed as follows:

1. Get the list of servers
2. Connect to each server and collect event IDs according to set filters (this script could be used to capture any event ID of your choice)
3. Convert and parse event as XML to gather more details such as username, date and time, ip address etc.
4. Export the findings to an Excel spreadsheet and create pivot table to summarise the findings

Make sure to run the following command to import the excel module before running this script (one time task)

Import-Module ImportExcel

If the server is not connected to the internet as in most scenarios, you may download the package from https://github.com/dfinke/ImportExcel and place it in the C:\Windows\System32\WindowsPowerShell\v1.0\Modules and then run Import-Module ImportExcel

The beauty of this module is that you can create and edit excel files without having to install MS Excel.

 

$servers = get-content "path to server list"

#oldest event we are looking for should be 1 day older

$oldest = (get-date).adddays(-1)

$newest = get-date

$outfile = "path to excel file_$(get-date -f yyyy-MM-dd).xlsx"

#initiate empty data collection to store the events

$datacol = @()

#initiate empty data collection to store failed hosts

$errordatacol = @()

#function to create PS object that represents one event

function createerrorPSObj($hostname,$error) {

$errordata = New-Object -TypeName PSObject

$errordata | Add-Member -MemberType NoteProperty -Name Hostname -value $hostname

$errordata | Add-Member -MemberType NoteProperty -Name Error -value $error

return $errordata

}

foreach ($server in $servers) {

Write-Host $server

#this is test to see if the server is reachable

if(Get-WmiObject -ComputerName $server win32_computersystem -ErrorAction Continue) {

} else {

#if the test fails, insert in error data collection

$errordatacol += createerrorPSObj $server $Error[0]

Continue

}

#get events per server

$events = Get-WinEvent -ComputerName $server -FilterHashtable @{LogName='Security'; ID='4625'; Starttime=$oldest; endtime=$newest} -ErrorAction Continue

if($events) {

foreach($event in $events) {

$data = New-Object -TypeName PSObject

#capture each event in xml format

$eventXML = [xml]$event.ToXml()

$data | Add-Member -MemberType NoteProperty -Name Hostname -value $server

$EventID = $event.id

$data | Add-Member -MemberType NoteProperty -Name EventID -value $EventID

$ProcessName = $eventXML.Event.EventData.Data[18].'#text'

$data | Add-Member -MemberType NoteProperty -Name ProcessName -value $ProcessName

$Workstation = $eventXML.Event.EventData.Data[13].'#text'

$data | Add-Member -MemberType NoteProperty -Name WorkstationName -value $Workstation

$EventTime = $event.timecreated

$data | Add-Member -MemberType NoteProperty -Name EventTime -value $EventTime

$LogonType = $eventXML.Event.EventData.Data[10].'#text'

$data | Add-Member -MemberType NoteProperty -Name LogonType -value $LogonType

$IpAddress = $eventXML.Event.EventData.Data[19].'#text'

$data | Add-Member -MemberType NoteProperty -Name IpAddress -value $IpAddress

$UserName = $eventXML.Event.EventData.Data[5].'#text'

$data | Add-Member -MemberType NoteProperty -Name UserName -value $UserName

#add current data object to data collection

$datacol += $data

}

}

}

#excel parameters

$ExcelParams = @{

Path = $outfile

IncludePivotTable = $true

PivotRows = 'Username','Hostname'

PivotTableName = 'pivot'

PivotData = @{'Username' = 'count'} #Pivot table for the count of username per host

Activate = $true

}

#export data collection into Excel using parameters defined above

$excel = $datacol | Export-Excel @ExcelParams

#export error data collection into Excel on a seperate worksheet

$errordatacolGDC | Export-Excel $outfile -WorksheetName 'Script Errors' -AutoSize -AutoFilter -FreezeTopRow -TitleBold

The pivot table should appear as follows: