zhGGQ08k

1#If there is no Out-Clipboard, set it
2If(!(Get-Command Out-Clipboard -ErrorAction SilentlyContinue)){Set-Alias Out-Clipboard "$env:SYSTEMROOTSystem32clip.exe"}
3
4#Get current date
5$Date = get-date -format yyyy-MM-dd
6$Company = "company"
7$Company2 = "company2"
8#Define all files/Paths.
9$Path = "C:$Company2BlockedIP"
10md "$PathHTML$Date" -Force |Out-Null
11$path2 = "$PathHTML$Date"
12$PathWeb = "/HTML/$Date"
13
14#Path = C:/$Company2/BlockedIP
15#Path2 = C:/$Company2/BlockedIP/HTML/2014-07-09
16
17#Define File's used or created in this script.
18$File = "$PathIP-$Date.txt"
19$FileHtml = "$Path2IP-$Date.htm"
20$FileXML = "$PathIP-$Date.xlsx"
21$FileHTMLWeb = "$PathWebIP-$date.htm"
22
23#File = C:/$Company2/BlockedIP/IP-2014-07-09.txt
24#FileXML = C:/$Company2/BlockedIP/HTML/2014-07-09/IP-2014-07-09.htm
25#FileHtml = C:/$Company2/BlockedIP/HTML/2014-07-09/IP-2014-07-09.xlsx
26#FileHTMLWeb = PublicIP/HTML/2014-07-09/IP-2014-07-09.htm
27
28
29
30#Define error actions.
31#$erroractionpreference = "SilentlyContinue"
32
33#Get content from given IP list.
34$colComputers = @(get-content $File | Sort -unique)
35$count = $colComputers.Count
36    write-output "$Count IP's detected."
37
38#Get DNS Results
39$Progress=1
40$DNSResults = $colComputers | %{
41 Write-Progress -Activity "Creating a usable 'Blocked IP' list ($Progress/$count)" -PercentComplete ($Progress/$Count*100) -Status "Please stand by"
42try {
43    ($dnsresult = [System.Net.DNS]::GetHostEntry($_))|out-null
44}
45catch {
46    $dnsresult = "Fail"
47}
48[PSCustomObject][Ordered]@{
49    Source=$_.ToUpper()
50    HostName=$dnsresult.HostName
51    IPAddress=$dnsresult.AddressList[0].ToString()
52}
53$Progress++
54}
55
56$DNSResults | Sort HostName | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard
57
58#Open Excel.
59$a = New-Object -comobject Excel.Application
60
61#Since we want this script to look like it's being used without excel I set it's visibility to false.
62$a.visible = $True
63
64#Disable excel confirmations.
65$a.DisplayAlerts = $False
66
67<# 
68# set interactive to false so nothing from excel is shown.
69$Excel.DisplayAlerts = $false
70$Excel.ScreenUpdating = $false
71$Excel.Visible = $false
72$Excel.UserControl = $false
73$Excel.Interactive = $false
74#>
75
76#Create sheets in Excel.
77$b = $a.Workbooks.Add()
78$c = $b.Worksheets.Item(1)
79$c.Activate() | Out-Null
80
81#Create a Title for the first worksheet and adjust the font
82$c.Cells.Item(1,1)= "Blocked IP's $Date"
83$c.Cells.Item(1,1).Font.ColorIndex = 55
84$c.Cells.Item(1,1).Font.Color = 8210719
85$range = $c.Range("a1","e1")
86$range.Style = 'Title'
87$range.Select()
88$range.MergeCells = $true
89$range.VerticalAlignment = -4108
90
91$CounterRow = $Count+5
92#Define subjects.
93$c.Name = "Blocked IP's ($Date)"
94$c.Cells.Item(2,1) = "Given IP"
95$c.Cells.Item(2,2) = "Resolved DNS"
96$c.Cells.Item(2,3) = "Returned IP"
97$c.Cells.Item(2,5) = "$Company"
98$c.Cells.Item($Count+5,1) = "Created by"
99$link = "http://www.$Company"
100$link2 = "https://www.linkedin.com/profile/view?id=#########"
101$r = $c.Range("E2") 
102[void]$c.Hyperlinks.Add($r, $link) 
103$r = $c.Range("A$Counterrow") 
104[void]$c.Hyperlinks.Add($r, $link)
105
106#Define cell formatting from subjects.
107$c.Range("A2:E2").Interior.ColorIndex = 6
108$c.Range("A2:E2").font.size = 13
109$c.Range("A2:E2").Font.ColorIndex = 1
110$c.Range("A2:E2").Font.Bold = $True
111
112#Define html code for Excel save to .htm.
113$xlExcelHTML = 44
114
115#Define the usedrange, excluding header and footer rows
116$e = $c.Range("A3:E$($DNSResults.Count+2)")
117
118#Populate data into spreadsheet
119$DNSResults | Sort HostName | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard
120#$c.Cells.Item(3,1).Select()
121$c.Paste($e,$false)
122
123$e = $c.Range("A3:C$($DNSResults.Count+2)")
124$c.Paste($e,$false)
125
126$Keywords = "Google","thenetworkfactory","HappyTreeFriends"
127$Filter = "($(($Keywords|%{[RegEx]::Escape($_)}) -join "|"))"
128
129$DNSResults | Where{$_ -match $filter} | Sort HostName | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard
130
131
132ForEach($Cell in $e){
133If([String]::IsNullOrWhitespace($Cell.value2)){$Cell.interior.colorindex=6}
134}
135
136#Define the usedrange for autofitting.
137$d = $c.UsedRange
138
139#Set background color for the IP list.
140$E.interior.colorindex = 15
141
142#Define borders here.
143$xlOpenXMLWorkbook = 51
144$xlAutomatic=-4105
145$xlBottom = -4107
146$xlCenter = -4108
147$xlRight = -4152
148$xlContext = -5002
149$xlContinuous=1
150$xlDiagonalDown=5
151$xlDiagonalUp=6
152$xlEdgeBottom=9
153$xlEdgeLeft=7
154$xlEdgeRight=10
155$xlEdgeTop=8
156$xlInsideHorizontal=12
157$xlInsideVertical=11
158$xlNone=-4142
159$xlThin=2 
160$selection = $c.range("A3:C$($DNSResults.Count+2)")
161$selection.select() |out-null
162$selection.HorizontalAlignment = $xlRight
163$selection.VerticalAlignment = $xlBottom
164$selection.WrapText = $false
165$selection.Orientation = 0
166$selection.AddIndent = $false
167$selection.IndentLevel = 0
168$selection.ShrinkToFit = $false
169$selection.ReadingOrder = $xlContext
170$selection.MergeCells = $false
171$selection.Borders.Item($xlInsideHorizontal).Weight = $xlThin
172
173
174
175#Make everything fit in it's cell.
176$d.EntireColumn.AutoFit() | Out-Null
177
178#Save the file as .xlsx on every placed IP to ensure the file is not lost due to any reason.
179$b.SaveAs("$FileXML")
180
181#Clear screen on every checked IP to remove the 'True' statement.
182#cls
183#
184
185
186#Save final result as a .htm file
187$b.SaveAs("$FileHTML",$xlExcelHTML)
188
189#Close and quit Excel.
190$b.Close()
191get-process *Excel* | Stop-Process -force
192
193#Move .txt file to the correct HTML folder.
194move-item $file $path2 -Force
195
196#Move .xlsx file to the correct HTML folder.
197move-item $filexml $path2 -Force
198
199#Declare XLSX file for mail
200$MailXML = "$path2IP-$Date.xlsx"
201
202#Clear screen, again. (Let's keep things tidy.)
203#cls
204
205#Variables for public IP
206# I am defining website url in a variable
207$url = "http://checkip.dyndns.com" 
208# Creating a new .Net Object names a System.Net.Webclient
209$webclient = New-Object System.Net.WebClient
210# In this new webdownlader object we are telling $webclient to download the
211# url $url 
212$IpPublic = $webclient.DownloadString($url)
213# Just a simple text manuplation to get the ipadress form downloaded URL
214# If you want to know what it contain try to see the variable $IpPublic
215$IpPublic2 = $IpPublic.ToString()
216$ipPublic3 = $IpPublic2.Split(" ")
217$ipPublic4 = $ipPublic3[5]
218$ipPublic5 = $ipPublic4.replace("</body>","")
219$FinalIPAddress = $ipPublic5.replace("</html>","")
220$ipLocal = (Get-WmiObject -class win32_NetworkAdapterConfiguration -Filter 'ipenabled = "true"').ipaddress[0]
221
222#Variables e-mail.
223$From = "Blocked IP <r.van.tour@$Company>"
224$To = "IT Dept <r.van.tour@$Company>"
225$CC = "Someone <$Company2@$Company"
226$Subject = "Blocked IPs for $date ($Count Total)"
227#The href should point to the htm file in your iis/apache folder.
228$WebLink = $FinalIPAddress+$FileHtmlWeb
229$here = "<a href='http://$Weblink'><b>Here</b></a>"
230#Define the body of your e-mail, in this case it displays a message and shows the server it is send from with it's local IP.
231#A link to the .htm file, how many IP's were blocked and the date of the message.
232$Body = "<!DOCTYPE html><html><head> <title>Blocked IP's $Date</title></head><header><h1>Blocked IP</h1><p><time pubdate datetime='$date'></time></p></header><br>" 
233$body += "<body>Dear <font color=black>$to</font>,<br><br>"
234$body += "This is an automated message generated by server: <font color=red><b>$env:COMPUTERNAME, $IPLocal.</b></font><br><br>"
235$body += "Click <font color=red><b>$here</b></font> to see the Blocked IP report for $date containing $count IP's.<br>"
236$body += "Or see the attachment to open it in Excel.<br></body></html>"
237
238
239#Clear screen, again. (Let's keep things tidy.)
240#cls
241
242#Send output as e-mail.
243$SMTPServer = "smtp.gmail.com"
244$SMTPPort = "587"
245$Username = "##@gmail.com"
246$Password = "##"
247$message = New-Object System.Net.Mail.MailMessage
248$message.IsBodyHTML = $true
249$message.ReplyTo = $From
250$message.Sender = $From
251$message.subject = $subject
252$message.body = $body
253$message.to.add($to)
254$message.from = $From
255$message.attachments.add($MailXML)
256$smtp = New-Object System.Net.Mail.SmtpClient($SMTPServer, $SMTPPort);
257$smtp.EnableSSL = $true
258$smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password);
259$smtp.send($message)
260
261#Create a function to relase Com object at end of script.
262function Release-Ref ($ref) { 
263([System.Runtime.InteropServices.Marshal]::ReleaseComObject( 
264[System.__ComObject]$ref) -gt 0) 
265[System.GC]::Collect() 
266[System.GC]::WaitForPendingFinalizers() 
267                        }
268
269#Release COM Object
270[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$a) | Out-Null
271
272#Clear screen for the final time. (Let's keep things tidy.)
273#cls
274
275#Exit powershell 
276exit
277	
278$Keywords = "Google","Cloudflare","Cloud","Ping","Easy-    Voyage","McAfee","Pingdom","Panopta","Scoot","Uniglobe"
279$Filter = "($(($Keywords|%{[RegEx]::Escape($_)}) -join "|"))"
280
281#Define error actions.
282#$erroractionpreference = "SilentlyContinue"
283
284#Get content from given IP list.
285$colComputers = @(get-content $File | Sort -unique)
286$SourceCount = $colComputers.Count
287write-output "$SourceCount IP's detected."
288
289#Get DNS Results
290$Progress=1
291$DNSResults = $colComputers | %{
292Write-Progress -Activity "Creating a usable 'Blocked IP' list ($Progress/$sourcecount)" -PercentComplete ($Progress/$sourceCount*100) -Status "Please stand by"
293try {
294    ($dnsresult = [System.Net.DNS]::GetHostEntry($_))|out-null
295}
296catch {
297    $dnsresult = "Fail"
298}
299[PSCustomObject][Ordered]@{
300    Source=$_.ToUpper()
301    HostName=$dnsresult.HostName
302    IPAddress=$dnsresult.AddressList[0].ToString()
303}
304$Progress++
305}
306
307$count = ($DNSResults|?{$_ -match $filter}).count
308	
309#Get DNS Results
310$Progress=1
311$DNSResults = $colComputers | %{
312     Write-Progress -Activity "Creating a usable 'Blocked IP' list ($Progress/$count)" -PercentComplete ($Progress/$Count*100) -Status "Please stand by"
313    try {
314        ($dnsresult = [System.Net.DNS]::GetHostEntry($_))|out-null
315    }
316    catch {
317        $dnsresult = "Fail"
318    }
319    [PSCustomObject][Ordered]@{
320        Source=$_.ToUpper()
321        HostName=$dnsresult.HostName
322        IPAddress=$dnsresult.AddressList[0].ToString()
323    }
324    $Progress++
325}
326	
327If(!(Get-Command Out-Clipboard -ErrorAction SilentlyContinue)){Set-Alias Out-Clipboard "$env:SYSTEMROOTSystem32clip.exe"}
328	
329$DNSResults | Sort HostName | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard
330	
331$e = $c.Range("A3:E$($DNSResults.Count+2)")
332$c.Paste($e,$false)
333	
334$Keywords = "Google","Facebook","HappyTreeFriends"
335$Filter = "($(($Keywords|%{[RegEx]::Escape($_)}) -join "|"))"
336	
337$DNSResults | Where{$_ -match $filter} | Sort HostName | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard
338	
339#If there is no Out-Clipboard, set it
340If(!(Get-Command Out-Clipboard -ErrorAction SilentlyContinue)){Set-Alias Out-Clipboard "$env:SYSTEMROOTSystem32clip.exe"}
341
342#Get current date
343$Date = get-date -format yyyy-MM-dd
344$Company = "company"
345$Company2 = "company2"
346#Define all files/Paths.
347$Path = "C:$Company2BlockedIP"
348md "$PathHTML$Date" -Force |Out-Null
349$path2 = "$PathHTML$Date"
350$PathWeb = "/HTML/$Date"
351
352#Path = C:/$Company2/BlockedIP
353#Path2 = C:/$Company2/BlockedIP/HTML/2014-07-09
354
355#Define File's used or created in this script.
356$File = "$PathIP-$Date.txt"
357$FileHtml = "$Path2IP-$Date.htm"
358$FileXML = "$PathIP-$Date.xlsx"
359$FileHTMLWeb = "$PathWebIP-$date.htm"
360
361#File = C:/$Company2/BlockedIP/IP-2014-07-09.txt
362#FileXML = C:/$Company2/BlockedIP/HTML/2014-07-09/IP-2014-07-09.htm
363#FileHtml = C:/$Company2/BlockedIP/HTML/2014-07-09/IP-2014-07-09.xlsx
364#FileHTMLWeb = PublicIP/HTML/2014-07-09/IP-2014-07-09.htm
365
366$Keywords = "Google","thenetworkfactory"
367$Filter = "($(($Keywords|%{[RegEx]::Escape($_)}) -join "|"))"
368
369#Define error actions.
370#$erroractionpreference = "SilentlyContinue"
371
372#Get content from given IP list.
373$colComputers = @(get-content $File | Sort -unique)
374$SourceCount = $colComputers.Count
375    write-output "$Count IP's detected."
376
377#Get DNS Results
378$Progress=1
379$DNSResults = $colComputers | %{
380 Write-Progress -Activity "Creating a usable 'Blocked IP' list ($Progress/$sourcecount)" -PercentComplete ($Progress/$sourceCount*100) -Status "Please stand by"
381try {
382    ($dnsresult = [System.Net.DNS]::GetHostEntry($_))|out-null
383}
384catch {
385    $dnsresult = "Fail"
386}
387[PSCustomObject][Ordered]@{
388    Source=$_.ToUpper()
389    HostName=$dnsresult.HostName
390    IPAddress=$dnsresult.AddressList[0].ToString()
391}
392$Progress++
393}
394
395$count = ($DNSResults|?{$_ -match $filter}).count
396
397#Open Excel.
398$a = New-Object -comobject Excel.Application
399
400#Since we want this script to look like it's being used without excel I set it's visibility to false.
401$a.visible = $True
402
403#Disable excel confirmations.
404$a.DisplayAlerts = $False
405
406<# 
407# set interactive to false so nothing from excel is shown.
408$Excel.DisplayAlerts = $false
409$Excel.ScreenUpdating = $false
410$Excel.Visible = $false
411$Excel.UserControl = $false
412$Excel.Interactive = $false
413#>
414
415#Create sheets in Excel.
416$b = $a.Workbooks.Add()
417$c = $b.Worksheets.Item(1)
418$c.Activate() | Out-Null
419
420#Create a Title for the first worksheet and adjust the font
421$c.Cells.Item(1,1)= "Blocked IP's $Date"
422$c.Cells.Item(1,1).Font.ColorIndex = 55
423$c.Cells.Item(1,1).Font.Color = 8210719
424$range = $c.Range("a1","e1")
425$range.Style = 'Title'
426$range.Select()
427$range.MergeCells = $true
428$range.VerticalAlignment = -4108
429
430$CounterRow = $Count+5
431#Define subjects.
432$c.Name = "Blocked IP's ($Date)"
433$c.Cells.Item(2,1) = "Given IP"
434$c.Cells.Item(2,2) = "Resolved DNS"
435$c.Cells.Item(2,3) = "Returned IP"
436$c.Cells.Item(2,5) = "$Company"
437$c.Cells.Item($Count+5,1) = "Created by"
438$link = "http://www.$Company"
439$link2 = "https://www.linkedin.com/profile/view?id=#########"
440$r = $c.Range("E2") 
441[void]$c.Hyperlinks.Add($r, $link) 
442$r = $c.Range("A$Counterrow") 
443[void]$c.Hyperlinks.Add($r, $link)
444
445#Define cell formatting from subjects.
446$c.Range("A2:E2").Interior.ColorIndex = 6
447$c.Range("A2:E2").font.size = 13
448$c.Range("A2:E2").Font.ColorIndex = 1
449$c.Range("A2:E2").Font.Bold = $True
450
451#Define html code for Excel save to .htm.
452$xlExcelHTML = 44
453
454#Define the usedrange, excluding header and footer rows
455$e = $c.Range("A3:E$(2+$Count)")
456
457#Populate data into spreadsheet
458$DNSResults | Where{$_ -match $filter} | Sort HostName | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard
459#$c.Cells.Item(3,1).Select()
460$c.Paste($e,$false)
461
462ForEach($Cell in $e){
463If([String]::IsNullOrWhitespace($Cell.value2)){$Cell.interior.colorindex=6}
464}
465
466#Define the usedrange for autofitting.
467$d = $c.UsedRange
468
469#Set background color for the IP list.
470$E.interior.colorindex = 15
471
472#Define borders here.
473$xlOpenXMLWorkbook = 51
474$xlAutomatic=-4105
475$xlBottom = -4107
476$xlCenter = -4108
477$xlRight = -4152
478$xlContext = -5002
479$xlContinuous=1
480$xlDiagonalDown=5
481$xlDiagonalUp=6
482$xlEdgeBottom=9
483$xlEdgeLeft=7
484$xlEdgeRight=10
485$xlEdgeTop=8
486$xlInsideHorizontal=12
487$xlInsideVertical=11
488$xlNone=-4142
489$xlThin=2 
490$selection = $c.range("A3:C$($DNSResults.Count+2)")
491$selection.select() |out-null
492$selection.HorizontalAlignment = $xlRight
493$selection.VerticalAlignment = $xlBottom
494$selection.WrapText = $false
495$selection.Orientation = 0
496$selection.AddIndent = $false
497$selection.IndentLevel = 0
498$selection.ShrinkToFit = $false
499$selection.ReadingOrder = $xlContext
500$selection.MergeCells = $false
501$selection.Borders.Item($xlInsideHorizontal).Weight = $xlThin
502
503
504
505#Make everything fit in it's cell.
506$d.EntireColumn.AutoFit() | Out-Null
507
508#Save the file as .xlsx on every placed IP to ensure the file is not lost due to any reason.
509$b.SaveAs("$FileXML")
510
511#Clear screen on every checked IP to remove the 'True' statement.
512#cls
513#
514
515
516#Save final result as a .htm file
517$b.SaveAs("$FileHTML",$xlExcelHTML)
518
519#Close and quit Excel.
520$b.Close()
521get-process *Excel* | Stop-Process -force
522
523#Move .txt file to the correct HTML folder.
524move-item $file $path2 -Force
525
526#Move .xlsx file to the correct HTML folder.
527move-item $filexml $path2 -Force
528
529#Declare XLSX file for mail
530$MailXML = "$path2IP-$Date.xlsx"
531
532#Clear screen, again. (Let's keep things tidy.)
533#cls
534
535#Variables for public IP
536# I am defining website url in a variable
537$url = "http://checkip.dyndns.com" 
538# Creating a new .Net Object names a System.Net.Webclient
539$webclient = New-Object System.Net.WebClient
540# In this new webdownlader object we are telling $webclient to download the
541# url $url 
542$IpPublic = $webclient.DownloadString($url)
543# Just a simple text manuplation to get the ipadress form downloaded URL
544# If you want to know what it contain try to see the variable $IpPublic
545$IpPublic2 = $IpPublic.ToString()
546$ipPublic3 = $IpPublic2.Split(" ")
547$ipPublic4 = $ipPublic3[5]
548$ipPublic5 = $ipPublic4.replace("</body>","")
549$FinalIPAddress = $ipPublic5.replace("</html>","")
550$ipLocal = (Get-WmiObject -class win32_NetworkAdapterConfiguration -Filter 'ipenabled = "true"').ipaddress[0]
551
552#Variables e-mail.
553$From = "Blocked IP <r.van.tour@$Company>"
554$To = "IT Dept <r.van.tour@$Company>"
555$CC = "Someone <$Company2@$Company"
556$Subject = "Blocked IPs for $date ($Count Total)"
557#The href should point to the htm file in your iis/apache folder.
558$WebLink = $FinalIPAddress+$FileHtmlWeb
559$here = "<a href='http://$Weblink'><b>Here</b></a>"
560#Define the body of your e-mail, in this case it displays a message and shows the server it is send from with it's local IP.
561#A link to the .htm file, how many IP's were blocked and the date of the message.
562$Body = "<!DOCTYPE html><html><head> <title>Blocked IP's $Date</title></head><header><h1>Blocked IP</h1><p><time pubdate datetime='$date'></time></p></header><br>" 
563$body += "<body>Dear <font color=black>$to</font>,<br><br>"
564$body += "This is an automated message generated by server: <font color=red><b>$env:COMPUTERNAME, $IPLocal.</b></font><br><br>"
565$body += "Click <font color=red><b>$here</b></font> to see the Blocked IP report for $date containing $count IP's.<br>"
566$body += "Or see the attachment to open it in Excel.<br></body></html>"
567
568
569#Clear screen, again. (Let's keep things tidy.)
570#cls
571
572#Send output as e-mail.
573$SMTPServer = "smtp.gmail.com"
574$SMTPPort = "587"
575$Username = "##@gmail.com"
576$Password = "##"
577$message = New-Object System.Net.Mail.MailMessage
578$message.IsBodyHTML = $true
579$message.ReplyTo = $From
580$message.Sender = $From
581$message.subject = $subject
582$message.body = $body
583$message.to.add($to)
584$message.from = $From
585$message.attachments.add($MailXML)
586$smtp = New-Object System.Net.Mail.SmtpClient($SMTPServer, $SMTPPort);
587$smtp.EnableSSL = $true
588$smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password);
589$smtp.send($message)
590
591#Create a function to relase Com object at end of script.
592function Release-Ref ($ref) { 
593([System.Runtime.InteropServices.Marshal]::ReleaseComObject( 
594[System.__ComObject]$ref) -gt 0) 
595[System.GC]::Collect() 
596[System.GC]::WaitForPendingFinalizers() 
597                        }
598
599#Release COM Object
600[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$a) | Out-Null
601
602#Clear screen for the final time. (Let's keep things tidy.)
603#cls
604
605#Exit powershell 
606exit
607	
608[PSCustomObject][Ordered]@{
609    Source=$_.ToUpper()
610    HostName=$(if(!([string]::IsNullOrEmpty($dnsresult.HostName))){$dnsresult.HostName})
611    IPAddress=$(if(!([string]::IsNullOrEmpty($dnsresult.AddressList))){$dnsresult.AddressList[0].ToString()})
612}
613	
614$DNSWithKeyword = $DNSResults | ?{$_.HostName -match $Filter}
615$DNSNoKeyword = $DNSResults | ?{!($_.HostName -match $Filter) -and !([string]::IsNullOrEmpty($_.HostName))}
616$DNSLookupFailed = $DNSResults | ?{([string]::IsNullOrEmpty($_.HostName))}
617#$count = ($DNSResults|?{$_ -match $filter}).count
618$count = $SourceCount
619	
620#Define the usedrange, excluding header and footer rows
621$KeyRange = $c.Range("A3:E$(2+$DNSWithKeyword.Count)")
622$NoKeyRange = $c.Range("A$(2+$DNSWithKeyword.Count+1):E$(2+$DNSWithKeyword.Count+$DNSNoKeyword.Count)")
623$NoDNSRange = $c.Range("A$(2+$DNSWithKeyword.Count+$DNSNoKeyword.Count+1):E$(2+$DNSWithKeyword.Count+$DNSNoKeyword.Count+$DNSLookupFailed.Count)")
624$e = $c.Range("A3:E$(2+$Count)")
625
626#Populate data into spreadsheet
627$DNSWithKeyword | Sort HostName | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard
628$c.Paste($KeyRange,$false)
629$DNSNoKeyword | Sort HostName | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard
630$c.Paste($NoKeyRange,$false)
631$DNSLookupFailed | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard
632$c.Paste($NoDNSRange,$false)
633
634#Define the usedrange for autofitting.
635$d = $c.UsedRange
636
637#Set background color for the IP list.
638$KeyRange.interior.colorindex = 3
639$NoKeyRange.interior.colorindex = 15
640$NoDNSRange.interior.colorindex = 14
641
642ForEach($Cell in $e){
643If([String]::IsNullOrWhitespace($Cell.value2)){$Cell.interior.colorindex=6}
644}