PowerShell function to convert WMI Event Log date and time to datetime data type

PowerShell likes to hold dates and times as the DateTime data type. This enables you to do useful things with it such as maths, easily pick out bits of it (e.g. just the date ignoring the time, just the hour, etc.) and view it in a variety of different formats.

If you pull events from the Windows Event Log via Get-WmiObject you get dates/times in a different format, such as:

20140721092203.000000+060

Which won’t directly convert to a PowerShell DateTime.

So I wrote this function, which will do it for you.

function Convert-WMIDateTime($WMIDateTime){
    # WMI Event Log DateTime format is 20150625134302.000000+060 where the +060 is the time zone (BST in this case)
    $DateTimeArray = $WMIDateTime -split "\."
    $DateTimeOnly = $DateTimeArray[0] # This is the bit before the "."
    if($DateTimeArray[1] -like "*+*"){ # Is the time zone plus or minus
        $TimeZoneSymbol = "+"
    }else{
        $TimeZoneSymbol = "-"
    }
    # Split out the time zone from the zeros and convert from minutes to hours
    $WMISummerTime = ($DateTimeArray[1] -split "\$TimeZoneSymbol")[1] / 60
    # Build the formatted date/time string up ready for conversion to a datetime variable
    $FormattedDateTime = ($DateTimeOnly + $TimeZoneSymbol + $WMISummerTime)
    [datetime]::ParseExact($FormattedDateTime,"yyyyMMddHHmmssz",[System.Globalization.CultureInfo]::InvariantCulture)
}

Just pass it a WMI-formatted date/time and it’ll return a PowerShell DateTime variable. It takes account of the time zone/daylight saving information that WMI uses.

Posted in PowerShell | Tagged , , , , , , , , , | Leave a comment

PowerShell: Get downtime between a range of dates/times

This is an enhancement of my previous script which found the duration of the most recent downtime of a Windows machine. As with that script, by “downtime” I mean the time between the machine shutting down (cleanly or unexpectedly) and starting up again, as found from the System Event Log entries with IDs 6005 (startup), 6006 (normal shutdown), and 6008 (unexpected shutdown).

The script by default searches the local machine’s event log, and looks for all events. It returns a table of the downtime periods, the durations of each, whether or not each downtime period was normal (i.e. a clean shutdown) or unexpected (i.e. Windows did not shut down normally), and the total downtime from all the downtime periods found. As with the previous script, this does not take account of the downtime of the applications or services on the server, just Windows itself. The functionality loss caused by the downtime will probably be longer but would need monitoring in a much more complicated, application-specific, and probably external way.

You can specify a few optional parameters:

  • -Computer <computer name> will retrieve the data from a remote machine
  • -FromDate <datetime> will only retrieve downtime starting from the specified date and/or time. Tip: always use ISO 8601 format with Powershell, especially if you don’t live in the USA. You don’t have to specify the time if you don’t need to, 2015-06-16 will do – the time will default to midnight.
  • -ToDate <datetime> retrieves downtime up until the specified date and time.

Note that the Event Logs by default remove older events to make room for newer ones, so you may not have any start, stop (or both) events presents. The script needs at least one start and one stop event to be able to calculate the downtime and will tell you if it hasn’t found any.

Example output:

PS DFSScripts:\robin> \\rcmtech\Scripts\Robin\GetDowntimeHistory.ps1
Searching events on computer RCM01, range 1970-01-01T00:00:00 - 2015-06-19T13:35:05

Started             Stopped             Duration ShutdownType
-------             -------             -------- ------------
16/06/2015 15:05:24 16/06/2015 15:04:47 00:00:37 Normal      
16/06/2015 15:04:28 16/06/2015 15:03:04 00:01:24 Unexpected  
16/06/2015 08:09:01 16/06/2015 08:07:36 00:01:25 Unexpected  
15/06/2015 09:43:23 15/06/2015 09:42:37 00:00:46 Normal      
21/05/2015 03:40:43 21/05/2015 03:39:52 00:00:51 Normal      
19/05/2015 13:43:25 19/05/2015 13:42:39 00:00:46 Normal      
19/05/2015 07:54:38 19/05/2015 07:53:54 00:00:44 Normal      
18/05/2015 13:00:59 18/05/2015 13:00:28 00:00:31 Normal      
18/05/2015 12:51:03 18/05/2015 12:50:20 00:00:43 Normal      

Total downtime in selected period is 467 seconds

Here’s the script:

