This script also does some conversion and opens the CSV in Excel when it's done.
############################################################################################
#
# SSID Report Conversion
# Process SSID report from NCS. Calculate connection time in seconds and KB transferred
#
Function Get-FileName($initialDirectory)
{
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
$OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
$OpenFileDialog.ShowHelp = $true
$OpenFileDialog.initialDirectory = $initialDirectory
$OpenFileDialog.filter = "All files (*.*)| *.*"
$OpenFileDialog.ShowDialog() | Out-Null
$OpenFileDialog.filename
} #end function Get-FileName
#
[string]$infile = Get-FileName -initialDirectory "Downloads"
if (-not $infile) { exit }
$outfile = $infile -replace ".csv", "-ADJUSTED.csv"
$file = get-content $infile
$today = Get-Date
$today = $today.touniversaltime()
write-output "Converted: $today" | out-file -encoding ASCII -filepath $outfile
foreach ($line in $file) {
$field = $line.split(",")
if ($field.count -eq 13) {# report body
if ($field[0] -eq "Client Username") { #header line (first field heading matches)
$heading = $line + ",Seconds Connected,KBytes Transferred"
write-output $heading | out-file -encoding ASCII -filepath $outfile -append
continue
} #end header line
#fix average Kb
$avgKbits = $field[6]
if ($avgKbits -eq "<0.1") { $avgKbits = 0 }
#Connection time in seconds
$conn = $field[5]
[array]$seperator = " hrs " , " min " , " sec"
$option = [System.StringSplitOptions]::RemoveEmptyEntries
$hours = 0
$minutes = 0
$seconds = 0
$connection = 0
$temp = $conn.split($seperator, $option)
if ($temp.count -eq 3) {
$hours = [int]$temp[0]
$minutes = [int]$temp[1]
$seconds = [int]$temp[2]
}
elseif ($temp.count -eq 2) {
$minutes = [int]$temp[0]
$seconds = [int]$temp[1]
}
elseif ($temp.count -eq 1) {
$seconds = [int]$temp[0]
}
$connection = ( $hours * 60 * 60 ) + ( $minutes * 60 ) + $seconds
#Bytes transferred
$KBytes = $connection * $avgKbits * 8
$line = $line + "," + $connection + "," + $KBytes
write-output $line | out-file -encoding ASCII -filepath $outfile -append
}#end if 16 fields
else {# report heading line (does not have 13 fields)
write-output $line | out-file -encoding ASCII -filepath $outfile -append
}#end else
}#end foreach line
start excel $outfile
No comments:
Post a Comment