Parse non-Microsoft Logs in PowerShell
I recently needed some firewall logs for east-west traffic to hunt down some of the computers in our environment that most frequently communicate with a certain group of servers.
I was able to bug one of our network team members to get the needed CSV files from our non-Microsoft firewalls. Upon first glance at one of the CSV files in Excel, I was met with an overwhelming amount of noise to the tune of about 20,000 rows that would be an absolute bear to wade through manually.
The main item that I wanted to extract from the logs was: What are the top 10 computers that are communicating with each of these servers? Secondly, I wanted to find out their hostnames, which was a piece of information that the logs did not include.
Here’s how I went about getting the needed information.
I want to note here that the following techniques are for extracting only a couple different types of information from one type of log file, but the same principles and techniques can be applied to many different situations of parsing data in PowerShell.
I first needed to verify that the CSV file was formatted in a PowerShell friendly way, meaning that the names of the various properties in the CSV file are in the first row. If the first several rows are occupied by miscellaneous information other than log entries, than just delete all the rows above the row that includes the property names and save it as a new file. If you have the opposite situation where all you have is log entries and no property names, then I’ll give you a solution in the following example.
Once your CSV is ready, run the following command:
$variableName = Import-Csv csvfilepath.csv
If your CSV has no property names in the first row, you can add these with the following optional parameter, “Header”. (You can also just add them into the CSV file before you import it.)
$variableName = Import-Csv csvfilepath.csv -Header “PropertyName1”, “PropertyName2”, “PropertyName3”
What the previous command allowed us to do was import the data into a variable that we created, which now houses all of our log entries in a PowerShell-centric format. We can now work with this data in the same way that we would with the output from a popular command like “Get-Process”.
We can now begin parsing our data.
$variableName | Select-Object -ExpandProperty “PropertyName1”
You may have many different properties (columns) associated with each of your log entries (rows), but right now we want to just look at one property of each of our entries. That is the IP address. What the previous command allowed us to do is only look at the IP addresses and no other properties. This is in line with the PowerShell way of scripting and that is filter left and format right. We want to filter down to the needed data right away. This will make the script run more efficiently as all of the following commands in the pipeline won’t be bogged down by unnecessary data.
While the results are definitely slimmed down a bit, there’s still just as many of them and they are still just as difficult to wade through. Here’s where we start narrowing down our output to get to our desired outcomes — the IP addresses with the most entries and their hostnames.
There’s an extremely handy PowerShell command that will allow us to group all of the duplicate entries in our CSV file into single rows as well as list the count that each of those duplicate entries occurs. That command is “Group-Object”.
$variableName | Select-Object -ExpandProperty “PropertyName1” | Group-Object |Select-Object Count, Name
We’re getting much closer as we just grouped all of our duplicate log entries into single rows and we’re only selecting the needed output from the “Group-Object” command, which are the “Count” and “Name” columns.
We can now both sort the output as well as filter down to only the top 10 results with the following command.
$variableName | Select-Object -ExpandProperty “PropertyName1” | Group-Object |Select-Object Count, Name | Sort-Object Count -Descending |Select-Object -First 10
We just sorted our output by the “Count” property and we output the results in a descending order (largest to smallest). We then passed on those results to “Select-Object” and used the “First” parameter to only include the first 10 results, which are the IP addresses with the most entries.
The results from the last command satisfy our first goal. Now we want to look up the hostnames that go along with our 10 IP addresses. In order to do that we could use a command like Nslookup (old CMD utility) or Resolve-DNSName (new PowerShell utility) to manually lookup each of the 10 entries in 10 different commands. This would defeat the purpose of using PowerShell to automate. We can actually use a slightly modified version of the last command to pipe those results onto even the pre-PowerShell utility — Nslookup and get our desired outcome.
$variableName | Select-Object -ExpandProperty “PropertyName1” | Group-Object |Select-Object Count, Name |Sort-Object Count -Descending |Select-Object -First 10 -ExpandProperty Name | Nslookup
The modification (-ExpandProperty Name) extracts only the IP addresses and passes those strings onto Nslookup, which then gets us our hostnames.
Hopefully this article has given you some ideas on how you can parse logs or data in general with PowerShell.
Further Reading and References:
Using Variables to Store Objects: https://docs.microsoft.com/en-us/powershell/scripting/getting-started/fundamental/using-variables-to-store-objects?view=powershell-5.1
Select-Object: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/select-object?view=powershell-5.1
Group-Object: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/group-object?view=powershell-5.1
Sort-Object: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/sort-object?view=powershell-5.1
Nslookup: https://technet.microsoft.com/en-us/library/bb490950.aspx
Resolve-DnsName: https://technet.microsoft.com/en-us/library/jj590781(v=wps.630).aspx
