Friday, November 24, 2006

Reporting on the age of content in mailbox folders and across an Exchange Server via a script

Like many of us the age of the content in mailboxes is constantly aging the usefulness of aged content on expensive storage is an always a elemental question for those people who are grasping at trying to manage mail-store usage. If your still running Exchange 2000 then you maybe battling with a very finite amount of storage asking your service provider to please make it last another six months (why me!!). So if this is the case you need to arm your users (and yourself) with maybe a little more information about where the storage in your mailboxes is being used and where the growth curves have happened over the years they have been commanding the storage in their mailbox.

So enter this script, what this script does is scans every single item in a mailbox and looks at the creation date of this item. It then aggregates the size of the item and the item count into one of 3 categories 0-1 year old , 1-2 years old and over 2 years old. The script then produces a little html report that shows for each folder what this size of and total number of items are in this folder over these three time periods.

The time it takes to scan every item in a mailbox means that if you have large mailboxes this script will take a very long time to run. On a server with a large number of mailboxes it probably not practical. But it can be handy to get a farily detail report on a single mailbox and it can be good on a small mail server with not too many mailboxes if you have time to run it. The script itself uses WebDAV to search firstly the folder hierarchy and then search each individual folder. I’ve come up with two versions of the script the first Is a version that uses Form Based Authentication and just scans one configured mailbox. The other version takes the servername of a sever you want to run it against as a commandline parameter and then querys for all mailboxes that are visible in the GAL on this server. It connects to and scans every mailbox using the Admin virtual root which means the script can run with delegated Exchange admin rights.

The FBA version of the script creates a htm report for the user it’s configured to run against in the c:\temp directory of the server. The server version creates a separate report for each user it runs against and creates one report that shows the totals for every mailbox across each time period.

To use the FBA version you need to configure the authentication details to use for the synthetic form logon and the mailbox and server you want to connect to in the following line

snServername = "servername.com"
mnMailboxname = "mailboxname"
username = "username"
domain = "domain"
strpassword = "password"


To run the ageautserver.vbs script it takes one command line parameter which is the name of the server you want to run it against (eg cscript ageautserver.vbs servername). By default the script isn’t using SSL which may mean you need to adjust the following line if you are using SSL on the ExAdmin Directory

Eg change

falias = "http://" & servername & "/exadmin/admin/" & dpDefaultpolicy & "/mbx/"

to

falias = "https://" & servername & "/exadmin/admin/" & dpDefaultpolicy & "/mbx/"

The script is pretty verbose which could be cut down because it was such a long running script I kind of preferred that it at least told me what it was doing while it was taking so long.

I’ve put a downloadable version of the code here the server version looks like

