3
\$\begingroup\$

I wrote my first powershell script. It reads through all the emails in a folder and exports some of the data as a csv file. I'm happy with the way it works but I plan to share it with co-workers, so it should be more portable. I guess making the outfile directory relative instead of specific to my workstation is one improvement that is needed. It is also probably quite brittle in that it won't adapt well to changes in the format of the email body.

function isSecurityAlert($subject){
    if($subject.contains("Approaching Subscription Expiration")){
        return $false
    }

    if($subject.contains("Database Download Failure")){
        return $false
    }

    if($subject.contains("Database Load Failure")){
        return $false
    }

    return $true
}



function getFirstName($messageBody){
   $re = "DC=babc/([A-Z a-z]*)\\, ([A-Z a-z]*)"
    if($messageBody -match $re){  
         return $matches[2]
    } 

}

function getLastName($messageBody){

    if($messageBody -match "DC=dabc/([A-Z a-z]*)"){  
         return $matches[1]
    } 
}

function getSourceIP($messageBody){
    $re = "User IP address: (\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})"
    if($messageBody -match $re){
         return $matches[1]
    } else {
     $re2 = "IP address: (\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})"
     $k = $messageBody -replace "\r\n" 
     if($k -match $re2){
         return $matches[1]
      }
    }
}

function getDestIP($messageBody){
    ## Two possible formats  "Destination IP address"  and "IP address"

    $re = "Destination IP address: (\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})"
    if($messageBody -match $re){

        return $matches[1]
    }else{
       $re2 = "^IP address: (\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})" 
       $r = [regex]::Matches($messageBody, $re2, 'MultiLine') | % { $_.Groups[1].Value }
       return $r
    }

}

function getDestURL($messageBody){

    if($messageBody -match "URL: (.+)"){
        return $matches[1]
    } 
}

function getCategory($messageBody){

    if($messageBody -match "Category: ([a-z A-X]*)"){
         return $matches[1]
    } 
}


function getFileName(){
    return "C:\users\alibaba\Documents\powershell\vectors-{0:MM}{0:dd}{0:hh}{0:mm}{0:ss}.csv" -f (Get-Date)
}

Add-Type -assembly "Microsoft.Office.Interop.Outlook"
$Outlook = New-Object -comobject Outlook.Application
$namespace = $Outlook.GetNameSpace("MAPI")
$websenseEmails = $namespace.Folders.item(2).Folders.item(2).Folders.item('websense').items

$alertList = @()
foreach($email in $websenseEmails){
    $emailBody = $email.Body
    $subject = $email.TaskSubject.split(":")[1]

    if(isSecurityAlert $subject){       

        $event = [pscustomobject]@{
              Category = getCategory $emailBody
              creationTime = $email.CreationTime
              sourceIP = getSourceIP $emailBody
              destIP = getDestIP $emailBody
              destURL = getDestURL $emailBody 
              FirstName = getFirstName $emailBody
              LastName = getLastName $emailBody
            }
        $alertList +=$event

    }

}

$filename = getFileName
$alertList | export-Csv $filename -NoTypeInformation

EDIT: Paste of two different input samples. Scrubbed of identifying data:

Date: 4/16/2015 10:24:31 AM
Type: Information
Source: Upliftify Usage Monitor

A client has exceeded a configured daily Internet usage threshold.

For more information, run investigative or presentation reports in RIPON - Web Upliftify. See the RIPON - Web Upliftify Help system for details.

User name: LDAP://ABC199401V01 OU=CHUMP,OU=Users,OU=ABC_EFG,DC=wi-abc1,DC=cc,DC=abc/Juicy\, Jane User IP address: 11.111.11.111 Threshold (in visits): 100
Category: Illegal or Chump
Action: Blocked

--Most recent request--
URL: HTTPS://77.77.777.777:443
IP address: 77.777.777.777
Port: 443

Date: 4/22/2015 7:36:30 PM
Type: Information
Source: Upliftify Usage Monitor

Suspicious activity has exceeded the alerting threshold for this severity level.

Severity: High
Category: Bot Networks
Filtering action: Blocked
Threshold (in hits): 5

Log on to Upliftify - Web Upliftify and access the Upliftify dashboard for more details about these incidents.

Access Upliftify - Web Infinity here: https://xx.xx.xx.xx:944/Upliftify/Upliftify/?eip.tab=wsg

---Most recent incident---
User: 
IP address: 11.11.11.111
URL: http://asksdofsdf.info/get/
Destination IP address: 195.195.194.103   Port: 80
\$\endgroup\$
  • \$\begingroup\$ Can we see some sample input data for this? \$\endgroup\$ – Matt Jun 18 '17 at 19:18
  • \$\begingroup\$ Thanks for responding, Matt. I pasted two (well-scrubbed) input samples to the original question. \$\endgroup\$ – mcgyver5 Jun 20 '17 at 17:08
  • \$\begingroup\$ In that second example is it a mistake that the port is on the same line as destination ipaddress? I also see similar behaviour with User on the first example for ipaddress and threshold. Do the mails really look like that or is it a mistake from the scrubbing? Its important for one of my suggestions. \$\endgroup\$ – Matt Jun 20 '17 at 18:30
  • \$\begingroup\$ no, those are in the same line in the emails. \$\endgroup\$ – mcgyver5 Jun 20 '17 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.