# collect-tcpconnections
#
# Collect TCP-Connections by clients from various servers and store as hisstorical data
#
# Create the subnet2site.csv with Generate-subnet2site.csv
#   
# 20170927 FC Erste öffentliche Version mit Auswertungen und Zusammenfassungen

param (
	$servers = ("ex2016n1","ex2016n1"),
	$datadir = ".\",
	$subnet2sitecsv = ".\subnet2site.csv"
)

write-host "collect-tcpconnections:Start"
set-psdebug -strict

write-host "collect-tcpconnections:Spawning Jobs to computers using Invoke-Command START"
# using select to reduce the amount of data transmitted from remote servers
foreach ($computer in $servers){
	write-host "collect-tcpconnections: Server $($computer)"
	Invoke-Command `
		-ComputerName $computer `
		-asjob `
		-ScriptBlock {Get-NetTCPConnection `
			| where {(($_.localport -eq 443) -and ($_.remoteport -ne 0))} `
			| select LocalAddress,RemoteAddress,RemotePort,State,CreationTime}
}
write-host "collect-tcpconnections:Spawning Jobs to computers using Invoke-Command DONE"

write-host "collect-tcpconnections:Wait for Jobs to complete START"
do {
	$joblist = get-job
	$joblist | out-host
	start-sleep -seconds 1
}
while ($joblist | where {$_.State -eq "Running"})
write-host "collect-tcpconnections:Wait for Jobs to complete DONE"

write-host "collect-tcpconnections: Collecting Results"
$result = get-job | receive-job 

write-host "collect-tcpconnections: Total Records collected: $($result.count)"

write-host "collect-tcpconnections: Removing Jobs"
Get-Job | remove-job

[string]$now = get-date -Format yyyyMMdd-HHmm
write-host "collect-tcpconnections: Processing output"
[datetime]$nowdt = get-date 
write-host "collect-tcpconnections: Generate Liste of Connections with duration and timestamp"
$result `
| select @{name="datetime";expression={$now}},LocalAddress,RemoteAddress,RemotePort,State,CreationTime, @{name="duration";expression={[long]($nowdt-[datetime]($_.creationtime)).totalseconds}} `
| export-csv `
   -delimiter "," `
   -encoding unicode `
   -path ($datadir+"\iisports-"+$now+".csv") `
   -notypeinformation

write-host "collect-tcpconnections: Group results by RemoteAddress"
$resultgroup = $result `
| group RemoteAddress -noelement `
| select `
	@{Name="RemoteAddress";Expression={$_.name}}, `
	Count 

write-host "collect-tcpconnections: Generate Liste with connections per RemoteAddress"
$resultgroup  | export-csv `
   -delimiter "," `
   -encoding unicode `
   -path ($datadir+"\iisports-"+$now+"-groupbyremoteaddress.csv") `
   -notypeinformation

write-host "collect-tcpconnections: Generate Client per ADSite Table"
if (!(test-path -path $subnet2sitecsv -pathtype leaf)) {
	write-warning "collect-tcpconnections:No ADSiteFile found. Skip Report"
}
else {}
	write-host "collect-tcpconnections: Loading Subnet2Site-Table"
	[hashtable]$subnetb2sitename=@{}
	import-csv -path $subnet2sitecsv `
		| %{$subnetb2sitename[$_.SubnetB]=$_.Site}

	write-host "collect-tcpconnections: Generate Liste with connections per SubnetB Site"
	$resultgroup `
		| select `
			@{Name="SubnetB";Expression={($_.RemoteAddress.split(".")[0..1]) -join "."}}`
			,count `
		| group Subnetb `
		| Select name,count,Group,avgconnection,maxconnection `
		| %{$_.avgconnection = [long](($_.Group | Measure-Object count -Sum).sum / $_.count); `
			$_.maxconnection = ($_.Group | Measure-Object count -Maximum).Maximum; `
			$_} `
		| select `
			@{Name="SubnetB";Expression = {$_.name}}, `
			@{Name="Client";Expression = {$_.count}}, `
			@{Name="Sitename";Expression = {$subnetb2sitename[$_.name]}}, `
			avgconnection, `
			maxconnection `
		| sort avgconnection -descending `
		| export-csv `
			-delimiter "," `
			-encoding unicode `
			-path ($datadir+"\iisports-"+$now+"-connectionbysite.csv") `
			-notypeinformation
} 

write-host "collect-tcpconnections:End"