param(
    [string]$Computer=$env:COMPUTERNAME,
    [datetime]$FromDate="1970-01-01T00:00:00",
    [datetime]$ToDate = (Get-Date)
)
# Note: Please specify dates/times in either US format, or, preferably, ISO 8601 format: 2015-06-01 or 2015-06-01T13:45:00
# If dates are specified in UK format, e.g. 16/06/2015 they will be converted to US format by PowerShell and confusion will reign.

Write-Host ("Searching events on computer $Computer, range "+($FromDate | Get-Date -Format s)+" - "+($ToDate | Get-Date -Format s))

# Event IDs for EventLog:
# 6005 = The Event log service was started.
# 6006 = The Event log service was stopped.
# 6008 = The previous system shutdown at  on  was unexpected.

# Flags so we know if we've found any stop events at all (we might not, and if that's the case we can't compute the downtime)
$Found6006 = $false
$Found6008 = $false
# Create arrays for holding the event log data. We have to force the data to be added to an array even if there's only
# one result as otherwise we have potential issues trying to add a System.Diagnostics.Eventing.Reader.EventRecord (if only a single stop event of one type is found)
# to a System.Array (if multiple stop events of a different type are found). We also need to be able to step through an array later when pairing up starts and stops.
$NormalStopEvents=@()
$UnexpectedStopEvents=@()
$StartEvents = @()
try{
    # Rebuild the array in place adding any events found
    $NormalStopEvents += Get-WinEvent -FilterHashtable @{logname='System'; id=6006; starttime=$FromDate; endtime=$ToDate} -ComputerName $Computer -ErrorAction Stop
    $Found6006 = $true
}catch{ # No 6006 events found
}
try{
    $UnexpectedStopEvents += Get-WinEvent -FilterHashtable @{logname='System'; id=6008; starttime=$FromDate; endtime=$ToDate} -ComputerName $Computer -ErrorAction Stop
    $Found6008 = $true
}catch{ # No 6008 events found
}
if($Found6006 -eq $false -and $Found6008 -eq $false){
    Write-Host "Could not find any stop events (6006 or 6008) during time specified, unable to continue" -ForegroundColor Red
    return
}
$StopEvents = $NormalStopEvents + $UnexpectedStopEvents | Sort-Object -Property TimeCreated -Descending
try{
    $StartEvents += Get-WinEvent -FilterHashtable @{logname='System'; id=6005; starttime=$FromDate; endtime=$ToDate} -ComputerName $Computer -ErrorAction Stop
}catch{
    Write-Host "Could not find any start events (6005) during time specified, unable to continue" -ForegroundColor Red
    return
}
$StartEvents += $StartEvents | Sort-Object -Property TimeCreated -Descending

$Results = @()
for($i=0;$i -lt $StartEvents.Count;$i++){
    $ThisStopDateTime = $null
    if($StopEvents[$i].Id -eq 6008){
        # Last shutdown was unexpected, this event is logged as the computer starts so we
        # have to extract the date & time of the stop from the event message text
        [string]$MessageText = $StopEvents[$i].Message
        $MessageText = $MessageText.Replace("The previous system shutdown at ","")
        $MessageText = $MessageText.Replace("was unexpected.","")
        $MessageText = $MessageText.Replace("on ","")
        # There are some weird non-printable characters in the text string so now we have
        # to faff around to clean them out. if you try and convert $MessageText to a DateTime
        # without doing this it will fail, even though the format string looks correct in the ISE (it shows ? in a PowerShell window)
        $MessageText = $MessageText -replace '[^\w -]', [string]::Empty
        $Array = $MessageText.Split("")
        $MessageText = ($Array[0]+" "+$Array[1])
        # Now convert the "clean" text string to a DateTime
        $ThisStopDateTime = [datetime]::ParseExact($MessageText,"HHmmss ddMMyyyy",[System.Globalization.CultureInfo]::InvariantCulture)
        $ShutdownType = "Unexpected"
    } else {
        $ThisStopDateTime = $StopEvents[$i].TimeCreated
        $ShutdownType = "Normal"
    }

    if($ThisStopDateTime -ne $null){
        $ThisResult = New-Object System.Object
        $ThisResult | Add-Member -MemberType NoteProperty -Name "Started" -Value $StartEvents[$i].TimeCreated
        $ThisResult | Add-Member -MemberType NoteProperty -Name "Stopped" -Value $ThisStopDateTime
        $ThisResult | Add-Member -MemberType NoteProperty -Name "Duration" -Value ($StartEvents[$i].TimeCreated - $ThisStopDateTime)
        $ThisResult | Add-Member -MemberType NoteProperty -Name "ShutdownType" -Value $ShutdownType
        $Results += $ThisResult
    }
}

