March | 2009 | David Muegge's Blog

Welcome!

This is a little trick some might find useful. I was working on decommissioning some servers and I needed a way to find out what was connecting to these machines. I decided to create a script to log connections. I have done this in the past in various ways which usually involved logging a bunch of data and then querying against it to find the unique connections.

This time it finally occurred to me, just filter the data as it is being collected. So I set out to write a PowerShell script that would keep a running list of client TCP connections to a given machine. This information would be stored in a text file.

The first step was to collect the information and put it into a PowerShell object.

# Create array to hold netstat data
$allnetstatdata = @()

# Get TCP connections from netstat
$netstatResults = & netstat -p TCP 
foreach($line in $netstatResults){

	# Create object data and add to object
	$netstatdata = "" | Select local,port,foreign
	if(-not (($line.Trim() -eq [String]::Empty) -or ($line.contains("Active Connections")) -or ($line.contains("Local Address")))){
		if ($line -match 's*(TCP)s{1,}(DS{1,})s{1,}(DS{1,})s{1,}(DS{1,}$)'){
			$localhost = $matches[2].Split(":")
			$netstatdata.local = $dchost[0]
			$netstatdata.port = $dchost[1]
			$foreignhost = $matches[3].Split(":")
			$netstatdata.foreign = $foreignhost[0]
			$allnetstatdata = $allnetstatdata + $netstatdata
		}
	}			
}

# Create array to hold netstat data

$allnetstatdata = @()

# Get TCP connections from netstat

$netstatResults = & netstat -p TCP

