File Audit report - to CSV part #2

37mm · September 24, 2015, 3:28am

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
  
    } 
    }

bobmccoy · September 24, 2015, 4:34am

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"

brian-b · September 24, 2015, 4:36am

#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

bobmccoy · September 24, 2015, 4:49am

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

brian-b · September 24, 2015, 4:56am

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

bobmccoy · September 24, 2015, 5:01am

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.

37mm · September 24, 2015, 5:09am

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

37mm · September 24, 2015, 5:15am

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.

bobmccoy · September 24, 2015, 5:32am

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.

bobmccoy · September 24, 2015, 5:35am

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])]

37mm · September 24, 2015, 6:10am

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

37mm · September 24, 2015, 6:15am

Delete operations don’t populate though. Not sure why

37mm · September 24, 2015, 6:28am

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.

brian-b · September 24, 2015, 6:55am

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.

37mm · September 24, 2015, 7:00am

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)

curtis-smith · September 24, 2015, 7:22am

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 :
`

37mm · September 24, 2015, 7:24am

EDIT

curtis-smith · September 24, 2015, 7:30am

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)”

37mm · September 24, 2015, 7:39am

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

curtis-smith · September 24, 2015, 7:41am

No Problem

Topic		Replies	Views
CSV to CSV PowerShell Help	9	112	October 20, 2014
Strings!! PowerShell Help	5	130	February 22, 2019
2 questions about output to csv PowerShell Help	1	159	February 12, 2018
Convert txt file to csv PowerShell Help	0	159	December 31, 2011
Export to CSV PowerShell Help	4	163	April 7, 2017

File Audit report - to CSV part #2

Related Topics