# Display results
$Results | Format-Table -AutoSize
foreach($Result in $Results){
    $TotalDowntime = $TotalDowntime + $Result.Duration
}
Write-Host ("Total downtime in selected period is "+$TotalDowntime.TotalSeconds+" seconds")
    
Posted in PowerShell, Windows | Tagged , , , , , , , , , , , | Leave a comment

PowerShell: Get downtime between shutdown and startup

I have a requirement to provide the amount of time that servers are unavailable for, and the way I have been asked to calculate this is to measure time between when a server shuts down and when it has started back up again. Ideally you’d extract this information from a monitoring system, but in this case a suitable one is not available.

Thus the only easy way to do this that’ll work across multiple server configurations is to read the System Event Log and look for the events that get logged when Windows shuts down (6006 from EventLog) and when it starts back up again (6005 from EventLog).

However, there is another option that needs to be accounted for too: an unexpected shutdown. In these situations, the shutdown was unexpected so Windows wasn’t able to write an event during shutdown. Instead it writes event 6008 (from EventLog) during the next startup, and specifies the date and time of the unexpected shutdown in the event message text.

Extracting this date and time should have been a case of basic text processing and conversion to a datetime variable, but either the Event Log message, or how I’m retrieving it via PowerShell, causes some strange non-printable and non-visible on screen characters to be present around the date so I had to fiddle around a bit to strip these out so that I could then convert the text string into a datetime variable in order to do the downtime calculation. If you’re interested, do a Write-Host of the $MessageText variable once it has been populated from a 6008 event and step through the text with the cursor keys: you have to press the key twice to move one character forward before the first digit of each of the numbers that make up the date. Very strange and I’ve never seen anything like this before… Interestingly, if you run Get-WinEvent from a PowerShell window (as opposed to the ISE which I normally use) you get a question mark where the “hidden” character is, e.g. ?16/?06/?2015. I’ve logged this with Microsoft as a bug.

Anyway, here’s the script, just pass it a computer name and it’ll give you the duration of the last downtime on the computer. There’s no error handling, I’ll leave that as an exercise for the reader.

param([Parameter(Mandatory=$true)] [string]$Computer="")

#Event IDs for EventLog:
# 6005 = The Event log service was started.
# 6006 = The Event log service was stopped.
# 6008 = The previous system shutdown at  on  was unexpected.

$StoppedEvents = Get-WinEvent -FilterHashtable @{logname='System'; id=6006} -ComputerName $Computer -ErrorAction SilentlyContinue
$StoppedEvents += Get-WinEvent -FilterHashtable @{logname='System'; id=6008} -ComputerName $Computer -ErrorAction SilentlyContinue
$LastStoppedEvent = $StoppedEvents | Sort-Object -Property TimeCreated -Descending | Select-Object -First 1
if($LastStoppedEvent.Id -eq 6008){
    # Last shutdown was unexpected, this event is logged as the computer starts so we
    # have to extract the date & time of the stop from the event message text
    [string]$MessageText = $LastStoppedEvent.Message
    $MessageText = $MessageText.Replace("The previous system shutdown at ","")
    $MessageText = $MessageText.Replace("was unexpected.","")
    $MessageText = $MessageText.Replace("on ","")
    # there are some weird non-printable characters in the text string so now we have
    # to faff around to clean them out. if you try and convert $MessageText to a DateTime
    # without doing this it will fail, even though the format string looks correct
    $MessageText = $MessageText -replace '[^\w -]', [string]::Empty
    $Array = $MessageText.Split("")
    $MessageText = ($Array[0]+" "+$Array[1])
    # now convert the "clean" text string to a DateTime
    $LastStopDateTime = [datetime]::ParseExact($MessageText,"HHmmss ddMMyyyy",[System.Globalization.CultureInfo]::InvariantCulture)
    Write-Host $LastStopDateTime -ForegroundColor Red
} else {
    $LastStopDateTime = $LastStoppedEvent.TimeCreated
    Write-Host ("Stopped: "+$LastStopDateTime+" (expected)") -ForegroundColor Yellow
}

$StartedEvents = Get-WinEvent -FilterHashtable @{logname='System'; id=6005} -ComputerName $Computer -ErrorAction SilentlyContinue
$LastStartedEvent = $StartedEvents | Sort-Object -Property TimeCreated -Descending | Select-Object -First 1
$LastStartDateTime = $LastStartedEvent.TimeCreated
Write-Host ("Started: "+$LastStartDateTime) -ForegroundColor Green

