Extracting Data from CSV Files Using PowerShell

Question

I've got a syslog server dumping logs from our firewall appliance to CSV files. I'm taking that csv file, and want to pull out certain pieces of data like source and destination IP addresses in the log so I can run further analysis on them later.

This is an example of a single record from the source file. Notice how the IP addresses contained within the data are prefixed with either "src_ip", "dst_ip", "tran_src_ip" or "tran_dest_ip". Note: I've edited the IP addresses to mask them.

2018-07-01 14:48:47,Local7.Info,192.168.1.00,device="SFW" date=2018-07-01 time=14:48:39 timezone="PDT" device_name="XG" device_id=00000000000000 log_id=010101600001 log_type="Firewall" log_component="Firewall Rule" log_subtype="Allowed" status="Allow" priority=Information duration=11 fw_rule_id=3 policy_type=3 user_name="" user_gp="" iap=0 ips_policy_id=0 appfilter_policy_id=0 application="Secure Socket Layer Protocol" application_risk=1 application_technology="Network Protocol" application_category="Infrastructure" in_interface="Port2" out_interface="Port1" src_mac=00: 0:00: 0:00: 0 src_ip=75.148.000.000 src_country_code=USA dst_ip=23.24.000.000 dst_country_code=USA protocol="TCP" src_port=55000 dst_port=443 sent_pkts=7 recv_pkts=6 sent_bytes=1369 recv_bytes=918 tran_src_ip=192.168.000.000 tran_src_port=0 tran_dst_ip=192.168.000.000 tran_dst_port=0 srczonetype="WAN" srczone="WAN" dstzonetype="LOCAL" dstzone="LOCAL" dir_disp="" connevent="Stop" connid="1782869248" vconnid="" hb_health="No Heartbeat" message="" appresolvedby="Signature"

I've been able to write a script that can pull IP addresses out of the entire CSV file, but it doesn't specify if it's the src_ip or dst_ip, etc. I'd like to be able to create a script that can take the data from a CSV file, and then create a new CSV file with columns that contain the src_ip, dest_ip, etc.

My code is below:

$input_path = ‘c:\powershell_work\data.csv’
$output_file = ‘c:\powershell_work\output-file.csv’
$regex = ‘\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\b’

$ipaddresses = select-string -Path $input_path -Pattern $regex -AllMatches | 
% { $_.Matches } | % { $_.Value } | out-file $output_file -append

Is that exactly how the file appears to you? That is not really a csv. It has 4 columns, with no headers the last of which contains newlines — Matt
– Matt, Commented Jul 1, 2018 at 23:51
I added spaces in my post so that they would be formatted as code, but I can see now that it messed it up. I'll edit the OP so that it won't contain the spaces for my example input file. — FrameWork Team
– FrameWork Team, Commented Jul 2, 2018 at 0:21

mklement0 · Accepted Answer · 2018-07-03 02:24:00Z

1

$regex = '\b(\w+)=(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\b)'

Select-String -LiteralPath $input_path -AllMatches -Pattern $regex | ForEach-Object {
    $obj = New-Object pscustomobject
    foreach ($match in $_.Matches) {
      Add-Member -InputObject $obj -NotePropertyName $match.Groups[1].Value -NotePropertyValue $match.Groups[2].Value
    }
    $obj
} | Export-Csv -NoTypeInformation $output_file

EDIT by LotPings

Sample output of your above updated input (before the Export-Csv call):

src_ip         dst_ip        tran_src_ip     tran_dst_ip
------         ------        -----------     -----------
75.148.000.000 23.24.000.000 192.168.000.000 192.168.000.000

To extract a specifiable set of properties by name:

# Use a regex that matches all key-value pairs.
$regex = '\b(\w+)=([^ ]+)'

Select-String -LiteralPath $input_path -AllMatches -Pattern $regex | ForEach-Object {
    $obj = New-Object pscustomobject
    foreach ($match in $_.Matches) {
      Add-Member -InputObject $obj -NotePropertyName $match.Groups[1].Value -NotePropertyValue $match.Groups[2].Value
    }
    $obj
} | Select-Object *_ip, srczone, src_country_code, dstzone, dst_country_code | 
     Export-Csv -NoTypeInformation $output_file

Note that this first creates an object with all input properties and then selects only those of interest via Select-Object, which is somewhat inefficient, but keeps the command conceptually simple and allows you to easily determine the extraction order.

edited Jul 3, 2018 at 2:24

answered Jul 2, 2018 at 1:01

mklement0

452k68 gold badges727 silver badges986 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FrameWork Team Over a year ago

Awesome that worked! Is there a way we can further parse this to pull out all of the information into additional columns? Like pulling out the Country_Code, srzzone, dstzone, etc.

mklement0 Over a year ago

@FrameWorkTeam: Glad to hear it; as for pulling out other fields: please see my update.

Collectives™ on Stack Overflow

Extracting Data from CSV Files Using PowerShell

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related