$query = @' SELECT computers.computerid AS `Computer Id`, Locations.LocationID AS `Location Id`, computers.name AS `Computer`, clients.name AS `Client`, Locations.name AS `Location`, computers.domain AS `Domain`, computers.username AS `User`, IFNULL(IFNULL(edfAssigned1.Value,edfDefault1.value),'') AS `PublicIP`, IFNULL(IFNULL(edfAssigned2.Value,edfDefault2.value),'') AS `Hostname`, IFNULL(IFNULL(edfAssigned3.Value,edfDefault3.value),'') AS `ASN`, IFNULL(IFNULL(edfAssigned4.Value,edfDefault4.value),'') AS `City`, IFNULL(IFNULL(edfAssigned5.Value,edfDefault5.value),'') AS `State`, IFNULL(IFNULL(edfAssigned6.Value,edfDefault6.value),'') AS `Country`, IFNULL(IFNULL(edfAssigned7.Value,edfDefault7.value),'') AS `ZipCode`, IFNULL(IFNULL(edfAssigned8.Value,edfDefault8.value),'') AS `gps`, IFNULL(IFNULL(edfAssigned9.Value,edfDefault9.value),'') AS `Latest`, IFNULL(IFNULL(edfAssigned10.Value,edfDefault10.value),'') AS `Average`, IFNULL(IFNULL(edfAssigned11.Value,edfDefault11.value),'') AS `Highest`, IFNULL(IFNULL(edfAssigned12.Value,edfDefault12.value),'') AS `Lowest` FROM Computers LEFT JOIN inv_operatingsystem ON (Computers.ComputerId=inv_operatingsystem.ComputerId) LEFT JOIN subgroupwchildren ON (Computers.ComputerId=subgroupwchildren.computerid) LEFT JOIN Clients ON (Computers.ClientId=Clients.ClientId) LEFT JOIN Locations ON (Computers.LocationId=Locations.LocationID) LEFT JOIN ExtraFieldData edfAssigned1 ON (edfAssigned1.id=Locations.LocationId AND edfAssigned1.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='a4265955-6283-474c-a5d0-526d3b6c2665')) LEFT JOIN ExtraFieldData edfDefault1 ON (edfDefault1.id=0 AND edfDefault1.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='a4265955-6283-474c-a5d0-526d3b6c2665')) LEFT JOIN ExtraFieldData edfAssigned2 ON (edfAssigned2.id=Locations.LocationId AND edfAssigned2.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='a50a526d-3c18-4c02-8ded-a37f8f217d76')) LEFT JOIN ExtraFieldData edfDefault2 ON (edfDefault2.id=0 AND edfDefault2.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='a50a526d-3c18-4c02-8ded-a37f8f217d76')) LEFT JOIN ExtraFieldData edfAssigned3 ON (edfAssigned3.id=Locations.LocationId AND edfAssigned3.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='72921066-55f2-4916-9449-555d78bc8d2f')) LEFT JOIN ExtraFieldData edfDefault3 ON (edfDefault3.id=0 AND edfDefault3.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='72921066-55f2-4916-9449-555d78bc8d2f')) LEFT JOIN ExtraFieldData edfAssigned4 ON (edfAssigned4.id=Locations.LocationId AND edfAssigned4.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='2ee79806-7cd3-478f-93a6-2dcd8486492f')) LEFT JOIN ExtraFieldData edfDefault4 ON (edfDefault4.id=0 AND edfDefault4.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='2ee79806-7cd3-478f-93a6-2dcd8486492f')) LEFT JOIN ExtraFieldData edfAssigned5 ON (edfAssigned5.id=Locations.LocationId AND edfAssigned5.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='7af16bbb-3aa1-461a-8b3b-0832cf455245')) LEFT JOIN ExtraFieldData edfDefault5 ON (edfDefault5.id=0 AND edfDefault5.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='7af16bbb-3aa1-461a-8b3b-0832cf455245')) LEFT JOIN ExtraFieldData edfAssigned6 ON (edfAssigned6.id=Locations.LocationId AND edfAssigned6.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='06049f2b-1e22-49f6-a55d-1615202e6e2e')) LEFT JOIN ExtraFieldData edfDefault6 ON (edfDefault6.id=0 AND edfDefault6.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='06049f2b-1e22-49f6-a55d-1615202e6e2e')) LEFT JOIN ExtraFieldData edfAssigned7 ON (edfAssigned7.id=Locations.LocationId AND edfAssigned7.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='1a991abf-575a-41da-8612-282d90775381')) LEFT JOIN ExtraFieldData edfDefault7 ON (edfDefault7.id=0 AND edfDefault7.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='1a991abf-575a-41da-8612-282d90775381')) LEFT JOIN ExtraFieldData edfAssigned8 ON (edfAssigned8.id=Locations.LocationId AND edfAssigned8.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='1c4f9db2-439b-4b7d-9500-08867240c7aa')) LEFT JOIN ExtraFieldData edfDefault8 ON (edfDefault8.id=0 AND edfDefault8.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='1c4f9db2-439b-4b7d-9500-08867240c7aa')) LEFT JOIN ExtraFieldData edfAssigned9 ON (edfAssigned9.id=Locations.LocationId AND edfAssigned9.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='5c6d0d76-ad20-4978-877b-9f247c9436e8')) LEFT JOIN ExtraFieldData edfDefault9 ON (edfDefault9.id=0 AND edfDefault9.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='5c6d0d76-ad20-4978-877b-9f247c9436e8')) LEFT JOIN ExtraFieldData edfAssigned10 ON (edfAssigned10.id=Locations.LocationId AND edfAssigned10.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='564d0120-42f0-4fa1-9445-4a73846e975f')) LEFT JOIN ExtraFieldData edfDefault10 ON (edfDefault10.id=0 AND edfDefault10.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='564d0120-42f0-4fa1-9445-4a73846e975f')) LEFT JOIN ExtraFieldData edfAssigned11 ON (edfAssigned11.id=Locations.LocationId AND edfAssigned11.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='1b75a002-ec48-4f78-be1b-f1cfc028fcaa')) LEFT JOIN ExtraFieldData edfDefault11 ON (edfDefault11.id=0 AND edfDefault11.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='1b75a002-ec48-4f78-be1b-f1cfc028fcaa')) LEFT JOIN ExtraFieldData edfAssigned12 ON (edfAssigned12.id=Locations.LocationId AND edfAssigned12.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='d3c9509b-c2dc-4f1e-8342-6709e7c8d407')) LEFT JOIN ExtraFieldData edfDefault12 ON (edfDefault12.id=0 AND edfDefault12.ExtraFieldId =(SELECT ExtraField.id FROM ExtraField WHERE LTGuid='d3c9509b-c2dc-4f1e-8342-6709e7c8d407')) WHERE subgroupwchildren.GroupID = 1638 AND Locations.LocationID <> 73 '@ $results = Invoke-MySqlQuery $query forEach ($result in $results) { $date = (Get-Date).ToString("MMMM dd, yyyy") $client = $result.Client $location = $result.Location $ip = $result.PublicIP $ispHost = $result.Hostname $asn = $result.ASN $city = $result.City $state = $result.State $country = $result.Country $zip = $result.ZipCode $gps = $result.gps $now = $result.latest $avg = $result.average $high = $result.highest $low = $result.lowest $html = @" <!DOCTYPE html> <head> <style> .a1 { font-family: "Segoe UI"; font-size: 24px; font-weight: bold; color: #006b80; vertical-align: top; margin: 0 0 0 0; } .a2 { font-family: "Segoe UI Semibold"; font-size: 21px; color: #006B80; margin: 0 0 3px 0; } .a3 { font-family: "Segoe UI Semibold"; font-size: 19px; color: #006b80; margin: 0 0 0 0; } .a4 { font-family: "Segoe UI"; font-size: 19px; color: #006b80; margin: 0 0 0 0; } .date { font-family: "Segoe UI"; font-size: 13px; color: #000000; } .title { font-family: "Segoe UI Semibold"; font-size: 12px; color: #000000; } .information { font-family: "Segoe UI"; font-size: 12px; color: #000000; } .speeds { } .table2 { margin-left: 0px; margin-right: 0px; margin-top: 0px; margin-bottom: 0px; border-width: 0px; empty-cells: show; width: 720px; } hr { color: grey; width: 720px; height: 1px; } .latest { font-family: "Segoe UI Semibold"; font-size: 19px; background-color: #2196f3; color: #FFFFFF; width: 33%; max-width: 240px; max-height: 240px; text-align: center; } .latest > p { vertical-align: middle; } .highest { font-family: "Segoe UI Semibold"; font-size: 19px; background-color: #72b274; color: #FFFFFF; width: 33%; max-width: 240px; max-height: 240px; text-align: center; } .highest > p { vertical-align: middle; } .lowest { font-family: "Segoe UI Semibold"; font-size: 19px; background-color: #ee6f65; color: #FFFFFF; width: 33%; height: 240px; max-height: 240px; max-width: 240px; text-align: center; } .lowest > p { vertical-align: middle; } .average { font-family: "Segoe UI Semibold"; font-size: 19px; background-color: #805dbd; color: #FFFFFF; height: 240px; width: 33%; max-width: 240px; max-height: 240px; text-align: center; } .average > p { vertical-align: middle; } .row { display: flex; } .column { flex: 50%; } .column2 { text-align: right; vertical-align: middle; } .gutter { width: 80px; max-width: 80px; } .spacer { height: 10px; max-height: 10px; } .spacer2 { height: 25px; max-height: 25px; } </style> </head> <body> <div style="max-width:720px;max-height:3248px;"> <div class="row"> <div class="column"><p class="a1">ISP Information</p><p class="date">$date</p></div> <div class="column2"><img src="http://msinetworks.com/wp-content/uploads/2017/09/400dpiLogo.png" style="height:62px;width:160px;"></div> </div> <hr /> <p class="a2">$client</p> <p class="a3">$location</p> <p class="spacer"></p> <table align="Center" style="width:80%;"> <tr class="title"> <td colspan="2" align="center">Public IP</td> <td colspan="2" align="center">Hostname</td> <td colspan="2" align="center">ASN/Provider</td> </tr> <tr class="information"> <td colspan="2" align="center">$ip</td> <td colspan="2" align="center">$ispHost</td> <td colspan="2" align="center">$asn</td> </tr> <tr class="title"> <td colspan="2">City</td> <td align="center">State</td> <td align="center">Country</td> <td align="center">Zip Code</td> <td align="center">Lat/Long</td> </tr> <tr class="information"> <td colspan="2">$city</td> <td align="center">$state</td> <td align="center">$country</td> <td align="center">$zip</td> <td align="center">$gps</td> </tr> <tr class="spacer2"></tr> </table> <hr /> <p class="spacer"></p> <table class="table2"> <tr> <td class="gutter"></td> <td><p class="a4">Latest Speed</p></td> <td class="gutter"></td> <td><p class="a4">Average Speed</td> <td class="gutter"></td> </tr> <tr> <td class="gutter"></td> <td class="latest">$now</td> <td class="gutter"></td> <td class="average">$avg</td> <td class="gutter"></td> </tr> <tr class="spacer2"></tr> <tr> <td class="gutter"></td> <td><p class="a4">Highest Speed</p></td> <td class="gutter"></td> <td><p class="a4">Lowest Speed</p></td> <td class="gutter"></td> </tr> <tr> <td class="gutter"></td> <td class="highest">$high</td> <td class="gutter"></td> <td class="lowest">$low</td> <td class="gutter"></td> </tr> </table> </div> </body> </html> "@ $outfile = "C:\Users\Public\reports\ " + ($client).replace(" ","_") + ".html" $html | Out-File $outfile }