on error resume next
Servername = wscript.arguments(0)
treport = "<table border=""1"" width=""100%"">" & vbcrlf
treport = treport & " <tr>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Mailbox
Name</font></b></td>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"" colspan=""2""><b><font
color=""#FFFFFF"">Over 2 Years Old</font></b></td>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"" colspan=""2""><b><font
color=""#FFFFFF"">1-2 Years Old</font></b></td>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"" colspan=""2""><b><font
color=""#FFFFFF"">0-1 Years Old</font></b></td>" & vbcrlf
treport = treport & "</tr>" & vbcrlf
treport = treport & " <tr>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">&nbsp;</font></b></td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">#Messages</font></b></td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Size(MB)</font></b></td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">#Messages</font></b></td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Size(MB)</font></b></td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">#Messages</font></b></td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Size(MB)</font></b></td>"
& vbcrlf
treport = treport & "</tr>" & vbcrlf
set req = createobject("microsoft.xmlhttp")
set com = createobject("ADODB.Command")
set conn = createobject("ADODB.Connection")
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strNameingContext = iAdRootDSE.Get("configurationNamingContext")
strDefaultNamingContext = iAdRootDSE.Get("defaultNamingContext")
Conn.Provider = "ADsDSOObject"
Conn.Open "ADs Provider"
polQuery = "<LDAP://" & strNameingContext & ">;(&(objectCategory=msExchRecipientPolicy)(cn=Default
Policy));distinguishedName,gatewayProxy;subtree"
svcQuery = "<LDAP://" & strNameingContext & ">;(&(objectCategory=msExchExchangeServer)(cn="
& Servername & "));cn,name,legacyExchangeDN;subtree"
Com.ActiveConnection = Conn
Com.CommandText = polQuery
Set plRs = Com.Execute
while not plRs.eof
for each adrobj in plrs.fields("gatewayProxy").value
if instr(adrobj,"SMTP:") then dpDefaultpolicy =
right(adrobj,(len(adrobj)-instr(adrobj,"@")))
next
plrs.movenext
wend
wscript.echo dpDefaultpolicy
Com.CommandText = svcQuery
Set Rs = Com.Execute
while not rs.eof
GALQueryFilter = "(&(&(&(& (mailnickname=*)(!msExchHideFromAddressLists=TRUE)(|
(&(objectCategory=person)(objectClass=user)(msExchHomeServerName=" &
rs.fields("legacyExchangeDN") & ")) )))))"
strQuery = "<LDAP://" & strDefaultNamingContext & ">;" & GALQueryFilter &
";displayname,mail,distinguishedName,mailnickname,proxyaddresses;subtree"
com.Properties("Page Size") = 100
Com.CommandText = strQuery
Set Rs1 = Com.Execute
while not Rs1.eof
report = "<table border=""1"" width=""100%"">" & vbcrlf
report = report & " <tr>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font
color=""#FFFFFF"">Folder Name</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""
colspan=""2""><b><font color=""#FFFFFF"">Over 2 Years Old</font></b></td>" &
vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""
colspan=""2""><b><font color=""#FFFFFF"">1-2 Years Old</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""
colspan=""2""><b><font color=""#FFFFFF"">0-1 Years Old</font></b></td>" & vbcrlf
report = report & "</tr>" & vbcrlf
report = report & " <tr>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font
color=""#FFFFFF"">&nbsp;</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font
color=""#FFFFFF"">#Messages</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font
color=""#FFFFFF"">Size(MB)</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font
color=""#FFFFFF"">#Messages</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font
color=""#FFFFFF"">Size(MB)</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font
color=""#FFFFFF"">#Messages</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font
color=""#FFFFFF"">Size(MB)</font></b></td>" & vbcrlf
report = report & "</tr>" & vbcrlf
falias = "http://" & servername & "/exadmin/admin/" & dpDefaultpolicy & "/mbx/"
for each paddress in rs1.fields("proxyaddresses").value
if instr(paddress,"SMTP:") then falias = falias & replace(paddress,"SMTP:","")

next
ReDim tresarray(1,6)
wscript.echo falias
call RecurseFolder(falias)
report = report & "</table>" & vbcrlf
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("c:\temp\" & rs1.fields("mail").value &
".htm",2,true)
wfile.write report
wfile.close
set wfile = nothing
treport = treport & "<tr>" & vbcrlf
treport = treport & "<td align=""center"">" & rs1.fields("mail").value &
"&nbsp;</td>" & vbcrlf
treport = treport & "<td align=""center"">" & tresarray(0,1) & "&nbsp;</td>" &
vbcrlf
treport = treport & "<td align=""center"">" &
FormatNumber(tresarray(1,1)/1024/1024,2) & "&nbsp;</td>" & vbcrlf
treport = treport & "<td align=""center"">" & tresarray(0,2) & "&nbsp;</td>" &
vbcrlf
treport = treport & "<td align=""center"">" &
FormatNumber(tresarray(1,2)/1024/1024,2) & "&nbsp;</td>" & vbcrlf
treport = treport & "<td align=""center"">" & tresarray(0,3) & "&nbsp;</td>" &
vbcrlf
treport = treport & "<td align=""center"">" &
FormatNumber(tresarray(1,3)/1024/1024,2) & "&nbsp;</td>" & vbcrlf
treport = treport & "</tr>" & vbcrlf
rs1.movenext
wend
rs.movenext
wend
rs.close
set conn = nothing
set com = nothing
treport = treport & "</table>" & vbcrlf
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("c:\temp\mailboxage.htm",2,true)
wfile.write treport
wfile.close
set wfile = nothing
set fso = nothing

Public Sub RecurseFolder(sUrl)

req.open "SEARCH", sUrl, False, "", ""
sQuery = "<?xml version=""1.0""?>"
sQuery = sQuery & "<g:searchrequest xmlns:g=""DAV:"">"
sQuery = sQuery & "<g:sql>SELECT ""http://schemas.microsoft.com/"
sQuery = sQuery & "mapi/proptag/x0e080003"", ""DAV:hassubs"" FROM SCOPE "
sQuery = sQuery & "('SHALLOW TRAVERSAL OF """ & sUrl & """') "
sQuery = sQuery & "WHERE ""DAV:isfolder"" = true and ""DAV:ishidden"" = false
and ""http://schemas.microsoft.com/mapi/proptag/x36010003"" = 1"
sQuery = sQuery & "</g:sql>"
sQuery = sQuery & "</g:searchrequest>"
req.setRequestHeader "Content-Type", "text/xml"
req.setRequestHeader "Translate", "f"
req.setRequestHeader "Depth", "0"
req.setRequestHeader "Content-Length", "" & Len(sQuery)
req.send sQuery
Set oXMLDoc = req.responseXML
Set oXMLSizeNodes = oXMLDoc.getElementsByTagName("d:x0e080003")
Set oXMLHREFNodes = oXMLDoc.getElementsByTagName("a:href")
Set oXMLHasSubsNodes = oXMLDoc.getElementsByTagName("a:hassubs")
For i = 0 to oXMLSizeNodes.length - 1
call procfolder(oXMLHREFNodes.Item(i).nodeTypedValue,sUrl)
wscript.echo oXMLHREFNodes.Item(i).nodeTypedValue
If oXMLHasSubsNodes.Item(i).nodeTypedValue = True Then
call RecurseFolder(oXMLHREFNodes.Item(i).nodeTypedValue)
End If
Next
End Sub

sub procfolder(strURL,pfname)
wscript.echo strURL
ReDim resarray(1,6)
strQuery = "<?xml version=""1.0""?><D:searchrequest xmlns:D = ""DAV:""
xmlns:b=""urn:uuid:c2f41010-65b3-11d1-a29f-00aa00c14882/"">"
strQuery = strQuery & "<D:sql>SELECT ""DAV:displayname"",
""urn:schemas:httpmail:subject"", "
strQuery = strQuery & """DAV:creationdate"", ""DAV:getcontentlength"", "
strQuery = strQuery & """urn:schemas:httpmail:fromemail"""
strQuery = strQuery & " FROM scope('shallow traversal of """
strQuery = strQuery & strURL & """') Where ""DAV:ishidden"" = False AND
""DAV:isfolder"" = False</D:sql></D:searchrequest>"
req.open "SEARCH", strURL, false
req.setrequestheader "Content-Type", "text/xml"
req.setRequestHeader "Translate","f"
req.send strQuery
If req.status >= 500 Then
ElseIf req.status = 207 Then
set oResponseDoc = req.responseXML
set oNodeList = oResponseDoc.getElementsByTagName("a:displayname")
set oNodeList1 = oResponseDoc.getElementsByTagName("a:href")
set oSize = oResponseDoc.getElementsByTagName("a:getcontentlength")
set odatereceived = oResponseDoc.getElementsByTagName("a:creationdate")
For i = 0 To (oNodeList.length -1)
set oNode = oNodeList.nextNode
set oNode1 = oNodeList1.nextNode
set oNode2 = oSize.nextNode
set oNode3 = odatereceived.nextNode
wscript.echo oNode3.text
If CDate(DateSerial(mid(oNode3.text,1,4),
mid(oNode3.text,6,2),mid(oNode3.text,9,2))) < dateadd("m",-24,now()) Then
resarray(0,1) = resarray(0,1) + 1
resarray(1,1) = resarray(1,1) + Int(oNode2.text)
End if
If CDate(DateSerial(mid(oNode3.text,1,4),
mid(oNode3.text,6,2),mid(oNode3.text,9,2))) > dateadd("m",-24,now()) And
CDate(DateSerial(mid(oNode3.text,1,4),
mid(oNode3.text,6,2),mid(oNode3.text,9,2))) < dateadd("m",-12,now()) Then
resarray(0,2) = resarray(0,2) + 1
resarray(1,2) = resarray(1,2) + Int(oNode2.text)
End if
If CDate(DateSerial(mid(oNode3.text,1,4),
mid(oNode3.text,6,2),mid(oNode3.text,9,2))) > dateadd("m",-12,now()) And
CDate(DateSerial(mid(oNode3.text,1,4),
mid(oNode3.text,6,2),mid(oNode3.text,9,2))) < now() Then
resarray(0,3) = resarray(0,3) + 1
resarray(1,3) = resarray(1,3) + Int(oNode2.text)
End if
Next
Else
End If
tresarray(0,1) = tresarray(0,1) + resarray(0,1)
tresarray(1,1) = tresarray(1,1) + resarray(1,1)
tresarray(0,2) = tresarray(0,2) + resarray(0,2)
tresarray(1,2) = tresarray(1,2) + resarray(1,2)
tresarray(0,3) = tresarray(0,3) + resarray(0,3)
tresarray(1,3) = tresarray(1,3) + resarray(1,3)
report = report & "<tr>" & vbcrlf
report = report & "<td align=""center"">" & unescape(Replace(strURL,falias,""))
& "&nbsp;</td>" & vbcrlf
report = report & "<td align=""center"">" & resarray(0,1) & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" &
FormatNumber(resarray(1,1)/1024/1024,2) & "&nbsp;</td>" & vbcrlf
report = report & "<td align=""center"">" & resarray(0,2) & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" &
FormatNumber(resarray(1,2)/1024/1024,2) & "&nbsp;</td>" & vbcrlf
report = report & "<td align=""center"">" & resarray(0,3) & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" &
FormatNumber(resarray(1,3)/1024/1024,2) & "&nbsp;</td>" & vbcrlf
report = report & "</tr>" & vbcrlf
end sub
 

Friday, November 10, 2006

Creating a Server Side rule to move suspect messages with inline gifs to another folder using Rule.dll

Recently there has been a large increase in the amount of image based spam being sent out and also an increase in the amount of SPAM making it though SPAM filters because of the methods being employed such as randomizing images and also modifying images so they are difficult for any OCR based spam filter to decode the text. Stopping this at Edge device is the ultimate goal of any decent Sys Admin but this continuing war between spammers and those that create the software that can fight spam (and the accountants that stop us buying said software) means that we have to deal with the stuff that makes it though any defenses we might have and the inevitable complaints from the end users that stem from this. I decided to see if I could make a rule that at least could move any of these image based emails into a separate folder mainly for my postmaster account which tends to get hammered.

Analyzing the basic image based spam message it consists of one inline gif image and a bunch of text. So to write a server rule with rule dll I needed to come up with a rule that would act on two basic logic constraints. Using the PR_TRANSPORT_MESSAGE_HEADERS property which stores the Entire header of message which includes information about the bodyparts of a message eg if it’s a html body part or a text bodypart or an attachment etc. So the rule looks at the message header and the first logic constraint looks to see if this message has any body parts that has a content type of image/gif then next logic condition which is a Bitwise NOT looks to see if this message has any body parts with have a content disposition set to attachment; which means they will actually be an attached file vs inline attachments. The two logic constraints get tied together via a Bitwise AND logic condition (eg the first condition is positive if there is a bodypart of image/gif and the second is positive is there are no attached files). The end result is a rule that will fire on any message that are received with one inline gif image.

BUT!!!! Before you go tearing off and wanting to look at using this on a users mailbox you should first consider this rule may generate a lot of false positives. For instance if someone has an inline gif in there signature this would cause the rule to fire. Some HTML based newsletters will also cause this to fire. For a postmaster account it seems to work pretty well it also seems to even be able to move image base spam messages that are located attached to NDR notification messages because the headers from the bounced message are still included in the NDR’s PR_TRANSPORT_MESSAGE_HEADERS prop.

The Code is setup to move these images into the Junk Email Folder you may however want to look at creating another folder like Potential Image Spam to hold messages that are moved by the rule.

This code requires the Rule.dll to be registered on the machine you’re running the script on if you have never used rule.dll you should read up about the restrictions around rules created this way.

I’ve put a download of this code here the code itself looks like.

const SUBSTRING = 1 ' Substring
const IGNORECASE = &H00010000 ' Ignore case
Const ACTION_MOVE = 1
const B_NEZ = 2
const L_OR = 3
Const REL_EQ = 7
const MSG_ATTACH = 2
Const PR_Transport_Headers = &H007D001E

servername = "servername"
mailboxname = "user"

Set objSession = CreateObject("MAPI.Session")

objSession.Logon "","",false,true,true,true,servername & vbLF & mailboxname
Set objRules = CreateObject("MSExchange.Rules")
objRules.Folder = objSession.Inbox
Set objInbox = objSession.Inbox

Set CdoInfoStore = objSession.GetInfoStore
Set CdoFolderRoot = CdoInfoStore.RootFolder
Set CdoFolders = CdoFolderRoot.Folders

bFound = False
Set CdoFolder = CdoFolders.GetFirst
Do While (Not bFound) And Not (CdoFolder Is Nothing)
If CdoFolder.Name = "Junk E-mail" Then
bFound = True
Else
Set CdoFolder = CdoFolders.GetNext
End If
Loop
Set ActionFolder = CdoFolder


Set importPropVal = CreateObject("MSExchange.PropertyValue")
importPropVal.Tag = PR_Transport_Headers
importPropVal.Value = " attachment;"

Set importPropCond = CreateObject("MSExchange.ContentCondition")
importPropCond.PropertyType = PR_Transport_Headers
importPropCond.Operator = SUBSTRING + IGNORECASE
importPropCond.Value = importPropVal


Set importPropVal1 = CreateObject("MSExchange.PropertyValue")
importPropVal1.Tag = PR_Transport_Headers
importPropVal1.Value = "image/gif"

Set importPropCond1 = CreateObject("MSExchange.ContentCondition")
importPropCond1.PropertyType = PR_Transport_Headers
importPropCond1.Operator = SUBSTRING + IGNORECASE
importPropCond1.Value = importPropVal1


Set logPropCond1 = CreateObject("MSExchange.LogicalCondition")
logPropCond1.Operator = 3
logPropCond1.Add importPropCond

Set logPropCond = CreateObject("MSExchange.LogicalCondition")
logPropCond.Operator = 1
logPropCond.Add importPropCond1
logPropCond.Add logPropCond1


' Create action
Set objAction = CreateObject("MSExchange.Action")
objAction.ActionType = ACTION_MOVE
objAction.Arg = ActionFolder

' Create new rule
Set objRule = CreateObject("MSExchange.Rule")
objRule.Name = "Gif Image Move Rule"

' Add action and assign condition
objRule.Actions.Add , objAction
objRule.Condition = logPropCond

' Add rule and update
objRules.Add , objRule
objRules.Update

' Log off and cleanup
objSession.Logoff

Set objRules = Nothing
Set objSession = Nothing
Set importProp = Nothing
Set importPropVal = Nothing
Set objAction = Nothing
Set objRule = Nothing

Thursday, November 02, 2006

Geolocationing Exchange Message Tracking with Powershell (Exchange 2000/2003) (Seeing what countries your messages/Spam are coming from)

Geolocation as wikipedia aptly puts is the real-world geographic location of a computer based on its IP address. Many people use this for web site statistics for displaying where users are coming from. This information can also be very useful to actually tell you where your mail (and SPAM) is coming from. There are a lot of people out there selling and open sourcing geoip services, for instance awstats uses one of the open source variants to get the country information it uses in its report. Following some of these trails I found references to the IpToCountry.csv file which is a list of IP ranges per country that a few people maintain and provide (in slightly differing formats) for free as a download. The most up to date version in the one provided by Webnet77 which is the one I choose to use and is what this script is based on. Using this CSV file in its native format while possible with a little manipulation using the Microsoft text driver I found was very slow when trying to resolve a large number of entries from the message tracking. So for this script I decided to go with creating a mdb database using the ADO and ADO.NET and importing the csv file into to the database.

The script itself is a cut down version of my BYO message tracker the main section of script remains the same. It produces a .NET form that allows you to enter a server-name and a time range for the message tracking logs you want to query. (There was a lot of more functionality in the BYO message tracker that I didn’t port over into this script).It still uses WMI behind the scenes to query the Exchange Message tracking logs. To manage the Geolocation process a number of functions and routines have been added. This script itself is self maintaining meaning that as long as you’re using it on a machine that has ADO on it the script will create the database it’s going to use itself using ADOX. And it also includes a maintenance routine that checks the import file to see if its been updated based on the file modified time of the iptocountry.csv file. If you have downloaded a new csv file when the script is run if will detect the new version and delete the old database and create a new one based on the new file. To insert the records into the database itself the System.Data.ole class is used as well as some powershell cmdlets to get the content of the IptoCountry file one line at a time and then parse it into the database. After the records are inserted into the database because this process tends to make the file size bloat a bit I used the ADO JRO objects to compress the mdb file back to smallest possible size which helps on the performance side of thing as well. To help out further with query performances two indexes are added to both the IPFrom and IPTo columns in the database.

Once the script had created and populated the databases you’ll have a database of numerical ranges which are representations of the IP Blocks that have been allocated to particular countries/IP’s . To find out where an IP address belongs to it’s a matter of first converting it into its numeric representation and then doing a SQL query to find the row in the database that this numeric representation falls within. Because you’ll find mail servers in your message tracking logs tend to repeat a lot to save querying the database for every occurrence of the same server I used a hash table the tracks the C class of a resolved IP and if the script detects it querying the same c class it will use the hashtable value instead of making a another database call. This speeds things up considerably when you making future queries because the database doesn’t need to be queried.

The WMI side of this script does differ from the BYO message tracker in that the message ID that is uses is different. Because the messageID I was using in the other script doesn’t have access to the sending servers IP address I had to use another Message Tracking ID. The one that I found worked the best was 1019 which is the first event ID in the tracking log see this kb. To cater for the possibility of multiple instances of a 1019 events for the same message a hash-table is used to prevent the same message ID being counted twice. The Email Type box allows you to select between what type of email this script will report on. Normal mail is as it indicates normal messages that are sent and received via the server. The Spam email type is messages that the IMF has taken gateway action on (either delete or achieve). When you select the Spam email type the type of tracking ID the WMI side of the script queries for changes from 1019 to 1040.

The extras side of the script allows the aggregation of the query results by country so you can see by country how much email is being sent and received. The side by side aggregation creates the same aggregation but it changes the WMI query to include both the 1019 and 1040 entry types and then creates a side by side aggregation so you can see by country how much normal email is sent vs. how much IMF detected Spam per country (I think this is very cool).

To use the script you need to first download the Iptocountry.csv file from Webnet77 . At the moment the script is set to read the file from the c:\temp directory and also create the database in the c:\temp directory this is controlled by the following lines.

$dbfilepath = "c:\temp\iptocountry.mdb"
$tmpdbfilepath = "c:\temp\iptocountrycomp.mdb"
$importfilepath = "c:\temp\iptocountry.csv"

The $tmpdbfilepath is used only during the compression section of the script.

I’ve put a downloadable copy of the script here. This script itself is a bit long to post verbatim heres what some of the mdb creation and maintenance routines.


function createdb{
$aoADOXDb = new-object -com ADOX.Catalog
$aoADOXDb.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + $dbfilepath)
$atADOXTable = new-object -com ADOX.Table
$atADOXTable.Name = "IPTOCOUNTRY"
$atADOXTable.Columns.Append("IPFrom", $adDouble)
$atADOXTable.Columns.Append("IPTo", $adDouble)
$atADOXTable.Columns.Append("Registry", $adVarWChar, 25)
$atADOXTable.Columns.Append("ASSIGNED", $adDouble)
$atADOXTable.Columns.Append("CTRY", $adVarWChar, 2)
$atADOXTable.Columns.Append("CNTRY", $adVarWChar, 3)
$atADOXTable.Columns.Append("COUNTRY", $adVarWChar, 100)
$atindex = new-object -com ADOX.index
$atindex.Name = "idxIPFrom"
$atindex.Columns.Append("IPFrom")
$atindex.Unique = $True
$atADOXTable.Indexes.Append($atindex)

$atindex1 = new-object -com ADOX.index
$atindex1.Name = "idxIPTo"
$atindex1.Columns.Append("IPTo")
$atindex1.Unique = $True
$atADOXTable.Indexes.Append($atindex1)

$aoADOXDb.Tables.Append($atADOXTable)
$atADOXTablever = new-object -com ADOX.Table
$atADOXTablever.Name = "Version"
$atADOXTablever.Columns.Append("FileModifiedDate", $adVarWChar, 255)
$aoADOXDb.Tables.Append($atADOXTablever)
# Cleanup
$aoADOXDb.ActiveConnection.close()

[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$atindex)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$atindex1)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$atADOXTable)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$atADOXTablever)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$aoADOXDb)
[system.gc]::Collect()
$atindex = $null
$atindex1 = $null
$atADOXTable = $null
$atADOXTablever = $null
$aoADOXDb = $null

}

function populatedb{

$file = get-item $importfilepath
$ocOdbcConnection.Open()
$dcOdBcommand = new-object System.Data.OleDb.OleDbCommand
$dcOdBcommand.connection = $ocOdbcConnection
$dcOdBcommand.commandtext = "Insert into Version values('" + $file.lastwritetime + "')"
$dcOdBcommand.ExecuteNonQuery()
$rcRowCount = 0
"Filling Database this may take a few minutes"
get-content $importfilepath | %{
$linarr = $_.replace("'","``").split(",")
if ($linarr[0].indexofany("#") -band $linarr.length -gt 1){
$stSQLStatement = "Insert into IPTOCOUNTRY values('" + $linarr[0].replace("`"","") + "','" `
+ $linarr[1].replace("`"","") + "','"+ $linarr[2].replace("`"","") + "','"+ `
$linarr[3].replace("`"","") + "','" + $linarr[4].replace("`"","") + "','" + `
$linarr[5].replace("`"","") + "','" + $linarr[6].replace("`"","") + "')"
$dcOdBcommand.commandtext = $stSQLStatement
$inResult = $dcOdBcommand.ExecuteNonQuery()
if ($inResult -ne 1){$inResult}
}
$rcRowCount = $rcRowCount + 1
if ($rcRowCount -eq 10000){
$rcRowCount = 0
"10000 Rows Inserted"
}
}
"Fill completed"
$dcOdBcommand = $null
$ocOdbcConnection.Close()
$ocOdbcConnection = $null
$jrJroobj = new-object -com JRO.JetEngine
$dbSourceDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + $dbfilepath
$dbDestinationDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + $tmpdbfilepath
$jrJroobj.CompactDatabase($dbSourceDB, $dbDestinationDB)
remove-item $dbfilepath
copy-item $tmpdbfilepath $dbfilepath
remove-item $tmpdbfilepath
}

function checkfileversion{
$ocOdbcConnection.Open()
$dcOdBcommand = new-object System.Data.OleDb.OleDbCommand
$dcOdBcommand.connection = $ocOdbcConnection
$dcOdBcommand.commandtext = "select FileModifiedDate from Version"
$drDBreader = $dcOdBcommand.ExecuteReader()
$retval = 0
while ($drDBreader.read()){
if ($drDBreader[0] -ne $file.lastwritetime){$retval = 1}
else{"DB up to date"}
}
$dcOdBcommand = $null
$ocOdbcConnection.Close()
return $retval
}