Powershell converting IP's to resolved DNS - Export to Excel

Question

I am having some issues with my script.

What it does is reading a folder in outlook that contains blocked IP's. That said it resolves those IP's to their DNS name. (If someone know a better way in powershell then in the script please let me know, as now a lot of the times it does not resolve IP's)

Once IP's have been resolved they should be divided in 3 category's; Cat1: Resolved IP's that match a filter Cat2: Resolved IP's that do not match a filter Cat3: IP's that could not be resolved.

Just so you can skip the whole mail part I have included a small list of IP's you could test on to see what I mean. (Script execution should take about 1 minute)

Now for the problem(s)

Category's: (SOLVED by the Mad Technician) They are not functional as they should... Cat1 and 2 will get mixed up somehow. Cat 3 does not even show...

Category's: (SOLVED by the Mad Technician) I have a few unresolved DNS adresses in my filter that I know are good. Is it possible to give it a hostname myself so it shows on the excel sheet? e.g 141.101.105.12 should have hostname CloudFlare Personally I know it's Cloudflare, but the rest of the people do not.

Excel 1: (SOLVED, ranges set were incorrect) Excel should make a nice sheet, it looks like ****, it throws the cat's in the wrong places, deforming the sheet.

Excel 2: (SOLVED by the Mad Technician) I would also very much like a way to create a link to every IP, that should be unique, if you see the script you'd understand what I am talking about, and what I am trying to do... I hope.

List of test IP's: (should be in a .txt file)

199.27.128.103
173.245.53.70
173.245.53.137
173.245.53.121
173.245.53.104
173.245.53.103
173.245.51.69
141.101.105.12
141.101.105.121
141.101.105.14
141.101.105.15
141.101.105.170
108.162.254.116
127.0.0.1
64.39.103.176
0.0.0.0
111.111.311.25
254.254.254.254
187.159.165.1

-Clearly some IP's should be shown in unresolved category.

As for the script:

#Get current date
$Date = date -format yyyy-MM-dd
$Company = "Company1"
    $Company2 = "Company2"
        $Link = "https://"
        ########################


#Define all Paths.
$Path = "C:\inetpub\wwwroot\BlockedIP"
    md "$Path\HTML\$Date" -Force |Out-Null
    $path2 = "$Path\HTML\$Date"
$PathWeb = "/BlockedIp/HTML/$Date"
########################


#Define File's used or created in this script.
$File = "$Path\IP-$Date.txt"
    $FileHtml = "$Path2\IP-$Date.htm"
        $FileXML = "$Path\IP-$Date.xlsx"
            $FileHTMLWeb = "$PathWeb\IP-$date.htm"
            ######################################


#Define error actions.
$erroractionpreference = "SilentlyContinue"
###########################################

#Since the script used COM objects it will need the following 2 maps:

#(32Bit)
MD "C:\Windows\System32\config\systemprofile\Dektop" -force
    MD "C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Windows\Temporary Internet" -force
#(64Bit)
MD "C:\Windows\SysWOW64\config\systemprofile\Desktop" -force
    MD "C:\Windows\SysWOW64\config\systemprofile\AppData\Local\Microsoft\Windows\Temporary Internet" -force
#Once successfull the script will run without a problem if scheduled.

cls

start Outlook
Function Get-OutlookInBox 
    { 
    Add-type -assembly "Microsoft.Office.Interop.Outlook" | out-null 
    $olFolders = "Microsoft.Office.Interop.Outlook.olDefaultFolders" -as [type]  
    $outlook = new-object -comobject outlook.application 

        $namespace = $outlook.GetNameSpace("MAPI") 
        $folder = $namespace.getDefaultFolder($olFolders::olFolderInBox) 
            $folder.items |  
            Select -Property Subject, ReceivedTime, Importance, SenderName, body 
    } #end function Get-OutlookInbox
    ###################################################################################

    cls

try {
    $switches = get-outlookinbox | where subject -eq "Ip was blocked"
        $e = $switches.body
    $e = $e -replace 'Blocked IP:| for agent| |:\d{1,2}'
    #foreach ($n in 999..1) { $e = $e.replace(":$n", "") }
     #   $e = $e.replace("Blocked IP:","")
      #  $e = $e.replace(" for agent","")
       # $e = $e.replace(" ","")

                    }
    catch {
          $switches = "Fail"
          }

    $f = $e |select -Unique |sort


    ni $File -type file
        $f | ac $File
            (gc $File) | ? {$_.trim() -ne "" } | sc $File
            $IPCount =  (gc $File)
            $IPCount =  $IPCount.count

    $index=0;  

    #Mark mails as read and delete.
    function display( [string]$subject, [string]$color , [string]$out)  {

    # REQUIRED LENGTH OF STRING
    $len = 20

    # STRINGS THAT ARE LONGER WILL BE CUT DOWN,
    # STRINGS THAT ARE TO SHORT WILL BE MADE LONGER
    if ( $subject.length -lt 20 ){
        $toadd=20-$subject.length;
        for ( $i=0; $i -lt $toadd; $i++ ){
            $subject=$subject+" ";
        }
        $len = $subject.length
    }
    else { $len = 20 }

    $index=$index+1
    Write -ForegroundColor $color -nonewline " |" ((($subject).ToString()).Substring(0,$len)).ToUpper()
}
$outlook = new-object -comobject outlook.application

#Define folders
$namespace = $outlook.GetNameSpace("MAPI")
$pst = $namespace.Stores
$pstRoot = $pst.GetRootFolder()
$pstFolders = $pstRoot.Folders
#$personal = $pstFolders.Items("ARCHIVE")  ##Not working, sadly.
$DefaultFolder = $namespace.GetDefaultFolder(6)
$InboxFolders = $DefaultFolder.Folders
$DeletedItems = $namespace.GetDefaultFolder(3)
$Emails = $DefaultFolder.Items

For($i=($emails.count-1);$i -ge 0;$i--){
    $($emails)[$i].Unread = $false
    $($emails)[$i].delete()
}




write "$IPCount unique IP addresses detected."

gps *Outlook* | Stop-Process -force



#Define error actions.
$erroractionpreference = "SilentlyContinue"



#Get content from given IP list.
$colComputers = @(gc $File | sort |Select -unique)
    $SourceCount = $colComputers.Count
    write "$SourceCount IP's detected."

Function Set-KnownIPs{
Param([Object]$DNSLookupObject)
Switch($DNSLookupObject){
    {$_.Source -Match "(108.162.254|141.101.(?:104|105)|199.27.128|173.245(?:53|52|51))"}{$_.HostName = "CloudFlare, Inc."}
    {$_.Source -Match "(64.18.[0-18])"}{$_.HostName = "Google, Inc."}
    {$_.Source -Match "(192.168|127.0.0)"}{$_.HostName = "Internal Infrastructure"}
}
$DNSLookupObject
}

#Get DNS Results
$Progress=1
$DNSResults = $colComputers | %{
Write-Progress -Activity "Creating a usable 'Blocked IP' list ($Progress/$sourcecount)" -PercentComplete ($Progress/$sourceCount*100) -Status "Please stand by"
try {
    ($dnsresult = [System.Net.DNS]::GetHostEntry($_))
}
catch {
    $dnsresult = "Fail"
}
Set-KnownIPs -DNSLookupObject ([PSCustomObject][Ordered]@{
Source=$_.ToUpper()
HostName=$(if(!([string]::IsNullOrEmpty($dnsresult.HostName))){$dnsresult.HostName})
IPAddress=$(if(!([string]::IsNullOrEmpty($dnsresult.AddressList))){$dnsresult.AddressList[0].ToString()})
})
$Progress++
}

$Keywords = "Google","Cloudflare","Cloud","Ping", `
"Easy-Voyage","McAfee","Pingdom","Panopta","Scoot","Uniglobe", `
"Internal"

$Filter = "($(($Keywords|%{[RegEx]::Escape($_)}) -join "|"))"


$DNSLookupFailed = $DNSResults | 
?{[string]::IsNullOrEmpty($_.HostName) -and !($_ -match $filter)}

$DNSWithKeyword = $DNSResults | 
?{$_ -match $Filter}

$DNSNoKeyword = $DNSResults | 
?{!($_.HostName -match $Filter) -and !([string]::IsNullOrEmpty($_.IPAddress))}


#$count = ($DNSResults|?{$_ -match $filter}).count
$count = $SourceCount
#####################


#start Excel.
$a = New-Object -comobject Excel.Application

# set interactive to false so nothing from excel is shown.
$a.DisplayAlerts = $False
$a.ScreenUpdating = $True
$a.Visible = $True
$a.UserControl = $True
$a.Interactive = $True
###########################


#Create sheets in Excel.
$b = $a.Workbooks.Add()
    $c = $b.Worksheets.Item(1)
    $c.Activate() | Out-Null

#Create a Title for the first worksheet and adjust the font
$c.Cells.Item(1,1)= "Blocked IP's $Date"
    $c.Cells.Item(1,1).Font.ColorIndex = 55
    $c.Cells.Item(1,1).Font.Color = 8210719

$c.Cells.Item((3+$DNSWithKeyword.Count+1),1) = "IP's not in whitelist"
    $c.Cells.Item((3+$DNSWithKeyword.Count+1),1).Font.ColorIndex = 55
    $c.Cells.Item((3+$DNSWithKeyword.Count+1),1).Font.Color = 8210719

$c.Cells.Item((3+$DNSWithKeyword.Count+$DNSNoKeyword.Count+3),1)= "IP's without DNS return"
    $c.Cells.Item((3+$DNSWithKeyword.Count+$DNSNoKeyword.Count+3),1).Font.ColorIndex = 55
    $c.Cells.Item((3+$DNSWithKeyword.Count+$DNSNoKeyword.Count+3),1).Font.Color = 8210719
    #######################################


$range = $c.Range("a1","e1")
$range.Style = 'Title'
$range.Select()
$range.MergeCells = $true
$range.VerticalAlignment = -4108
################################


#Define row to be used for linkedin link.
$CounterRow = $Count+5
######################


#Define subjects.
$c.Name = "Blocked IP's ($Date)"
$c.Cells.Item(2,1) = "Given IP"
$c.Cells.Item(2,2) = "Resolved DNS"
$c.Cells.Item(2,3) = "Returned IP"
$c.Cells.Item(2,5) = "$Company"
$c.Cells.Item((3+$DNSWithKeyword.Count+$DNSNoKeyword.Count+$DNSLookupFailed.Count+5),1) = "Created by"
########################################


$link = "http://www.$Company"
    $link2 = "$Linkedin"

$r = $c.Range("E2") 
    [void]$c.Hyperlinks.Add($r, $link) 

$r = $c.Range("A$(3+$DNSWithKeyword.Count+$DNSNoKeyword.Count+$DNSLookupFailed.Count+5)") 
    [void]$c.Hyperlinks.Add($r, $link2)
    ###################################

#Define cell formatting from subjects.
$c.Range("A2:E2").Interior.ColorIndex = 6
$c.Range("A2:E2").font.size = 13
$c.Range("A2:E2").Font.ColorIndex = 1
$c.Range("A2:E2").Font.Bold = $True
###################################


#Define the usedrange, excluding header and footer rows
$KeyRange = $c.Range("A3:c$(3+$DNSWithKeyword.Count)")
$NoKeyRange = $c.Range("A$(3+$DNSWithKeyword.Count+2):c$(3+$DNSWithKeyword.Count+$DNSNoKeyword.Count+2)") 
$NoDNSRange = $c.Range("A$(3+$DNSWithKeyword.Count+$DNSNoKeyword.Count+4):c$(3+$DNSWithKeyword.Count+$DNSNoKeyword.Count+$DNSLookupFailed.Count+4)")
$SheetRange = $c.Range("A3:e$(4+$DNSWithKeyword.Count+$DNSNoKeyword.Count+$DNSLookupFailed.Count+4)")
$Investigate = $c.Range("c$(3+$DNSWithKeyword.Count+$DNSNoKeyword.Count+4):c$(3+$DNSWithKeyword.Count+$DNSNoKeyword.Count+$DNSLookupFailed.Count+4)")
    ################################


#Set background color for the IP list.
$SheetRange.interior.colorindex = 6
$KeyRange.interior.colorindex = 4
$NoKeyRange.interior.colorindex = 15
$NoDNSRange.interior.colorindex = 8
####################################

#Populate data into spreadsheet
$DNSWithKeyword | Select Source, HostName, IPAddress | Sort HostName -Descending | 
ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | 
Select -Skip 1 | Clip
$c.Paste($KeyRange,$false)

$DNSNoKeyword | Select Source, HostName, IPAddress | Sort HostName -Descending | 
ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | 
Select -Skip 1 | Clip
    $c.Paste($NoKeyRange,$false)

$DNSLookupFailed | Select Source, HostName, IPAddress | sort Source -Descending|
ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | 
Select -Skip 1 | Clip
    $c.Paste($NoDNSRange,$false)
    ############################



ForEach($Cell in $Investigate){
If([String]::IsNullOrWhitespace($Cell.value2)){
$ip = ""
$link3 = "http://who.is/whois/$IP"
$Cell.Item($_) = "Please invesigate"
[void]$cell.Hyperlinks.Add($Cell,$link3)
    }
}

###########################################################################


#Define borders here.
$xlOpenXMLWorkbook = 51
$xlAutomatic=-4105
$xlBottom = -4107
$xlCenter = -4108
$xlRight = -4152
$xlContext = -5002
$xlContinuous=1
$xlDiagonalDown=5
$xlDiagonalUp=6
$xlEdgeBottom=9
$xlEdgeLeft=7
$xlEdgeRight=10
$xlEdgeTop=8
$xlInsideHorizontal=12
$xlInsideVertical=11
$xlNone=-4142
$xlThin=2 
#########    

$selection = $c.range("A2:C$(1+$DNSResults.Count-9)")
    $selection.select() |out-null
    $selection.HorizontalAlignment = $xlRight
    $selection.VerticalAlignment = $xlBottom
    $selection.WrapText = $false
    $selection.Orientation = 0
    $selection.AddIndent = $false
    $selection.IndentLevel = 0
    $selection.ShrinkToFit = $false
    $selection.ReadingOrder = $xlContext
    $selection.MergeCells = $false
    $selection.Borders.Item($xlInsideHorizontal).Weight = $xlThin
    #############################################################


#Define the usedrange for autofitting.
$d = $c.UsedRange
#################

#Make everything fit in it's cell.
$d.EntireColumn.AutoFit() | Out-Null
####################################

$D | Where{$_.Value2 -match "(\b(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]‌?[0-9][0-9]?)\b)"} | 
ForEach{$IPLink = "http://who.is/whois/$($Matches[1])";[void]$c.Hyperlinks.Add($_, $IPLink)}

#Define html code for Excel save to .htm.
$xlExcelHTML = 44
#################

#Save final result as an .xlsx file.
$b.SaveAs("$FileXML")
#####################

#Save final result as a .htm file
$b.SaveAs("$FileHTML",$xlExcelHTML)
###################################

#Close and quit Excel.
$b.Close()
##########

#Make sure excel and outlook is fully closed.
gps *Excel* | Stop-Process -force
#########################################


#Clear screen.
cls
###


#Move .txt file to the correct HTML folder.
mi $file $path2 -Force
#############################

#Move .xlsx file to the correct HTML folder.
mi $filexml $path2 -Force
################################


#Declare XLSX file for mail.
$MailXML = "$path2\IP-$Date.xlsx"
#################################


#Clear screen, again. (Let's keep things tidy.)
cls
###


#Variables for public IP
$url = "http://checkip.dyndns.com" 
$webclient = New-Object System.Net.WebClient
$IpPublic = $webclient.DownloadString($url)
$IpPublic2 = $IpPublic.ToString()
$ipPublic3 = $IpPublic2.Split(" ")
$ipPublic4 = $ipPublic3[5]
$ipPublic5 = $ipPublic4.replace("</body>","")
$FinalIPAddress = $ipPublic5.replace("</html>","")
$ipLocal = (Get-WmiObject -class win32_NetworkAdapterConfiguration `
-Filter 'ipenabled = "true"').ipaddress[0]
##########################################

#The href should point to the htm file in the iis/apache folder.
$WebLink = $FinalIPAddress+$FileHtmlWeb
    $here = "<a href='http://$Weblink'><b>Here</b></a>"
#######################################################


#Define From, To, CC and subject.
$From = "Blocked IP <r.van.tour@$Company>"
$To = "IT Dept <r.van.tour@$Company>"
$CC = "- <-@$Company>"
$Subject = "Blocked IPs for $date ($Count Total)"
#################################################


<#
Define the body of the e-mail, in this case
it displays a message and shows the 
server it is send from with it's local IP.

A link to the .htm file, how many IP's were blocked 
and the date of the message.
#>
$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>" 
    $body += "<body>Dear <font color=black>$to</font>,<br><br>"
    $body += "This is an automated message generated by server: <font color=red><b>$env:COMPUTERNAME, $IPLocal.</b></font><br><br>"
    $body += "Click <font color=red><b>$here</b></font> to see the Blocked IP report for $date containing $count IP's.<br>"
    $body += "Or see the attachment to open it in Excel.<br></body></html>"
###########################################################################


#Clear screen, again. (Let's keep things tidy.)
cls
###

#Define SMTP server.
$SMTPServer = "smtp.gmail.com"
$SMTPPort = "587"
################

#Define credentiala mail sender.
$Username = "-"
$Password = "-"
######################

#Define mail.
$message = New-Object System.Net.Mail.MailMessage
$message.IsBodyHTML = $true
$message.ReplyTo = $From
$message.Sender = $From
$message.subject = $subject
$message.body = $body
$message.to.add($to)
$message.cc.add($cc)
$message.from = $From
$message.attachments.add($MailXML)
    $smtp = New-Object System.Net.Mail.SmtpClient($SMTPServer, $SMTPPort);
        $smtp.EnableSSL = $true
        $smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password);
        $smtp.send($message)
        ####################


#Create a function to relase Com object at end of script.
function Release-Ref ($ref) { 
([System.Runtime.InteropServices.Marshal]::ReleaseComObject( 
[System.__ComObject]$ref) -gt 0) 
[System.GC]::Collect() 
[System.GC]::WaitForPendingFinalizers() 
                    }

#####################


#Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$a) | 
Out-Null
########

    #Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$b) | 
Out-Null
########

    #Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$c) | 
Out-Null
########

    #Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$d) | 
Out-Null
########

    #Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$e) | 
Out-Null
########

    #Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$outlook) | 
Out-Null
########

    #Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$message) | 
Out-Null
########

    #Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$smtp) | 
Out-Null
########

        #Release COM Object
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$webclient) | 
Out-Null
########


#Clear screen for the final time. (Let's keep things tidy.)
cls
###


#Exit powershell 
    exit
    ####

Hey, I know this script... So a quick look tells me your category filters are all buggered up. The $DNSLookupFailed = type lines that is, around line 167. Your ForEach at line 60 would be better done with a RegEx replacing once rather than looping 999 times. Basically mjolinor's answer from your other question. — TheMadTechnician
I remembered you helping me on this script earlier, just couldnt find a way of contacting you.. Hey there! :D I took a long and hard focus on the filters, but just cannot seem to get them straight... I am not that familiar with regex, got any suggestions? — Remy van Tour
So, should the IPs be in the unresolved category when they're IPs included in the Whitelist? — TheMadTechnician
The IP Address links, do you want that for the Source, or the DNS resolved IP? Do you want links for all categories? — TheMadTechnician

TheMadTechnician TheMadTechnician · Accepted Answer · 2014-08-20T18:20:25

Not going to try and put this in a comment. I'm not sure why you changed those lines from what we had done before:

$DNSWithKeyword = $DNSResults | ?{$_.HostName -match $Filter}
$DNSNoKeyword = $DNSResults | ?{!($_.HostName -match $Filter) -and !([string]::IsNullOrEmpty($_.HostName))}
$DNSLookupFailed = $DNSResults | ?{([string]::IsNullOrEmpty($_.HostName))}

As for not pasting when run as a service, I had you adding Out-Clipboard before, which you seem to have removed and now just pipe to Clip, and the account you are running the scheduled task as may have issues with the path to Clip. See if adding this back in and changing the |Clip references to |Out-Clipboard doesn't resolve that for you:

#If there is no Out-Clipboard, set it
If(!(Get-Command Out-Clipboard -ErrorAction SilentlyContinue)){Set-Alias Out-Clipboard "$env:WinDir\System32\clip.exe"}

Ok, let's try a slightly modified version of what you have then, and let me know how these turn out for you.

$DNSLookupFailed = $DNSResults | 
?{[string]::IsNullOrEmpty($_.HostName) -and !($_ -match $filter)}

$DNSWithKeyword = $DNSResults | 
?{$_ -match $Filter}

$DNSNoKeyword = $DNSResults | 
?{!($_ -match $Filter) -and !([string]::IsNullOrEmpty($_.HostName))}

Here's what I have that looks right (I think):

#Get current date
$Date = date -format yyyy-MM-dd
$Company = "Company1"
    $Company2 = "Company2"
    ########################


#Define all Paths.
$Path = "C:\inetpub\wwwroot\BlockedIP"
    md "$Path\HTML\$Date" -Force |Out-Null
    $path2 = "$Path\HTML\$Date"
$PathWeb = "/BlockedIp/HTML/$Date"
########################


#Define File's used or created in this script.
$File = "$Path\IP-$Date.txt"
    $FileHtml = "$Path2\IP-$Date.htm"
        $FileXML = "$Path\IP-$Date.xlsx"
            $FileHTMLWeb = "$PathWeb\IP-$date.htm"
            ######################################


#Define error actions.
$erroractionpreference = "SilentlyContinue"
###########################################

#Since the script used COM objects it will need the following 2 maps:

#(32Bit)
MD "C:\Windows\System32\config\systemprofile\Dektop" -force
    MD "C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Windows\Temporary Internet" -force
#(64Bit)
MD "C:\Windows\SysWOW64\config\systemprofile\Desktop" -force
    MD "C:\Windows\SysWOW64\config\systemprofile\AppData\Local\Microsoft\Windows\Temporary Internet" -force
#Once successfull the script will run without a problem if scheduled.

cls

start Outlook
Function Get-OutlookInBox 
    { 
    Add-type -assembly "Microsoft.Office.Interop.Outlook" | out-null 
    $olFolders = "Microsoft.Office.Interop.Outlook.olDefaultFolders" -as [type]  
    $outlook = new-object -comobject outlook.application 

        $namespace = $outlook.GetNameSpace("MAPI") 
        $folder = $namespace.getDefaultFolder($olFolders::olFolderInBox) 
            $folder.items |  
            Select -Property Subject, ReceivedTime, Importance, SenderName, body 
    } #end function Get-OutlookInbox
    ###################################################################################

    cls

try {
    $switches = get-outlookinbox | where subject -eq "Ip was blocked"
        $e = $switches.body

    $e -replace 'Hello:| Number|:\d{1,2}'
                    }
    catch {
          $switches = "Fail"
          }

    $f = $e |select -Unique |sort


    ni $File -type file
        $f | ac $File
            (gc $File) | ? {$_.trim() -ne "" } | sc $File
            $IPCount =  (gc $File)
            $IPCount =  $IPCount.count

    $index=0;  

    #Mark mails as read and delete.
    function display( [string]$subject, [string]$color , [string]$out)  {

    # REQUIRED LENGTH OF STRING
    $len = 20

    # STRINGS THAT ARE LONGER WILL BE CUT DOWN,
    # STRINGS THAT ARE TO SHORT WILL BE MADE LONGER
    if ( $subject.length -lt 20 ){
        $toadd=20-$subject.length;
        for ( $i=0; $i -lt $toadd; $i++ ){
            $subject=$subject+" ";
        }
        $len = $subject.length
    }
    else { $len = 20 }

    $index=$index+1
    Write -ForegroundColor $color -nonewline " |" ((($subject).ToString()).Substring(0,$len)).ToUpper()
}
$outlook = new-object -comobject outlook.application

#Define folders
$namespace = $outlook.GetNameSpace("MAPI")
$pst = $namespace.Stores
$pstRoot = $pst.GetRootFolder()
$pstFolders = $pstRoot.Folders
#$personal = $pstFolders.Items("ARCHIVE")  ##Not working, sadly.
$DefaultFolder = $namespace.GetDefaultFolder(6)
$InboxFolders = $DefaultFolder.Folders
$DeletedItems = $namespace.GetDefaultFolder(3)
$Emails = $DefaultFolder.Items

For($i=($emails.count-1);$i -ge 0;$i--){
    $($emails)[$i].Unread = $false
    $($emails)[$i].delete()
}




write "$IPCount unique IP addresses detected."

gps *Outlook* | Stop-Process -force



#Define error actions.
$erroractionpreference = "SilentlyContinue"


#Test Data
#$colComputers = @"
#199.27.128.103
#173.245.53.70
#173.245.53.137
#173.245.53.121
#173.245.53.104
#173.245.53.103
#173.245.51.69
#141.101.105.12
#141.101.105.121
#141.101.105.14
#141.101.105.15
#141.101.105.170
#108.162.254.116
#127.0.0.1
#64.39.103.176
#0.0.0.0
#111.111.311.25
#254.254.254.254
#187.159.165.1
#"@ -split "`n"

#Get content from given IP list.
$colComputers = @(gc $File | sort |Select -unique)
$SourceCount = $colComputers.Count
write "$SourceCount IP's detected."



#Get DNS Results
$Progress=1
$DNSResults = $colComputers | %{
Write-Progress -Activity "Creating a usable 'Blocked IP' list ($Progress/$sourcecount)" -PercentComplete ($Progress/$sourceCount*100) -Status "Please stand by"
try {
    ($dnsresult = [System.Net.DNS]::GetHostEntry($_))
}
catch {
    $dnsresult = "Fail"
}
[PSCustomObject][Ordered]@{
Source=$_.ToUpper()
HostName=$(if(!([string]::IsNullOrEmpty($dnsresult.HostName))){$dnsresult.HostName})
IPAddress=$(if(!([string]::IsNullOrEmpty($dnsresult.AddressList))){$dnsresult.AddressList[0].ToString()})


}
$Progress++
}
#CloudFlare = IP Range 108.162.254. + 141.101.104(105).

$Keywords = "192.","Google","Cloudflare","Cloud","Ping", `
"Easy-Voyage","McAfee","Pingdom","Panopta","Scoot","Uniglobe", `
"108.162.254.", "141.101.104.", "141.101.105."

$Filter = "($(($Keywords|%{[RegEx]::Escape($_)}) -join "|"))"

$DNSLookupFailed = $DNSResults | 
?{[string]::IsNullOrEmpty($_.HostName) -and !($_ -match $filter)}

$DNSWithKeyword = $DNSResults | 
?{$_ -match $Filter}

$DNSNoKeyword = $DNSResults | 
?{!($_ -match $Filter) -and !([string]::IsNullOrEmpty($_.HostName))}



#$count = ($DNSResults|?{$_ -match $filter}).count
$count = $SourceCount
#####################


#start Excel.
$a = New-Object -comobject Excel.Application

# set interactive to false so nothing from excel is shown.
$a.DisplayAlerts = $False
$a.ScreenUpdating = $True
$a.Visible = $True
$a.UserControl = $True
$a.Interactive = $True
###########################


#Create sheets in Excel.
$b = $a.Workbooks.Add()
    $c = $b.Worksheets.Item(1)
    $c.Activate() | Out-Null

#Create a Title for the first worksheet and adjust the font
$c.Cells.Item(1,1)= "Blocked IP's $Date"
    $c.Cells.Item(1,1).Font.ColorIndex = 55
    $c.Cells.Item(1,1).Font.Color = 8210719

$c.Cells.Item((3+$DNSWithKeyword.Count+1),1) = "IP's not in whitelist"
    $c.Cells.Item((3+$DNSWithKeyword.Count+1),1).Font.ColorIndex = 55
    $c.Cells.Item((3+$DNSWithKeyword.Count+1),1).Font.Color = 8210719

$c.Cells.Item((3+$DNSWithKeyword.Count+$DNSNoKeyword.Count+3),1)= "IP's without DNS return"
    $c.Cells.Item((3+$DNSWithKeyword.Count+$DNSNoKeyword.Count+3),1).Font.ColorIndex = 55
    $c.Cells.Item((3+$DNSWithKeyword.Count+$DNSNoKeyword.Count+3),1).Font.Color = 8210719
    #######################################


$range = $c.Range("a1","e1")
$range.Style = 'Title'
$range.Select()
$range.MergeCells = $true
$range.VerticalAlignment = -4108
################################


#Define row to be used for Company2.
$CounterRow = $Count+5
######################


#Define subjects.
$c.Name = "Blocked IP's ($Date)"
$c.Cells.Item(2,1) = "Given IP"
$c.Cells.Item(2,2) = "Resolved DNS"
$c.Cells.Item(2,3) = "Returned IP"
$c.Cells.Item(2,5) = "$Company"
$c.Cells.Item((4+$DNSWithKeyword.Count+$DNSNoKeyword.Count+$DNSLookupFailed.Count+4),1) = "Created by"
########################################


$link = "http://www.$Company"
    $link2 = "www.company2"

$r = $c.Range("E2") 
    [void]$c.Hyperlinks.Add($r, $link) 

$r = $c.Range("A$(4+$DNSWithKeyword.Count+$DNSNoKeyword.Count+$DNSLookupFailed.Count+4)") 
    [void]$c.Hyperlinks.Add($r, $link2)
    ###################################

#Define cell formatting from subjects.
$c.Range("A2:E2").Interior.ColorIndex = 6
$c.Range("A2:E2").font.size = 13
$c.Range("A2:E2").Font.ColorIndex = 1
$c.Range("A2:E2").Font.Bold = $True
###################################


#Define the usedrange, excluding header and footer rows
$KeyRange = $c.Range("A3:c$(3+$DNSWithKeyword.Count)")
$NoKeyRange = $c.Range("A$(3+$DNSWithKeyword.Count+2):c$(3+$DNSWithKeyword.Count+$DNSNoKeyword.Count+2)") 
$NoDNSRange = $c.Range("A$(3+$DNSWithKeyword.Count+$DNSNoKeyword.Count+4):c$(3+$DNSWithKeyword.Count+$DNSNoKeyword.Count+$DNSLookupFailed.Count+4)")
     $e = $c.Range("B3:B$(3+$DNSNoKeyword.Count+2)")
    ################################


#Set background color for the IP list.
$KeyRange.interior.colorindex = 4
$NoKeyRange.interior.colorindex = 15
$NoDNSRange.interior.colorindex = 8
####################################

#Populate data into spreadsheet
$DNSWithKeyword | Select Source, HostName, IPAddress | Sort HostName -Descending | 
ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | 
Select -Skip 1 | Clip
    $c.Paste($KeyRange,$false)

$DNSNoKeyword | Sort HostName -Descending | 
ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | 
Select -Skip 1 | Clip
    $c.Paste($NoKeyRange,$false)

$DNSLookupFailed | sort Source -Descending|
ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | 
Select -Skip 1 | Clip
    $c.Paste($NoDNSRange,$false)
    ############################



ForEach($Cell in $NoKeyRange){
If([String]::IsNullOrWhitespace($Cell.value2)){
$ip = ""
$link3 = "http://who.is/whois/$IP"
$Cell.Item($_) = "Please invesigate"
[void]$cell.Hyperlinks.Add($Cell,$link3)
    }
}

###########################################################################


#Define borders here.
$xlOpenXMLWorkbook = 51
$xlAutomatic=-4105
$xlBottom = -4107
$xlCenter = -4108
$xlRight = -4152
$xlContext = -5002
$xlContinuous=1
$xlDiagonalDown=5
$xlDiagonalUp=6
$xlEdgeBottom=9
$xlEdgeLeft=7
$xlEdgeRight=10
$xlEdgeTop=8
$xlInsideHorizontal=12
$xlInsideVertical=11
$xlNone=-4142
$xlThin=2 
#########    

$selection = $c.range("A3:C$($DNSResults.Count+6)")
    $selection.select() |out-null
    $selection.HorizontalAlignment = $xlRight
    $selection.VerticalAlignment = $xlBottom
    $selection.WrapText = $false
    $selection.Orientation = 0
    $selection.AddIndent = $false
    $selection.IndentLevel = 0
    $selection.ShrinkToFit = $false
    $selection.ReadingOrder = $xlContext
    $selection.MergeCells = $false
    $selection.Borders.Item($xlInsideHorizontal).Weight = $xlThin
    #############################################################


#Define the usedrange for autofitting.
$d = $c.UsedRange
#################

#Make everything fit in it's cell.
$d.EntireColumn.AutoFit() | Out-Null
####################################



#Define html code for Excel save to .htm.
$xlExcelHTML = 44
#################

#Save final result as an .xlsx file.
$b.SaveAs("$FileXML")
#####################

#Save final result as a .htm file
$b.SaveAs("$FileHTML",$xlExcelHTML)
###################################

#Close and quit Excel.
$b.Close()
##########

#Make sure excel and outlook is fully closed.
gps *Excel* | Stop-Process -force
#########################################


#Clear screen.
cls
###


#Move .txt file to the correct HTML folder.
mi $file $path2 -Force
#############################

#Move .xlsx file to the correct HTML folder.
mi $filexml $path2 -Force
################################


#Declare XLSX file for mail.
$MailXML = "$path2\IP-$Date.xlsx"
#################################


#Clear screen, again. (Let's keep things tidy.)
cls
###


#Variables for public IP
$url = "http://checkip.dyndns.com" 
$webclient = New-Object System.Net.WebClient
$IpPublic = $webclient.DownloadString($url)
$IpPublic2 = $IpPublic.ToString()
$ipPublic3 = $IpPublic2.Split(" ")
$ipPublic4 = $ipPublic3[5]
$ipPublic5 = $ipPublic4.replace("</body>","")
$FinalIPAddress = $ipPublic5.replace("</html>","")
$ipLocal = (Get-WmiObject -class win32_NetworkAdapterConfiguration `
-Filter 'ipenabled = "true"').ipaddress[0]
##########################################

#The href should point to the htm file in the iis/apache folder.
$WebLink = $FinalIPAddress+$FileHtmlWeb
    $here = "<a href='http://$Weblink'><b>Here</b></a>"
#######################################################


#Define From, To, CC and subject.
$From = "Blocked IP <Name1@$Company>"
$To = "IT Dept <Name2@$Company>"
$CC = "Name 3 <Name3@$Company>"
$Subject = "Blocked IPs for $date ($Count Total)"
#################################################


<#
Define the body of the e-mail, in this case
it displays a message and shows the 
server it is send from with it's local IP.

A link to the .htm file, how many IP's were blocked 
and the date of the message.
#>
$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>" 
    $body += "<body>Dear <font color=black>$to</font>,<br><br>"
    $body += "This is an automated message generated by server: <font color=red><b>$env:COMPUTERNAME, $IPLocal.</b></font><br><br>"
    $body += "Click <font color=red><b>$here</b></font> to see the Blocked IP report for $date containing $count IP's.<br>"
    $body += "Or see the attachment to open it in Excel.<br></body></html>"
###########################################################################


#Clear screen, again. (Let's keep things tidy.)
cls
###

#Define SMTP server.
$SMTPServer = "smtp.gmail.com"
$SMTPPort = "587"
################

#Define credentiala mail sender.
$Username = "[email protected]"
$Password = "Password"
######################

#Define mail.
$message = New-Object System.Net.Mail.MailMessage
$message.IsBodyHTML = $true
$message.ReplyTo = $From
$message.Sender = $From
$message.subject = $subject
$message.body = $body
$message.to.add($to)
$message.cc.add($cc)
$message.from = $From
$message.attachments.add($MailXML)
    $smtp = New-Object System.Net.Mail.SmtpClient($SMTPServer, $SMTPPort);
        $smtp.EnableSSL = $true
        $smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password);
        $smtp.send($message)
        ####################


#Create a function to relase Com object at end of script.
function Release-Ref ($ref) { 
([System.Runtime.InteropServices.Marshal]::ReleaseComObject( 
[System.__ComObject]$ref) -gt 0) 
[System.GC]::Collect() 
[System.GC]::WaitForPendingFinalizers() 
                    }

#####################


#Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$a) | 
Out-Null
########

    #Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$b) | 
Out-Null
########

    #Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$c) | 
Out-Null
########

    #Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$d) | 
Out-Null
########

    #Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$e) | 
Out-Null
########

    #Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$outlook) | 
Out-Null
########

    #Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$message) | 
Out-Null
########

    #Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$smtp) | 
Out-Null
########

        #Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$webclient) | 
Out-Null
########


#Clear screen for the final time. (Let's keep things tidy.)
cls
###


#Exit powershell 
    exit
    ####

Category's: I have a few unresolved DNS adresses in my filter that I know are good. Is it possible to give it a hostname myself so it shows on the excel sheet? e.g 141.101.105.12 should have hostname CloudFlare Personally I know it's Cloudflare, but the rest of the people do not. Can do! Let's add a SetKnown function. I think this will serve us better.

Function Set-KnownIPs{
    Param([Object]$DNSLookupObject)
    Switch($DNSLookupObject)
        {$_.Source -Match "(108.162.154|141.101.(?:104|105))"}{$_.HostName = "CloudFlare"}
        {$_.Source -Match "(64.18.[0-18])"}{$_.HostName = "Google"}
    }
    $DNSLookupObject
}

Now, even as we make our PSCustomObject we can run it through that function:

Set-KnownIPs -DNSLookupObject ([PSCustomObject][Ordered]@{
Source=$_.ToUpper()
HostName=$(if(!([string]::IsNullOrEmpty($dnsresult.HostName))){$dnsresult.HostName})
IPAddress=$(if(!([string]::IsNullOrEmpty($dnsresult.AddressList))){$dnsresult.AddressList[0].ToString()})
})

You put the function at the top of the script (or at the very least above where you call it, I prefer to put all functions at the top so I know they're loaded before they're needed and I know where to find them just in case). Here's all it's really doing: It takes the entire object in as it's parameter, passes it through the Switch command which lets you set various cases for it, and if it matches a case it responds appropriately (by updating the object's HostName in our case), and after the switch it passes the updated object back out as a result.

Adding links to the IP addresses. This will linkify all the IP's on the spreadsheet to who.is:

$c.usedrange | Where{$_.Value2 -match "(\b(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b)"} | ForEach{$IPLink = "http://who.is/$($Matches[1])";[void]$c.Hyperlinks.Add($_, $IPLink)}

Powershell converting IP's to resolved DNS - Export to Excel

1 Answers