Search Email AND output TAB delimited

I was trying to help someone who wanted a TAB delimited output file in PowerShell.
 
After some searching around and some help from the boards, I came up with this;
 
<———— Start POSH —————————————————————–>
 
$info = New-Object -ComObject Outlook.Application
$objNamespace = $info.GetNamespace("MAPI")
$objFolder = $objNamespace.GetDefaultFolder(6).Folders.Item("Reporting")
$colItems = $objFolder.Items
foreach ($item in $colItems) {
 If ($item.SenderEmailAddress -eq "Bill.Smith@myob.com") {
 Write-Host "$($item.SenderEmailAddress) `t $($item.SentOn) `t $($item.Subject)"
                   }
        }
 
<———–End Script ———————————————————————->
 
Or you could output to a file by removing the Write-Host and adding | Add-Content $path
 
<—————– Start POSH ————————————————————–> 
$path = "c:tempemail.txt"
$info = New-Object -ComObject Outlook.Application
$objNamespace = $info.GetNamespace("MAPI")
$objFolder = $objNamespace.GetDefaultFolder(6).Folders.Item("Merchant Reporting")
$colItems = $objFolder.Items
foreach ($item in $colItems) {
 If ($item.SenderEmailAddress -eq "Bill.Smith@myob.com") {
("$($item.SenderEmailAddress) `t $($item.SentOn) `t $($item.Subject)")| Add-Content $path
                   }
        }
 
< ————– End Script —————————————————————————->
What does it do?
 
Searches the In box for a sub folder and then for a sender.
writes them out to the screen or a file in a tab delimited format
Includes Senders name, Date/time sent and subject.
Its the `t that adds the tabs. `n adds a new line.  ‘r adds a line feed  
 

How do I find the last used row in Excel

So I saw a question on the Posh board yesterday and being a wise guy, I had to answer it.
 
Q:

I know how to open spreadsheet via powershell, question is whether there is
a way to determine the rows that contain data

Eg  I coded row less than 5000

for($row = 2; $row -lt 5000; $row++)
{

## i m looking at cell 1, so if cell 1 on the next row is empty, i will quit
the program
 $computername = $ws.Cells.Item($row,1).text
 if ($computername.length -eq 0) {exit}

}

 Is there a way to define number of rows rather than i put a number of 5000
or any other number  ?

 
My Answer:
 
You could try selecting the Active Range. That is all the Rows and
columns that have Data in them;
Then you go to the last used row;

In Powershell;

<—— PS Script ———————————> 

$xlCellTypeLastCell = 11
$xl = New-Object -c excel.application
$wb = $xl.Workbooks.Open("C:ScriptsBook5.xlsx")  #<– Your spreadsheet name goes here
$sh = $wb.worksheets.item(‘Sheet1’) #<– the sheet number or name goes here.
$xl.visible = $true
$used = $sh.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$newRow = $lastCell.row + 1
$sh.cells.item($newRow, 1).value2 = ‘This is the new last row now’

 <———– End Script ———————————>

 Or in vbScript

<——vbScript ———————————>

Const xlCellTypeLastCell = 11
Set oXL = CreateObject ("Excel.Application")
oXL.Visible = True
oXL.DisplayAlerts = False

Set objWorksheet = book1.Worksheets(1)
Set objRange = objWorksheet.UsedRange
objRange.SpecialCells(xlCellTypeLastCell).Activate
intNewRow = oXL.ActiveCell.Row + 1
strNewCell = "A" &  intNewRow
oXL.Range(strNewCell).Activate

<———– End Script ———————————>
 

 

Is It Unix or Windows ?

 

So someone hands you list of servers and they want to know everything there is to know about said servers by COB today.

 
Time for a script! But wait, when you run your script, you get several servers that do not reply or give you RPC failures.
Are they dead? Disconnected? or are they Unix?
 
Vadims Podāns on the PowerShell google group had this to say,
Well, here is a quick and dirty way to find out if they are Unix or Windows.
You can simply ping remote the host.
By default all *nix (Linux) reply with TTL=64 and Windows hosts reply with
TTL=128 this helps only determine the host is UNIX or not and you will unable to
determine Unix version. Be aware that at ICMP packet passage through each router ttl decreases by 1.
 
So using Vadims information, I came up with this Posh script.
#<—————- Begin ———————————————–>
$ping = New-Object System.Net.NetworkInformation.Ping
$computerlist = Get-Content ‘c:scriptsServers.txt’
foreach ($srv in $computerlist)
{
        $reply = $ping.Send($srv)
        if ($reply.status -eq "Success")
        {
                if ($reply.Options.ttl -le 64)
                {
                Write-Host $srv " probably is a Unix host"
                        }
                Else
                        { if ($reply.Options.ttl -ge 65)
                                {
                        Write-Host $srv " probably is a Windows host"
                                }
                }
        }
        Else { Write-Host $srv " Does Not Ping"
                 }
}
#<———————– End Script ————————————-­—–>
 
So I was getting some inconsistent results on my Vista work station, so I re-wrote the script like so;
 
  $computerlist = Get-Content ‘c:scriptsServers.txt’
 foreach ($srv in $computerlist)
 {
$response = Get-WmiObject -query "Select * From Win32_PingStatus Where Address = ‘$srv’"
 
  if( ($response -eq $null) -or ($response.StatusCode -ne 0)) {
                Write-Host $srv " Does Not Ping"
        } else { if ($response.TimeToLive -le 64)
                       {
                Write-Host $srv " probably is a Unix host"
                        }
                Else {
                        Write-Host $srv " probably is a Windows host" 
                       }
                }
        }
 

Setting up SNMP via a script