Write-Host ("Last downtime for $Computer lasted "+ ($LastStartDateTime-$LastStopDateTime))

I use Get-WinEvent because the filtering happens at the remote computer, which is more efficient than Get-EventLog which pulls all the events over the network and then filters them. The filter options can be found in this Scripting Guy blog post.

I then sort the events into descending order based on the time they were created, pick the most recent startup (printed to screen in green) and shutdown event (yellow for expected, red for unexpected) and subtract to get the downtime.

Note that 6005 is the first event Windows writes when it starts, and 6006 is the last it writes when it shuts down, so these are not going to accurately reflect how long the server was non-functional for from the point of view of whatever application or service runs on it, but they’re going to give some idea, especially if you have performance issues (e.g. slow disk) that are making what should be a fairly fast process take many minutes.

Note also that it does only retrieve the last downtime period, but you might find that your machine has been more than once in quick succession. Consider a PC that is waiting for a reboot following update installs but hasn’t due to a logged on user. If it experiences an expected shutdown (maybe due to power failure) and then starts back up again, it’ll no longer have a user logged on, will retry the update installs and then reboot again cleanly. It is only this last reboot that’ll be reported even though the power outage may have lasted significantly longer. I am going to work on a more advanced version of this script that’ll report on total downtime over a specified period of time.

Posted in PowerShell, Windows | Tagged , , , , , , , , , , , , | 1 Comment

Advanced Threats and the Human Factor – The Register

Notes I took from today’s The Register webcast with Proofpoint, Freeform Dynamics and Fujitsu.

  • People want to be productive so will find ways around cumbersome security processes and procedures.
  • Organisations often don’t take the time to explain why the procedures are in place and what the consequences are of ignoring them.
  • Have rules that can be bent a little and are flexible and realistic.
  • Top targeted industry is Finance.
  • Volume of messages with malicious attachments has increased 17x in the six months from Jan 2015, and is now significantly higher than URL-based attacks.
  • Top 3 most common email lures: communication notification, financial corporate, financial personal (in that order).
  • 1 in 25 malicious messages are clicked.
  • Sales, finance and supply chain staff (in that order) are three times more likely to click than IT staff.
  • Staff and management are twice as likely to click vs executives.
  • You should measure systems, user behaviour, data flows and network connections. Monitoring for exceptions to normal behaviour and traffic is key to identifying problems.
  • Attackers understand both people and technology, and don’t mind failure. They’re persistent, they only need one attack to work to breach the organisation’s security.
  • Cyber kill-chain and attacker framework, knowing this helps you mitigate against some of the techniques and processes that attackers use against you.
    • recon (linkedin, facebook, etc. give lots of info)
    • weaponise (selecting the type of attack)
    • deliver – how to deliver the attack
    • Exploit
    • Install
    • Command and Control
    • Action
  • Once access to a system has been gained, the attackers often don’t just grab data and go. Some attacks last for month.
  • Examples of attacks:
    • email received “from VP” inside the company sent to the PA of another VP. The PA then got a phone call from the “sender”, sounding authoritative and asking her to open the spreadsheet attachment and follow up urgently. The PA opened the attachment and her PC was infected. The attack was only discovered after the PA reported suspicions later after she’d had time to reflect.
    • Email was received containing a link that did different things depending on the platform it was launched from (e.g. Windows vs Android vs anything else)
  • Some malware now can detect if it has been sandboxed, e.g. if running in a virtual machine it will not trigger, so is hard to detect and diagnose how it behaves.
  • Rise in macro-based attacks in recent months. People see the “enable protected content” and know that legitimate files won’t work properly if they don’t enable it, so always do, despite the warnings.
  • People are known to be the weakest link in the security chain.
  • Intelligence aggregators (e.g. proofpoint) check data across multiple organisations to see if (e.g.) an email received in one organisation has also been received in another, or if a new outgoing connection has been detected across multiple organisations and thus could be a C&C channel. Makes management by exception more practical. Know what “normal” looks like.
  • Next generation security techniques:
    • Prepare – understand risk posture
    • Prevent – block known threats
    • Detect – detect unknown threats
    • Respond – respond to incidents
  • Plan how to respond before you need to! You need to have a process in place to prevent knee jerk reactions.
  • Don’t reply on other people/news media to tell you when there’s a problem.
  • Keep the security training/information transfer short and to the point, and keep it regular.

Links

Posted in Security | Leave a comment

PowerShell: Get SCCM Malware Detections to CSV file

Pulling detection data from dedicated Malware systems such as McAfee ePO is quite straightforward, simple, and fairly powerful.

The SCCM 2012 reports are arguably powerful, but whereas ePO has a fairly straightforward report creator built in, with SCCM you need to know how to use the (not very user-friendly for non-specialists) Microsoft Report Viewer and/or SQL Server Reporting Services to get exactly the data you want. I do not currently fall into the category of people who can write their own SSRS reports, but I can knock up an SQL query, so I decided to just pull the data direct from the SCCM SQL database.

The built-in Infected Computers report does show you which machines have had malware detected, but it groups them by computer, so you can’t easily get an overview of malware detections by date – if a machine has detected malware multiple times you have to drill into that machine to see all its detection dates. So whilst you can save the report output as CSV or Excel, it’s not good to me as I can’t get all the data in one place.

I like to be able to see detections over time, and store and analyse the results. For this I need raw data, so I wrote the following SQL query and PowerShell script that will pull the appropriate data out of SCCM and save it to a CSV file. When writing the SQL query, this SQL script was handy to find the tables containing the column references.

$SCCMSQL = "SCCMSQLServer.rcmtech.co.uk"
$SCCMDB = "CM_RCM"
$OutFile = "D:\SCCM Malware Detections.csv"

# Open connection to SCCM DB
$SCCMDBConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList "Server=$SCCMSQL;Database=$SCCMDB;Integrated Security=SSPI"
$SCCMDBConnection.Open()
# Get data
$SQLCommand = $SCCMDBConnection.CreateCommand()
$SQLSelect = "SELECT
    Computer_System_DATA.Name00 as ComputerName,
	DetectionTime,
    Users.UserName,
    Process,
    ThreatName,
    Path,
    EP_ThreatSeverities.Severity,
	EP_ThreatCategories.Category,
    CleaningAction,
    ExecutionStatus,
    ActionSuccess,
    PendingActions,
    ErrorCode,
    RemainingActions,
    LastRemainingActionsCleanTime
    FROM $SCCMDB.dbo.EP_Malware
    INNER JOIN $SCCMDB.dbo.Computer_System_DATA on EP_Malware.MachineID = Computer_System_DATA.MachineID
    INNER JOIN $SCCMDB.dbo.EP_ThreatCategories on EP_Malware.CategoryID = EP_ThreatCategories.CategoryID
    INNER JOIN $SCCMDB.dbo.EP_ThreatSeverities on EP_Malware.SeverityID = EP_ThreatSeverities.SeverityID
    INNER JOIN $SCCMDB.dbo.Users on EP_Malware.UserID = Users.UserID
    ORDER BY DetectionTime ASC"
$SQLCommand.CommandText = ($SQLSelect)
$SQLReader = $SQLCommand.ExecuteReader()
$SQLResultsTable = New-Object System.Data.DataTable
$SQLResultsTable.Load($SQLReader)
$SQLReader.Close()
$SCCMDBConnection.Close()
Write-Host ("Found "+($SQLResultsTable | Measure-Object -Line).Lines+" results")
$SQLResultsTable | Export-Csv -Path $OutFile -NoTypeInformation -Force
Posted in PowerShell | Tagged , , , , , , , , , , | Leave a comment

PowerShell: Get AD attributes

A while back I posted a script to monitor Active Directory LDAP response times. As part of this I had a chunk of code that not only did an LDAP lookup, but also pulled all the AD attributes into a PowerShell object. Here’s the code:

$User = $env:USERNAME
# assume that the DC is in the same domain as the user running the test
$DC = $env:LOGONSERVER.Replace("\\","")
$Root = [ADSI] ("LDAP://"+$DC+"."+$env:USERDNSDOMAIN)

$Searcher = New-Object System.DirectoryServices.DirectorySearcher $Root
$Searcher.Filter = "(cn=$User)"
# run the query
$Container = $Searcher.FindAll()

[System.Collections.Arraylist]$Names = $Container.Properties.PropertyNames
[System.Collections.Arraylist]$Properties = $Container.Properties.Values
$Obj = New-Object System.Object
for ($i = 0; $i -lt $Names.Count)
	{
		$Obj | Add-Member -type NoteProperty -Name $($Names[$i]) -Value $($Properties[$i])
		$i++
	}
$Obj.pwdlastset = [System.DateTime]::FromFileTime($Obj.pwdlastset)
$Obj.lastlogontimestamp = [System.DateTime]::FromFileTime($Obj.lastlogontimestamp)

$Obj
Posted in PowerShell | Tagged , , , , , , , | Leave a comment