function Get-SpeedChart { param( [parameter(Mandatory=$true)][string]$client, [parameter(Mandatory=$true)][string]$location ) $mySQLsrv = "[SRV]" $port = "3306" $mySQLdb = "labtech" $un = "[user]" $pw = '[password]' | ConvertTo-SecureString -AsPlainText -Force $creds = New-Object System.Management.Automation.PSCredential($un,$pw) Connect-MySqlServer -Credential $creds -ComputerName $mySQLsrv -Port $port -Database $mySQLdb Select-MySqlDatabase labtech $cSearch = '*' + $client + '*' $lSearch = '*' + $location + '*' # $tmpTable = '`' + ($client).Replace(' ','') + "_" + ($location).Replace(' ','') + '`' $dquery = "SELECT CAST(`plugin_speedtest`.`date` AS char) AS 'date',ROUND(`plugin_speedtest`.`download` / 1024,2) AS 'download',ROUND(`plugin_speedtest`.`upload` / 1024,2) AS 'upload',`plugin_speedtest`.`ping`,`clients`.`Name` AS 'Client',`locations`.`Name` AS 'Location' FROM `plugin_speedtest` LEFT JOIN `computers` ON `plugin_speedtest`.`computerid` = `computers`.`ComputerID` LEFT JOIN `clients` ON `computers`.`ClientID` = `clients`.`ClientID` LEFT JOIN `locations` ON `computers`.`LocationID` = `locations`.`LocationID`;" $dataSet = Invoke-MySqlQuery $dquery | Where {($_.client -like $cSearch) -And ($_.location -like $lSearch) -And (!($_.download -gt "100" -or $_.upload -gt "100"))} [void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization") $chart1 = New-Object System.Windows.Forms.DataVisualization.Charting.Chart $chart1.Width = 1000 $chart1.Height = 400 $chart1.AntiAliasing = [System.Windows.Forms.DataVisualization.Charting.AntiAliasingStyles]::All $chart1.BackColor = [System.Drawing.Color]::White $title = $($dataSet[0].client) + " (" + $($dataSet[0].location) + ")" $averageDL = ($dataSet.download).Average $averageUL = ($dataSet.upload).Average [void]$chart1.Titles.Add("$title") $chart1.Titles[0].Font = "segoeuilight,20pt" $chart1.Titles[0].Alignment = "topLeft" $chartArea = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea $chartArea.Name = "ChartArea1" $chartArea.AxisY.Title = "Speed (Mb/s)" $chartArea.AxisX.Title = "Date" $chartArea.AxisY.Interval = "5" $chartArea.AxisX.Interval = "1" $chartArea.AxisY.MajorGrid.LineColor = "#C0C0C0" $chartArea.AxisX.MajorGrid.LineColor = "#C0C0C0" $chartArea.AxisY.Crossing = 0; $chartArea.AxisX.Crossing = 0; $chartArea.AxisY.LineWidth = 2; $chartArea.AxisX.LineWidth = 2; #$chartArea.Area3DStyle.Enable3D = $true #$chartArea.Area3DStyle.Inclination = 20 $chart1.ChartAreas.Add($chartArea) $legend = New-Object System.Windows.Forms.DataVisualization.Charting.Legend $legend.Name = "Legend1" $chart1.Legends.Add($legend) [void]$chart1.Series.Add("download") $chart1.Series["download"].BorderWidth = 3 $chart1.Series["download"].IsVisibleInLegend = $true $chart1.Series["download"].ChartArea = "ChartArea1" $chart1.Series["download"].Legend = "Legend1" $chart1.Series["download"].Color = "#62B5CC" $chart1.Series["download"].ChartType = [System.Windows.Forms.DataVisualization.Charting.SeriesChartType]::Line ForEach ($d in $dataSet) {$date = ($($d.date)) -replace ".{5}$"; $chart1.Series["download"].Points.AddXY($date,$($d.download))} [void]$chart1.Series.Add("upload") $chart1.Series["upload"].BorderWidth = 3 $chart1.Series["upload"].IsVisibleInLegend = $true $chart1.Series["upload"].ChartArea = "ChartArea1" $chart1.Series["upload"].Legend = "Legend1" $chart1.Series["upload"].Color = "#E3B64C" $chart1.Series["upload"].ChartType = [System.Windows.Forms.DataVisualization.Charting.SeriesChartType]::Line ForEach ($u in $dataSet) {$date = ($($u.date)) -replace ".{5}$"; $chart1.Series["upload"].Points.AddXY($date,$($u.upload))} <# [void]$chart1.Series.Add("ping") $chart1.Series["ping"].Borderwidth = 2 $chart1.Series["ping"].IsVisibleInLegend = $true $chart1.Series["ping"].ChartArea = "ChartArea1" $chart1.Series["ping"].Legend = "Legend1" $chart1.Series["ping"].Color = [System.Drawing.Color]::Yellow $chart1.Series["ping"].ChartType = [System.Windows.Forms.DataVisualization.Charting.SeriesChartType]::Line ForEach ($p in $dataSet) {$chart1.Series["ping"].Points.AddXY($($p.date),$($p.date))} #> $outFile = "C:\Users\Public\Speed\" + $client + "_" + $location + ".png" $chart1.SaveImage("$outFile","png") Disconnect-MySqlServer Clear-Variable -name dataSet Clear-Variable -name chart1 Clear-Variable -name chartArea Clear-Variable -name legend Clear-Variable -name u Clear-Variable -name d Clear-Variable -name Client Clear-Variable -name location Clear-Variable -name cSearch Clear-Variable -name lSearch Clear-Variable -name query } $mySQLsrv = "MSILT" $port = "3306" $mySQLdb = "labtech" $un = "shayes" $pw = 'msi2015!' | ConvertTo-SecureString -AsPlainText -Force $creds = New-Object System.Management.Automation.PSCredential($un,$pw) Connect-MySqlServer -Credential $creds -ComputerName $mySQLsrv -Port $port -Database $mySQLdb Select-MySqlDatabase labtech $lQuery = "SELECT DISTINCT `clients`.`Name` AS 'Client',`locations`.`Name` AS 'Location' FROM `plugin_speedtest` LEFT JOIN `computers` ON `plugin_speedtest`.`computerid` = `computers`.`ComputerID` LEFT JOIN `clients` ON `computers`.`ClientID` = `clients`.`ClientID` LEFT JOIN `locations` ON `computers`.`LocationID` = `locations`.`LocationID`;" $locations = Invoke-MySqlQuery $lQuery ForEach ($l in $locations) { Get-SpeedChart -Client $l.client -Location $l.location } Clear-Variable -Name locations Disconnect-MySqlServer