Event log script help.

PowerShell Help
1

Hi,
I’m having a bit of problem with 3 scripts that weren’t written by me, but by a colleague who has now left the company. I can understand what’s happening to a degree but I don’t know how to alter them without running into difficulties and lacking the knowledge to fix it.

Basically, all 3 scripts run daily and append a .csv file each day. At the end of the month another script picks them up and emails the .csv to specific users. Something isn’t working correctly within the scripts. For some reason over the last 2 months (May & June) duplicate entries are appearing in the script. Now I managed to do a manual run in May and thought I’d kept notes on what I’d done, but now I can’t find these note.

What I need to do is be able to run these scripts within a certain date range, for example 1st of June to the 30th of June. I’ve tried using the “-after” and “before” but this is just not working out for me. I think I would eventually get there but I need to the collect these logs with some urgency.
Some help and guidance would be much appreciated.

I’ll post each script individually to keep the post smaller.

Script 1:
[b]#

DESCRIPTION: Getting Windows SQL Event 510 from the last 24 hours

Logon Type - Description

Event ID: 510 Eventtype: LOGON *

#Global Variables

$server = Get-Content “E:\scripts\Event_Logs\scripts\servers_sql.txt”

#Do it for each server in list above
ForEach ($computername in $server)

{
#Sub Global Variables
$Date = [DateTime]::Now.AddHours(-24)

$Date = get-date -format ‘MMMM-yyyy’
$Path = "E:\scripts\Event_Logs\logs\SQL_Audit"
$Filename = “$Computername-SQL-Logon-Audit-$date.csv”
$Filepath = $path + $filename
$eventList = @()

#Starting to get Eventlog & write Event Log to CSV

$event = Get-EventLog -computername $Computername “Application” -After $Date `
| Where -FilterScript {$.EventID -eq 510 -And $.message -Match “EventType: LOGON*”}

$event | export-csv $filepath -NoTypeInformation -append
}[/b]

2

First, you set the variable $Date to “24 hours before now” :

$Date = [DateTime]::Now.AddHours(-24)

Then, you set the same variable $Date to another value which means “right now”

 $Date = get-date -format 'MMMM-yyyy'

Get-Date means get a DateTime object for right now :

 PS C:\> Get-Date

08 July 2014 12:36:05

The “-format 'MMMM-yyyy” at the end is just formatting the object representing “right now”
So, when you run :

 Get-EventLog -computername $Computername "Application" -After $Date

You are asking for the events which occurred after “right now” , that’s why you get nothing.

So, you need to remove the line :

 $Date = get-date -format 'MMMM-yyyy'
3

Hi Mathieu,

Thanks for your reply. I had noticed the 2 variables that were the same, but as the script had been working I decided not to mess with it. I’ve changed the variables and I’m currently doing to some testing between other work.

Do you know how I can have this script run within a date range, for example 01 June 2014 - 30 June 2014. I’ll be moving on to that next but If I can get a head start it will help.

Regards,

John.

4

Thanks to the parameters -After and -Before of the cmdlet Get-EventLog, it’s not difficult.

Define the variables that you are going to feed to -After and -Before :

 [datetime]$StartDate = "06/01/2014" 
[datetime]$EndDate = "06/30/2014"

The [DateTime] before the variable ensures that the variable is considered as a DateTime object, instead of a string.
We need to do that because both -After and -Before expect an object of the type “DateTime”.

If you want to double-check the type of any object, just pipe it to Get-Member and look at the beginning of the output :

C:\ > $StartDate | Get-Member

   TypeName: System.DateTime

Now, in your script, the assignment of $event should be like this :

$event = Get-EventLog -computername $Computername "Application" -After $StartDate -Before $EndDate `
| Where -FilterScript {$_.EventID -eq 510 -And $_.message -Match "EventType: LOGON*"}

But I guess that 6 months from now, you won’t care about the events occurred in June, so it may be too static.
If you want to get the events which occurred from the 1st day of last month to the last day of last month (regardless of which month we are in) :

$StartDate = (Get-Date -Day 1).AddMonths(-1)
$EndDate = (Get-Date -Day 1).AddDays(-1)

Quick explanation for $StartDate :
“Get-Date” : gives the current date.
“-Day 1” : changes the day of the month to 1.
“.AddMonths(-1)” : substracts one month from the current month
We don’t need to add [DateTime] before the variable assignments because the cmdlet Get-Date gives us a DateTime object by default.

5

Mathieu,

Thank you! From what you explained I have been able to run the other scripts too. I didn’t understand using [datetime] until you explained, it makes perfect sense when explained. I used ‘[datetime]’ after the ‘=’ in the variable for example, ‘$EndDate = [datetime] “06/30/2014”’. Does doing it your way ([datetime]$EndDate = “06/30/2014”) make any difference?

I think I found out why I was having trouble running the scripts remotely from my laptop. I’m based in the UK and the date layout is different for the US (where the servers are), I think that perhaps there was some confusion with the dates? I didn’t get any errors to say that was the case. Also the ‘-append’ parameter caused an error when doing a single run for a date range.

Once again thanks for your help and explain everything.

6

Powershell has the ability to manage culture. See: http://blogs.technet.com/b/heyscriptingguy/archive/2011/08/25/use-culture-information-in-powershell-to-format-dates.aspx

If you are doing comparisons on say a french, japanese and us server that all have different cultures set, it would probably be easier to use UTC (Greenwich Mean Time) to do the comparisons.

7

Thanks Rob, that gives me something else to work with. I often run my scripts from servers in the US to save any problems with time zones but I’ll now practise using the culture option.

8

Hi Again,

I’m still having a lot of trouble with one of these scripts. Now the strange thing is even though the $date variable is used twice this script has been working fine for over a year. It takes approximately 30 minutes to complete. However, when I try to run it from my laptop it just runs and runs (It shouldn’t be a priv’s issue as I’m sys admin). I suspected it was a date format issue like the problem I had before, so I’ve also run it on another server in the same data centre (virtual), using the same date format, and the script does complete but takes hours.

Could anyone help me with understanding this script? I can see it’s checking the security log for event id 4624 and has some further ‘and’ requirements. The ‘for-each’ part I don’t fully understand, but it looks like this is create the layout/formatting for the .csv file. I’m not really getting the use of the variable $eventlist = @(), I thinking this some kind of array that’s piecing together the .csv?

Any help welcome. On a side note, when I’m adding a script into a post is there a correct way to do it(good practise if you like)?

Here’s the script:

DESCRIPTION: Getting Windows Security Event 4624 from the last 24 hours

Logon Type - Description

2 - Interactive (logon at keyboard and screen of system)

3 - Network (i.e. connection to shared folder on this computer from elsewhere on network)

4 - Batch (i.e. scheduled task)

5 - Service (Service startup)

7 - Unlock (i.e. unnattended workstation with password protected screen saver)

8 - NetworkCleartext (Logon with credentials sent in the clear text. Most often indicates a logon to IIS with “basic authentication”) See this article for more information.

9 - NewCredentials such as with RunAs or mapping a network drive with alternate credentials. This logon type does not seem to show up in any events. If you want to track users attempting to logon with alternate credentials see 4648.

10 - RemoteInteractive (Terminal Services, Remote Desktop or Remote Assistance)

11 - CachedInteractive (logon with cached domain credentials such as when logging on to a laptop when away from the network)

Event ID: 4624

Do not look for user names ReplacementStrings[5] : –> example: -and $_.ReplacementStrings[5] -notlike “*$”

Do not look for logon type ReplacementStrings[8] :

Change the timeframe you want to look at Now.AddHours(-24) . Can be replaced with Now.Adddays(-1) to look for longer periods.

#=======================================================================

#Global Variables
$server = Get-Content “E:\scripts\Event_Logs\scripts\servers.txt”

#Do it for each server in list above
ForEach ($computername in $server)

{

#Sub Global Variables
$Date = [DateTime]::Now.AddHours(-24)

$Date = get-date -format ‘MMMM-yyyy’
$Path = "E:\scripts\Event_Logs\logs\Security_Audit"
$Filename = “$Computername-SecurityLog-$date.csv”
$Filepath = $path + $filename
$eventList = @()

#Starting to get Eventlog

$event = Get-EventLog -computername $Computername “Security” -After $Date `
| Where -FilterScript {$.EventID -eq 4624 -and $.ReplacementStrings[4].Length -gt 10 `
-and $.ReplacementStrings[5] -notlike “*$” `
-and $.ReplacementStrings[5] -notlike “SophosSAUBPC*” `
-and $.ReplacementStrings[8] -notlike ""`
-and $.ReplacementStrings[8] -notlike "3"`
-and $.ReplacementStrings[8] -notlike "4"`
} `
| foreach-Object {
$row = “” | Select UserName, LoginTime, LogonType
$row.UserName = $.ReplacementStrings[5]
$row.LogonType = $.ReplacementStrings[8]
$row.LoginTime = $.TimeGenerated
$eventList += $row
}

#Write Event Log to CSV

$eventList | export-csv $filepath -NoTypeInformation -append
}

9

ReplacementStrings: http://msdn.microsoft.com/en-us/library/system.diagnostics.eventlogentry.replacementstrings(v=vs.110).aspx

The replacement strings are returned as a array, so the [#] indicates the index.

PS C:\> $rs = get-eventlog -LogName System -Newest 1 | Select ReplacementStrings

PS C:\> $rs

ReplacementStrings                                                                                                          
------------------                                                                                                          
{Background Intelligent Transfer Service, auto start, demand start, BITS}                                                   

PS C:\> $rs.ReplacementStrings[1]
auto start

So, the script is accessing different parts of the ReplacementStrings using the array index to filter the results. The $eventList variable is an empty array to append object to. The $row = “” | Select… basically creates a PSObject with those properties and then the $row.UserName = fills the property with data and the final line $eventList += $row appends the object into the array. There are numerous ways to create an custom PSObject, but that way is a bit confusing. You could replace all of this:

| foreach-Object {
$row = “” | Select UserName, LoginTime, LogonType
$row.UserName = $.ReplacementStrings[5]
$row.LogonType = $.ReplacementStrings[8]
$row.LoginTime = $_.TimeGenerated
$eventList += $row
}

with Select-Object and calculated properties and let Powershell generate the object for you:

| Select-Object @{Label=“UserName”;Expression={$.ReplacementStrings[5]}},@{Label=“LogonType”;Expression={$.ReplacementStrings[8]}},@{Label=“LoginTime”;Expression={$_.TimeGenerated}}

As far as how long the script takes to run could be multiple things including running locally, over the network, the size of the log, etc., so one can only speculate. You can try removing date\time from the query to see if it corrects the behavior to troubleshoot.