foreach($line in $netstatResults){

# Create object data and add to object

$netstatdata = "" | Select local,port,foreign

if(-not (($line.Trim() -eq [String]::Empty) -or ($line.contains("Active Connections")) -or ($line.contains("Local Address")))){

if ($line -match 's*(TCP)s{1,}(DS{1,})s{1,}(DS{1,})s{1,}(DS{1,}$)'){

$localhost = $matches[2].Split(":")

$netstatdata.local = $dchost[0]

$netstatdata.port = $dchost[1]

$foreignhost = $matches[3].Split(":")

$netstatdata.foreign = $foreignhost[0]

$allnetstatdata = $allnetstatdata + $netstatdata

}

Then the next step was to read the file with the previous information and add it to the PowerShell object.

# Define Log File
$logfile = C:TCPConnections.txt

# Get data from file and add to object
$filedata = Get-Content $logfile
if($filedata){
	foreach($line in $filedata){
		$netstatdata = "" | Select local,port,foreign
		$entry = $line.Split("`t")
		$netstatdata.port = $entry[0]
		$netstatdata.local = $entry[1]
		$netstatdata.foreign = $entry[2]
		$allnetstatdata = $allnetstatdata + $netstatdata
	}
}

# Define Log File

$logfile = C:TCPConnections.txt

# Get data from file and add to object

$filedata = Get-Content $logfile

if($filedata){

foreach($line in $filedata){

$netstatdata = "" | Select local,port,foreign

$entry = $line.Split("`t")

$netstatdata.port = $entry[0]

$netstatdata.local = $entry[1]

$netstatdata.foreign = $entry[2]

$allnetstatdata = $allnetstatdata + $netstatdata

}

We can now remove the duplicates from the combined information and save the updated file.

# Get unique rows
$newnetstatdata = $allnetstatdata | Select-Object * -unique 

# Add combined data to string
$tempcontent = New-Object System.Text.StringBuilder
foreach($netstat in $newnetstatdata){
	if($netstat.port){$tempcontent.Append($netstat.port.ToString()) | Out-Null}
	$tempcontent.Append("`t") | Out-Null 
	if($netstat.local){$tempcontent.Append($netstat.local.ToString()) | Out-Null}
	$tempcontent.Append("`t") | Out-Null 
	if($netstat.foreign){$tempcontent.Append($netstat.foreign.ToString()) | Out-Null}
	$tempcontent.Append("`n") | Out-Null 
}
		
# Write to file
Set-Content -path $logfile $tempcontent.ToString()

# Get unique rows

$newnetstatdata = $allnetstatdata | Select-Object * -unique

# Add combined data to string

$tempcontent = New-Object System.Text.StringBuilder

foreach($netstat in $newnetstatdata){

if($netstat.port){$tempcontent.Append($netstat.port.ToString()) | Out-Null}

$tempcontent.Append("`t") | Out-Null

if($netstat.local){$tempcontent.Append($netstat.local.ToString()) | Out-Null}

$tempcontent.Append("`t") | Out-Null

if($netstat.foreign){$tempcontent.Append($netstat.foreign.ToString()) | Out-Null}

$tempcontent.Append("`n") | Out-Null

}

# Write to file

Set-Content -path $logfile $tempcontent.ToString()

We can run this script in a scheduled task at whatever interval is required. Now we have a log of unique inbound TCP connections.

Best Regards,

Dave

Welcome!

One day I was pondering how I might use log parser to map visitors to a website by state. I am aware this is easily done with tools like Google Analytics, but I was interested in using existing logs for the info.

Using the PowerShell and Log Parser functions from the library listed in a previouse post. Log parser can easily get the visitors by IP Address from an IIS log.

# Get unique ip addresses to working file
$query = @"
SELECT Distinct c-ip As IPAddress
INTO D:Unique_IP_Addresses.txt
From C:Logsex080310.log
"@
$LPInputFormat = Get-LPInputFormat "w3c"
$LPOutputFormat = Get-LPOutputFormat "csv"
Invoke-LPExecuteBatch -query $query -inputtype $LPInputFormat -outputtype $LPOutputFormat | Out-Null

# Get unique ip addresses to working file

$query = @"

SELECT Distinct c-ip As IPAddress

INTO D:Unique_IP_Addresses.txt

From C:Logsex080310.log

$LPInputFormat = Get-LPInputFormat "w3c"

$LPOutputFormat = Get-LPOutputFormat "csv"

Invoke-LPExecuteBatch -query $query -inputtype $LPInputFormat -outputtype $LPOutputFormat | Out-Null

The next task is to get the location of the IP addresses. The tool I chose for this task was the free GeoLite City from MaxMind http://www.maxmind.com/app/geolitecity. Here is an example:

# Get location for IP addresses from GEOIP DB
$geodbpath = "C:GeoLiteCity"
$visitors = Import-Csv -Path "D:Unique_IP_Addresses.txt"
$ipinfo = New-Object -ComObject "GeoIPCOMEx.GeoIPEx"
$ipinfo.set_db_path($geodbpath) | Out-Null
Add-Content -Path "D:IP_Locations.txt" "Location"
foreach($visitor in $visitors){
	$ipinfo.find_by_addr($visitor.ipaddress) | Out-Null
	if($ipinfo.Region){
		Add-Content -Path "D:IP_Locations.txt" $ipinfo.Region
	}
	else
	{
		Add-Content -Path "D:IP_Locations.txt" "Unknown"
	}
}

# Get State counts
$query = @"
SELECT Location, Count(Location) As Visitors
INTO D:VisitorsByState.txt
From D:IP_Locations.txt
Group By Location
"@
$LPInputFormat = Get-LPInputFormat "csv"
$LPOutputFormat = Get-LPOutputFormat "csv"
Invoke-LPExecuteBatch -query $query -inputtype $LPInputFormat -outputtype $LPOutputFormat | Out-Null

# Get location for IP addresses from GEOIP DB

$geodbpath = "C:GeoLiteCity"

$visitors = Import-Csv -Path "D:Unique_IP_Addresses.txt"

$ipinfo = New-Object -ComObject "GeoIPCOMEx.GeoIPEx"

$ipinfo.set_db_path($geodbpath) | Out-Null

Add-Content -Path "D:IP_Locations.txt" "Location"

foreach($visitor in $visitors){

$ipinfo.find_by_addr($visitor.ipaddress) | Out-Null

if($ipinfo.Region){

Add-Content -Path "D:IP_Locations.txt" $ipinfo.Region

}

else

{

Add-Content -Path "D:IP_Locations.txt" "Unknown"

}

# Get State counts

$query = @"

SELECT Location, Count(Location) As Visitors

INTO D:VisitorsByState.txt

From D:IP_Locations.txt

Group By Location

$LPInputFormat = Get-LPInputFormat "csv"

$LPOutputFormat = Get-LPOutputFormat "csv"

Invoke-LPExecuteBatch -query $query -inputtype $LPInputFormat -outputtype $LPOutputFormat | Out-Null

There are a couple of ways to use the MaxMind GeoIP database. It can be used in it’s native binary format or it can be imported into SQL from csv files. MaxMind recommends using the binary format, which is what I chose to do. MaxMind also provides API’s for use with a variety of platform’s. I chose to use the COM version.

After the location is determined the counts are calculated. This brings us to the point where we need to chart the results. The tool I chose for this operation is PowerGadgets. This is a charting tool made for use with PowerShell, it can be handy. Here is an example:

# Create PowerGadgets Map
$charttitle = "Daily visitors by state"
$visits = Import-Csv -Path D:VisitorsByState.txt
$visits | Out-Map -mapsource "USUSA-StatesAbrev" `
		-label location `
		-values visitors `
		-palette "highcontrast" `
		-output $chartout `
		-size 600,400 `
		-BackColor White `
		-Titles_0_BackColor White `
		-Titles_0_Font "Arial, 14" `
		-Titles_0_Text $charttitle `
		-Titles_0_TextColor Black `
		-conditionalattributes_0_condition_from 1 `
		-conditionalattributes_0_condition_to 10 `
		-conditionalattributes_0_color Red `
		-conditionalattributes_0_text "1 to 10" `
		-conditionalattributes_1_condition_from 11 `
		-conditionalattributes_1_condition_to 25 `
		-conditionalattributes_1_color Blue `
		-conditionalattributes_1_text "11 to 25" `
		-conditionalattributes_2_condition_from 26 `
		-conditionalattributes_2_condition_to 50 `
		-conditionalattributes_2_color Green `
		-conditionalattributes_2_text "26 to 50" `
		-conditionalattributes_3_condition_from 51 `
		-conditionalattributes_3_condition_to 100 `
		-conditionalattributes_3_color Yellow `
		-conditionalattributes_3_text "51 to 100" `
		-conditionalattributes_4_condition_from 101 `
		-conditionalattributes_4_condition_to 1000 `
		-conditionalattributes_4_color Violet `
		-conditionalattributes_4_text "101 to 1000"

# Create PowerGadgets Map

$charttitle = "Daily visitors by state"

$visits = Import-Csv -Path D:VisitorsByState.txt

$visits | Out-Map -mapsource "USUSA-StatesAbrev" `

-label location `

-values visitors `

-palette "highcontrast" `

-output $chartout `

-size 600,400 `

-BackColor White `

-Titles_0_BackColor White `

-Titles_0_Font "Arial, 14" `

-Titles_0_Text $charttitle `

-Titles_0_TextColor Black `

-conditionalattributes_0_condition_from 1 `

-conditionalattributes_0_condition_to 10 `

-conditionalattributes_0_color Red `

-conditionalattributes_0_text "1 to 10" `

-conditionalattributes_1_condition_from 11 `

-conditionalattributes_1_condition_to 25 `

-conditionalattributes_1_color Blue `

-conditionalattributes_1_text "11 to 25" `

-conditionalattributes_2_condition_from 26 `

-conditionalattributes_2_condition_to 50 `

-conditionalattributes_2_color Green `

-conditionalattributes_2_text "26 to 50" `

-conditionalattributes_3_condition_from 51 `

-conditionalattributes_3_condition_to 100 `

-conditionalattributes_3_color Yellow `

-conditionalattributes_3_text "51 to 100" `

-conditionalattributes_4_condition_from 101 `

-conditionalattributes_4_condition_to 1000 `

-conditionalattributes_4_color Violet `

-conditionalattributes_4_text "101 to 1000"

And here is our final Chart.

This works pretty well the only drawback to this solution is that PowerGadgets is a pay tool, but since I own a copy it suites my needs.

Regards,

Dave

Monthly Archives for March 2009

Connection History with PowerShell and NetStat

PowerShell, Log Parser, PowerGadgets, and GeoIP what fun!

Share this:

Share this: