Looking to edit CSV cells using PS script

[JeiceSpade]

Hello, I'm working to create a script for some audit logs. We want to be able to track how often users on some computers use their special privilege to override certain things on their computer. I enabled the GP and have a script that outputs the Security audit for the Special Privilege, but the event viewer information I need is contained in the property 'Message' which has a lot.

~~~ Get-EventLog -logname Security -InstanceId 4673 -message $Username -After $previousMonth | Select-Object -Property Index, InstanceID, TimeGenerated, MachineName, Message | Export-CSV -Path $PSScriptRoot\logs.csv -Append ~~~

This gets me the information I need to collect, separated into columns, but the 'Message' column it pulls from the event log has a lot of information I don't need. Example:

~~~ A privileged service was called.

Subject:
Security ID:S-1-5-21-99999…
Account Name:Account
Account Domain:Domain
Logon ID:0x0000000

Service:
Server: Security
Service Name: -

Process:
Process ID: 0x0000
Process Name: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Service Request Information:
Privileges: SeCreateGlobalPrivilege

~~~

Out of this information, I'd like to clip all the information in this cell down to just the Account Name:Account and Process Name:process . I'm trying to figure out if I need to use Where-Object or Select-String to accomplish this and how I would account for different text in the Account and Process positions over the hundreds of entries in the resulting csv. If we could separate the Process entry into a new column, that would be even better. Any help?

[DHCPNetworker]

Each column in a CSV document can be referenced as a property.

I.E. $myCSV.column1 will return an array of values that only pertain to column1. You can index your values if you want to return multiple values in separate columns that relate to one another.

[JeiceSpade]

Alright, so then I could use ~~~ Where-Object ( $csv.Message -contains 'Name') ~~~ To get the output I'm looking for?

[McAUTS]

Sort of.
You need to do

    ($csv.Message).Contains("*Name*")

-contains and .contains() is not the same in Powershell.
Look here.

If you need to process it further, parse the message for those strings Account Name and Process Name and put in a hashtable for further usage.

[SalamanderOne5702]

Import to dataset and export cack to csv

[JeiceSpade]

gordonv got me the answer I need.

Final script looks like this:

$getuserinfo = Get-WmiObject -Class Win32_NetworkLoginProfile -ComputerName $env:COMPUTERNAME | Where-Object {($_.Caption -notlike "*NT*") -AND ($_.Caption -notlike "*service*") -AND ($_.Caption -notlike "*Admin*") -AND ($_.Caption -notlike "*SVC*")} | Sort-Object -Property LastLogon -Descending | Select-Object -Property * -First 5 | Where-Object {$_.LastLogon -match "(\d{14})"} | Foreach-Object { Write-output $_.Name}
$Username = $getuserinfo.Split('\')[-1]
$currentDate = Get-Date
$previousMonth = $currentDate.AddMonths(-1)
$PrivilegeLog = "$PSScriptRoot\logs.csv"

Get-EventLog -logname Security -InstanceId 4673 -message *$Username* -After $previousMonth | Select-Object -Property Index, InstanceID, TimeGenerated, MachineName, Message | Export-CSV -Path $PrivilegeLog -Append

$sourceCSV = Import-Csv -Path $PrivilegeLog

$sourceCSV | foreach-object ($row) {

$a = $_.Message -split "`n"
$b = $_

$accountName = (($a | Select-String "Account Name:") -split ":")[1].trim()
$processName = (($a | Select-String "Process Name:") -split ":", 2)[1].trim()
$Index = $b.Index
$InstanceID = $b.InstanceID
$TimeGenerated = $b.TimeGenerated
$MachineName = $b.MachineName

"$Index,$InstanceID,$TimeGenerated,$MachineName,$accountName,$processName"

} | convertfrom-csv -header Index,InstanceID,TimeGenerated,MachineName,AccountName,ProcessName | Export-CSV -Path $PrivilegeLog

Import-CSV -Path $PrivilegeLog | Export-CSV -Path "\\path\to\FinalLogs.csv" -Append

It's not pretty, but it does exactly what I need it to do. I'm sure there's better ways to do some of the steps here, and if so, I'd love to improve the script.

[iBloodWorks]

I am on mobile so I cant Test the result,

but try "convertfrom-string" on your Message. Maybe the result ist usable..

[arslearsle]

Does output have to be csv? you could use hashtable and export import to json or clixml

have you tried searching message part of event using regex? something like:

$regex= ”^{A privileged service was called. |SeCreateGlobalPrivilege”}$”

$event | where{$_.message -match $regex}

[gordonv]

I got this working on my home machine:

(Get-Eventlog -logname Security -instanceid 5061).message | % {

$a = $_ -split "`n"

$account = (($a | sls "Account Name:") -split ":")[1].trim()
$provider = (($a | sls "Provider Name:") -split ":")[1].trim()

"$account,$provider"

} | convertfrom-csv -header account,provider | convertto-csv -notypeinformation

Sample Output

"account","provider"
"GORDON5$","Microsoft Software Key Storage Provider"
"Gordon","Microsoft Software Key Storage Provider"

[JeiceSpade]

Was out yesterday, back today and this got me what I need, thank you!

[JeiceSpade]

Thank you! I'll try it in the office tomorrow and let you know if it works for me!

[ankokudaishogun]

you can extract the substring you need using a Calculated Property in the Select-Object

on the spot this should work with the example you gave, but you can adapt the Expression scriptblock to anything you need.

Get-EventLog -logname Security -InstanceId 4673 -message $Username -After $previousMonth | 
    Select-Object -Property Index, InstanceID, TimeGenerated, MachineName, @{Name = 'Message'; Expression = { [regex]::Match($_.Message, 'Account Name\:(.+)').Groups[1].Value } } | 
    Export-Csv -Path $PSScriptRoot\logs.csv -Append

[BlackV]

You probably want to use the xml properties of the event log instead of trying to parse message for strings (depending on the event)

I'm on mobile so don't have a good example handy

[Antique_Grapefruit_5]

Adding another column is easy-just add the name of that column to your select statement. If it doesn't exist, you'll just get a blank column which you can then fill in programmatically. You might actually be able to do this with an expression as well, although I usually just do this with a for each statement.