File Audit report - to CSV part #2

I got a little further having trouble get the data into a csv. Would appreciate any help!

$evt = Get-Eventlog -ComputerName sql -LogName Security  -InstanceId 5145 -after (Get-date).AddDays(-7)
foreach ($evt in $evts) {

Get-Eventlog -ComputerName sql -LogName Security  -InstanceId 5145 %{ 

   
    $timecreated = $_.TimeGenerated.DateTime
    $accountname =$_.ReplacementStrings[1]
    $objectype = $_.ReplacementStrings[4]
    $hostip = $_.ReplacementStrings[5]
    $filelocation =$_.ReplacementStrings[8]
    $whatfile = $_.ReplacementStrings[9]
    $whappend = $_.ReplacementStrings[10] `
   -Replace "0x100000", "Write Access (Synchronize)" `
   -Replace "0x80000", "Change Ownership" `
   -Replace "0x40000", "Modify Security" `
   -Replace "0x20000", "Read Access (Security)" `
   -Replace "0x10000", "Delete operation" `
   -Replace "0x100", "Write Access (Attributes)" `
   -Replace "0x80", "Read Access (Attributes)" `
   -Replace "0x40","Delete operation" `
   -Replace "0x20", "Read operation" `
   -Replace "0x10", "Write operation (Attributes)" `
   -Replace "0x8", "Read operation (Attributes)" `
   -Replace "0x4", "Write operation (Append)" `
   -Replace "0x2", "Write operation" `
   -Replace "0x1", "Write operation" `
   -Replace "0x0", "Read operation"

   $auditevent = New-Object System.Object
   $auditevent | Add-Member -Type NoteProperty -Name Time Created -SecondValue $timecreated
   $auditevent | Add-Member -type NoteProperty -Name UserName -SecondValue $accountname
   $auditevent | Add-Member -type NoteProperty -Name Object -SecondValue $objectype
   $auditevent | Add-Member -type NoteProperty -Name HostIP -SecondValue $hostip
   $auditevent | Add-Member -type NoteProperty -Name FileLocation -SecondValue $filelocation
   $auditevent | Add-Member -type NoteProperty -Name WhatFile -SecondValue $whatfile
   $auditevent | Add-Member -type NoteProperty -Name WhatHappened -SecondValue $whappend
  
    } 
    }

Here’s a snippet that gets rids of all those serial replacements (as you’re only going to do one replace per item) based on a hash table lookup.

$code = @{
   "0x100000" = "Write Access (Synchronize)"
   "0x80000" = "Change Ownership"
   "0x40000" = "Modify Security"
   "0x20000" = "Read Access (Security)"
   "0x10000" = "Delete operation"
   "0x100" = "Write Access (Attributes)"
   "0x80" = "Read Access (Attributes)"
   "0x40" = "Delete operation"
   "0x20" = "Read operation"
   "0x10" = "Write operation (Attributes)"
   "0x8" = "Read operation (Attributes)"
   "0x4" = "Write operation (Append)"
   "0x2" = "Write operation"
   "0x1" = "Write operation"
   "0x0" = "Read operation"
}

$replacementString10 = "0x80"
$text = $code[$replacementString10]
"The event text is $text"
#Create Array to hold results:
$ObjArray = @()

Get-Eventlog -ComputerName sql -LogName Security  -InstanceId 5145 -Newest 100  | %{ 

#Create a new object for every event so we can trap data
$ObjData = New-Object System.Object

#Each value needs to be added to the array.  I picked ID1 through ID7 just to have a property name   
$ObjData | Add-Member -type NoteProperty -name ID1 -value $_.TimeGenerated.DateTime
$ObjData | Add-Member -type NoteProperty -name ID2 -value $_.ReplacementStrings[1]
$ObjData | Add-Member -type NoteProperty -name ID3 -value $_.ReplacementStrings[4]
$ObjData | Add-Member -type NoteProperty -name ID4 -value $_.ReplacementStrings[5]
$ObjData | Add-Member -type NoteProperty -name ID5 -value $_.ReplacementStrings[8]
$ObjData | Add-Member -type NoteProperty -name ID6 -value $_.ReplacementStrings[9]
$ObjData | Add-Member -type NoteProperty -name ID7 -value $_.ReplacementStrings[10] `
   -Replace "0x100000", "Write Access (Synchronize)" `
   -Replace "0x80000", "Change Ownership" `
   -Replace "0x40000", "Modify Security" `
   -Replace "0x20000", "Read Access (Security)" `
   -Replace "0x10000", "Delete operation" `
   -Replace "0x100", "Write Access (Attributes)" `
   -Replace "0x80", "Read Access (Attributes)" `
   -Replace "0x40","Delete operation" `
   -Replace "0x20", "Read operation" `
   -Replace "0x10", "Write operation (Attributes)" `
   -Replace "0x8", "Read operation (Attributes)" `
   -Replace "0x4", "Write operation (Append)" `
   -Replace "0x2", "Write operation" `
   -Replace "0x1", "Write operation" `
   -Replace "0x0", "Read operation"

   #Add the completed single entry into the Array/Table
   $objArray += $ObjData
    }

#Output the completed Array to CSV (-NoTypeInformation is added to strip off the junk at the top)
$ObjArray | Export-Csv C:\Temp\MyOutput.csv -NoTypeInformation

You spend a lot of time generating intermediate results and then do a lot of unnecessary Add-Member. Try this on for size. I can’t verify since I don’t seem to have server generating that event.

$code = @{
   "0x100000" = "Write Access (Synchronize)"
    "0x80000" = "Change Ownership"
    "0x40000" = "Modify Security"
    "0x20000" = "Read Access (Security)"
    "0x10000" = "Delete operation"
      "0x100" = "Write Access (Attributes)"
       "0x80" = "Read Access (Attributes)"
       "0x40" = "Delete operation"
       "0x20" = "Read operation"
       "0x10" = "Write operation (Attributes)"
        "0x8" = "Read operation (Attributes)"
        "0x4" = "Write operation (Append)"
        "0x2" = "Write operation"
        "0x1" = "Write operation"
        "0x0" = "Read operation"
}
$results = Get-Eventlog -ComputerName sql -LogName Security  -InstanceId 5145 -after (Get-date).AddDays(-7) | foreach {

    [PSCustomObject]@{
        TimeCreated = $_.TimeGenerated.DateTime
        UserName =$_.ReplacementStrings[1]
        Object = $_.ReplacementStrings[4]
        HostIP = $_.ReplacementStrings[5]
        FileLocation = $_.ReplacementStrings[8]
        WhatFile = $_.ReplacementStrings[9]
        WhatHappened = $code[$_.ReplacementStrings[10]]
    }
}

# Sample outputs - pick one or more, your choice
$results
$results | Format-Table -AutoSize
$results | Out-GridView
$results | Export-Csv -Path .\foo.csv -NoTypeInformation -Encoding ASCII
$results | Out-File -FilePath .\foo.txt -Encoding ASCII
$results | Export-Clixml -Path .\foo.xml -Encoding ASCII

Totally agree Bob, your way is cleaner and now I have a reason to go back and update all my old scripts! haha. I’ve been doing it that old way for years. Thanks!

Brian

It seems to be a common approach to create an empty object and then do a bunch of Add-Member. It’s WAY more efficient to create a hash and then create (touch) the object once. I typically only use Add-Member for existing objects that I want to add additional properties, e.g., file system objects or process objects.

The other thing to keep in mind is that there is no true append to an array in PowerShell. So the “+=” operation ends up having to recreate the entire array every time it’s used. Needless to say, that can get expensive (read that as slow) as the array grows.

Thank you, bob and brian. Your knowledge is valuable beyond belief. Again Thanks!

Now Bob when I run the code the “what happened” field doesn’t populate with any data. I presume the way we are replace the value in string 10 isn’t correct? Only the read access shows up in the csv.

Are you running the fuller (later) example. The snippet above it was hardcoded to 0x80 (Read Access) just for an example of how to access the value in a has table. That makes me wonder if you copied from the later code.

Just to be sure we aren’t seeing an artifact from a previous run, change the last line of the splat to …

WhatHappened = $code[$($_.ReplacementStrings[10])]

Ok I the code to do the -replace after string 10 and it made it work.

$results = Get-Eventlog -ComputerName sql -LogName Security  -InstanceId 5145 -after (Get-date).AddDays(-1) | foreach {

    [PSCustomObject]@{
        TimeCreated = $_.TimeGenerated.DateTime
        UserName =$_.ReplacementStrings[1]
        Object = $_.ReplacementStrings[4]
        HostIP = $_.ReplacementStrings[5]
        FileLocation = $_.ReplacementStrings[8]
        WhatFile = $_.ReplacementStrings[9]
       WhatHappened = $_.ReplacementStrings[10]`
                        -Replace "0x100000", "Write Access (Synchronize)" `
                        -Replace "0x80000", "Change Ownership" `
                        -Replace "0x40000", "Modify Security" `
                        -Replace "0x20000", "Read Access (Security)" `
                        -Replace "0x10000", "Delete operation" `
                        -Replace "0x100", "Write Access (Attributes)" `
                        -Replace "0x80", "Read Access (Attributes)" `
                        -Replace "0x40","Delete operation" `
                        -Replace "0x20", "Read operation" `
                        -Replace "0x10", "Write operation (Attributes)" `
                        -Replace "0x8", "Read operation (Attributes)" `
                        -Replace "0x4", "Write operation (Append)" `
                        -Replace "0x2", "Write operation" `
                        -Replace "0x1", "Write operation" `
                        -Replace "0x0", "Read operation"
    }
}

# Sample outputs - pick one or more, your choice
#$results
#$results | Format-Table -AutoSize
#$results | Out-GridView
$results | Export-Csv -Path .\foo.csv -NoTypeInformation -Encoding ASCII
#$results | Out-File -FilePath .\foo.txt -Encoding ASCII
#$results | Export-Clixml -Path .\foo.xml -Encoding ASCII

Delete operations don’t populate though. Not sure why

0xc0080
Read operation080
Write operation7019f
Write operation30089
Write operation30089
0xc0080
Read operation080
Read Access (Attributes)
Write operation30197
Write operation30197

I get data for example that look like this.

Can you attach a screenshot or a copy/paste of the entire event from EventViewer (block out any proprietary info you don’t want shared) so we can see what it’s trying to parse out? We don’t have that advanced file auditing turned on here so i have no 5145’s to go by so i can try this.

A network share object was checked to see whether client can be granted desired access.

Subject:
Security ID: BRAENSTONE\JessicaP
Account Name: JessicaP
Account Domain: BRAENSTONE
Logon ID: 0x3F786A5E

Network Information:
Object Type: File
Source Address: XXXXXXXX (hidden)
Source Port: 54887

Share Information:
Share Name: \*\Zdrive
Share Path: ??\C:\Zdrive
Relative Target Name: JessicaP\1. Projects\Stone\2014-6-17 STONE-0013 Lancaster Farming Advertisement\STONE-0013 Lancaster Farming Tear Sheet 4-4-15.pdf

Access Request Information:
Access Mask: 0x80
Accesses: ReadAttributes

Access Check Results:
ReadAttributes: Granted by D:(A;;FA;;;DU)

37mm, I would take a slightly different approach using Get-WinEvent instead of Get-EventLog. With Get-WinEvent you can convert it the event into XML for manipulation. It also seems to run faster than Get-EventLog for me remotely. I definately think Bob’s approach of using a hashtable instead of a bunch of string replacements is the better of the two options. You are, however, going to have to build up your hash table over time just as you would your string replacements. The list you have is far from complete, as you can see in the results below, two of the returned code do not have matching text values from the hashtable. In fact, the first result did not either, I had to add it so it would show in the example output.

$codes = @{
   "0x100080" = "Read Attributes (Synchronize)"
   "0x100000" = "Write Access (Synchronize)"
   "0x80000" = "Change Ownership"
   "0x40000" = "Modify Security"
   "0x20000" = "Read Access (Security)"
   "0x10000" = "Delete operation"
   "0x100" = "Write Access (Attributes)"
   "0x80" = "Read Access (Attributes)"
   "0x40" = "Delete operation"
   "0x20" = "Read operation"
   "0x10" = "Write operation (Attributes)"
   "0x8" = "Read operation (Attributes)"
   "0x4" = "Write operation (Append)"
   "0x2" = "Write operation"
   "0x1" = "Write operation"
   "0x0" = "Read operation"
}

$Events = Get-WinEvent -ComputerName server1 -FilterHashtable @{LogName="Security"; ID=5145}

Foreach ($Event in $Events) {
    $EventDataXML = ([xml]$Event.ToXML()).Event.EventData.Data
    [PSCustomObject]@{
        TimeCreated = $Event.TimeCreated;
        UserName = ($EventDataXML | Where-Object {$_.Name -eq 'SubjectUserName'}).'#text';
        Object = ($EventDataXML | Where-Object {$_.Name -eq 'ObjectName'}).'#text';
        HostIP = ($EventDataXML | Where-Object {$_.Name -eq 'SourceAddress'}).'#text';
        FileLocation = ($EventDataXML | Where-Object {$_.Name -eq 'ShareName'}).'#text';
        WhatFile = ($EventDataXML | Where-Object {$_.Name -eq 'ShareLocalPath'}).'#text';
        WhatHappenedCode = ($EventDataXML | Where-Object {$_.Name -eq 'AccessMask'}).'#text';
        WhatHappenedText = $codes[($EventDataXML | Where-Object {$_.Name -eq 'AccessMask'}).'#text']
    }#pscustomobject
}#foreach 

Results

TimeCreated : 9/24/2015 3:05:37 PM
UserName : user1
Object :
HostIP :
FileLocation : \*\test$
WhatFile : ??\C:\Users\Public\Desktop\Automation
WhatHappenedCode : 0x100080
WhatHappenedText : Read Attributes (Synchronize)

TimeCreated : 9/24/2015 3:04:27 PM
UserName : user2
Object :
HostIP :
FileLocation : \*\IPC$
WhatFile :
WhatHappenedCode : 0x12019f
WhatHappenedText :

TimeCreated : 9/24/2015 3:04:27 PM
UserName : user2
Object :
HostIP :
FileLocation : \*\IPC$
WhatFile :
WhatHappenedCode : 0x12019f
WhatHappenedText :

EDIT

Correct, that is what is being done here

WhatHappenedText = $codes[($EventDataXML | Where-Object {$_.Name -eq 'AccessMask'}).'#text']

($EventDataXML | Where-Object {$_.Name -eq ‘AccessMask’}).’#text returns the value that is stored in the “AccessMask” field in the message of the event, so essentially our result is

WhatHappenedText = $codes[AccessMaskValue]

Ex:
WhatHappenedText = $codes[“0x100000”]

Since $codes is a hashtable, and you are passing in a name of “0x100000” it results in

WhatHappendText = “Write Access (Synchronize)”

Ok. I will work on getting this together and filling out the hash table. Thanks for the help Curtis!

No Problem