I have been trying to do this for way too long. There does not seem to be a way that I understand to set up SNMP via a script.
 
I did, however discover this little gem;
 
sysocmgr that you can read about here: http://support.microsoft.com/kb/222444
 
Usage:
 
sysocmgr /i:c:Windowsinfsysoc.inf /r /u:c:tempsnmp_cfg.txt
And c:tempsnmp_cfg.txt looks like this:
 
[Components]
NetOC=on
[NetOptionalComponents]
SNMP=1
[SNMP]
 Accept_CommunityName = public:Read_Create
 Any_Host = Yes
 Community_Name = public
 Contact_Name = "OldDog"
 Location = "MYOB, SF, Ca"
 Traps = 172.xx.xxx.xxx <- where you want them sent. My HP-SIM server or SCOM or wherever.
 Service = Physical, Applications, Datalink, Internet, End-to-End

Reading Email with a script and doing stuff


I am setting up HP-Sim for a client. One of the things they wanted was for SIM alerts to go directly to their Incident manager software. If the Email is formatted correctly, it will generate a Trouble Ticket automatically. Unfortunately, the email from HP-Sim is set in stone. You can change the subject line but not the body of the message. And that’s where my customer needs the properly formatted information. So, I set up a Rule in Outlook that runs an application upon receipt of a message with specified words in the Subject line. The scrip reads the mail, and then re-sends it to the help-desk app in the proper format. Here is the script, I think it’s a good example of how to read and act upon an email message.

PS: WordPress is converting the quote “, the ‘less than’ charactor and the ‘greater than’ charactor in my script so be alert!


Set objOL = WScript.CreateObject ("outlook.application")
Set olNS = objOL.GetNameSpace("MAPI")
Set fld = olNS.GetDefaultFolder(6)
set msg = fld.items

  For Each item In msg

    If item.SenderEmailAddress = "system.generated@myob.com" Then ‘      ProductionMsg = item.body ‘       Mailit(ProductionMsg)
    Else
             On Error GoTo 0
             WScript.Quit
    End If
 Next
Sub Mailit(ProductionMsg)
adminmail = "helpdesk@myob.com"
‘
Cat="Category=System Generated Requests" ‘CatSub="CategorySub=HP-SIM" ‘Sev="Severity=Tier 3"  ‘ProductionMsg = Cat & VbCrLf & CatSub & VbCrLf & Sev & VbCrLf &  VbCrLf & ProductionMsg
 smtp = "smtp.myob.com"
 Set mailing = CreateObject("CDO.Message")
 Set wshNet = CreateObject("WScript.Network")

 mailing.From = ("system.generated@myob.com")
 mailing.To = adminmail
 mailing.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
 mailing.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = smtp
 mailing.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

‘ Only required if your SMTP server requires a user name and password

‘mailing.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
‘mailing.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "Ja…@company.com"
‘mailing.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "SomePassword"

mailing.Subject = header & "HP-Sim Alert"
 mailing.TextBody = ProductionMsg
 mailing.Configuration.Fields.Update
 mailing.Send
 On Error GoTo 0

End Sub

You can also do stuff with PowerShell;


$info = New-Object -ComObject Outlook.Application
$objNamespace = $info.GetNamespace("MAPI")
$objFolder = $objNamespace.GetDefaultFolder(6).Folders.Item("HPSIMNotify")
$colItems = $objFolder.Items
foreach ($item in $colItems) {
 Write-Host $item.SentOn
}

This looks in the inbox (6) and looks for a sub folder called (“HPSIMNotify”)
It then tells you the date and time the message was sent.

What other folders can you look in?

 
Name

Value

Description

olFolderCalendar

9

The Calendar folder.

olFolderConflicts

19

The Conflicts folder (subfolder of Sync Issues folder). Only available for an Exchange account.

olFolderContacts

10

The Contacts folder.

olFolderDeletedItems

3

The Deleted Items folder.

olFolderDrafts

16

The Drafts folder.

olFolderInbox

6

The Inbox folder.

olFolderJournal

11

The Journal folder.

olFolderJunk

23

The Junk E-Mail folder.

olFolderLocalFailures

21

The Local Failures folder (subfolder of Sync Issues folder). Only available for an Exchange account.

olFolderManagedEmail

29

The top-level folder in the Managed Folders group. For more information on Managed Folders, see Help in Microsoft Office Outlook. Only available for an Exchange account.

olFolderNotes

12

The Notes folder.

olFolderOutbox

4

The Outbox folder.

olFolderSentMail

5

The Sent Mail folder.

olFolderServerFailures

22

The Server Failures folder (subfolder of Sync Issues folder). Only available for an Exchange account.

olFolderSyncIssues

20

The Sync Issues folder. Only available for an Exchange account.

olFolderTasks

13

The Tasks folder.

olFolderToDo

28

The To Do folder.

olPublicFoldersAllPublicFolders

18

The All Public Folders folder in the Exchange Public Folders store. Only available for an Exchange account.

olFolderRssFeeds

25

The RSS Feeds folder.

Want to see what the body of the message looks like?
 
Write-Host $item.body
 
How about the subject?
 
Write-Host $item.Subject
 
All together now;
 
#

$info = New-Object -ComObject Outlook.Application
$objNamespace = $info.GetNamespace('MAPI')
$objFolder = $objNamespace.GetDefaultFolder(6).Folders.Item('HPSIMNotify')
$colItems = $objFolder.Items
foreach ($item in $colItems) {
 Write-Host 'date/time sent: '$item.SentOn
 Write-Host 'Subject: ' $item.Subject
 Write-Host 'Body Text: '$item.body
}

#