PowerShell and Data Analysis

Sonny
Sonny
Nov 4 · 5 min read

Disclaimer: There are powerful tools available out there for data analysis but this is my, “minion” adventure to use PowerShell for data analysis.

Pretty unusual to start an article with a disclaimer but I thought its useful to do it as I don’t want the data analysis tool gods getting angry with me :)

Few months ago me and my buddy (https://twitter.com/gzboudreau) decided to give a joint talk at the ISACA conference in Halifax, Nova Scotia. Our talk was focused on analyzing Shodan data as it pertain to Atlantic Canada, for those who don’t know Atlantic Canada comprises of 4 Provinces located on the East coast of Canada they are Nova Scotia, New Brunswick, PEI and Newfoundland & Labrador . For more visual reference see the map below

Source: https://en.wikipedia.org/wiki/Atlantic_Canadian_English

If you never been to Atlantic Canada we have two awesome Cybersecurity conferences every year, AtlSecCon around April and ISACA Con around October. More details can be found below

Back to our topic; As I mentioned we used Shodan data for our analysis for those who don’t know Shodan is a repository of internet scan providing details on servicing listening on the internet. T

Shodan give subscribed user an option to download data in different format for offline analysis. For our purposes we decided to download data using JSON format. If you don’t know JSON (trust me it’s more than just tag and value) I would highly recommend following article to get the basics right.

PowerShell provides two Cmdlet for JSON, ConvertTo-JSON and ConvertFrom-JSON. As you can imagine ConvertTo-JSON convert the Objects into JSON format while ConvertFrom-JSON convert JSON to PSObject (fancy way of saying PowerShell object). Here is official description of this cmdlet from Microsoft

“The ConvertFrom-Json cmdlet converts a JavaScript Object Notation (JSON) formatted string to a custom PSCustomObject object that has a property for each field in the JSON string. JSON is commonly used by web sites to provide a textual representation of objects.”

But what does this mean?

Lets see an example, here is a small JSON file representing a single object, i saved this file as “shodan.json”

{
“source_ip”: “192.168.0.1”,
“port”: 22,
“org”: “Acme”
}

The ConvertFrom-JSON cmdlet was successful

Nothing fancy here right, well under the hood the txt file is converted to objects and we love objects. The screen capture below shows that we converted the txt file to PSCustomObject. (It’s a JSON file but in reality it’s just a txt file with bunch of data following JSON convention)

The Get-Member clearly reflecting our JSON data converted to Object

Lets take another example this time we have two objects

[
{
“source_ip”: “192.168.0.1”,
“port”: 22,
“org”: “Acme”
},

{
“source_ip”: “192.168.0.2”,
“port”: 23,
“org”: “NASA”
}
]

Here is the PowerShell cmdlet to convert JSON to PowerShell objects

But why are we converting the JSON file to objects, well once we have the JSON elements as objects we can leverage number of PowerShell cmdlets such as Measure-Object, Sort-Object to accomplish “interesting” things.

Moreover you can then use tool like Gnuplot or good old Excel to create nice looking graphs.

For our example, I wanted to analyze Shodan data containing services listening on Port 21 in the beautiful province of New Brunswick, Canada. So I used my subscription to pull the data from Shodan for Port 21 filtered on New Brunswick and saved it in JSON format. Once I have my JSON file I can start my analysis, to begin here is what the last element in my JSON file

In total we have 1000 record in the JSON file (That’s the default limit Shodan imposes you can increase that)

Now all these objects have properties such as ip, port, transport, product etc.

Object Properties

we can use certain properties and conduct “interesting” analysis. Let me show you how.

Let say I want to see which organization in New Brunswick get’s the Number 1 Rank in exposing insecure port 21 to the internet :) , Here’s how I would do it

Congratulations GlobalTech Communications you are Ranked Number 1 in exposing Port 21.

Similarly I can do further analysis such as type of software running etc. While PowerShell is not a data analysis platform it sure can help analyze data. I hope this small article help you to see PowerShell potential.

Thanks,

Sonny

Written by

Sonny

#Cybersecurity Geek | Noob #Malware researcher | Recent @elastic explorer|#BlueTeam student| #PowerShell preacher | #Foodie| Former KGB | #Vim camp|

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade