3 State Monitor with Power Shell Script

PowerShell
,
1 
$Filepath ="C:\temp\VDN.xlsx"
 
# Variablen
[int]$Zeile = 2 # ab 2, da in Zeile 1 Die Titel stehen
[int]$Spalte = 7
[int]$Spalte2 = 2
[int]$Spalte2 = 1

 
#Instanzieren eines COM-Objektes für Excel
$Excel = New-Object -ComObject excel.application
#Excel-Sheet sichtbar machen, heißt, ob es im Vordergrund oder im Hintergrund geschehen soll.
$Excel.Visible = $false

#Arbeitsmappe laden
$Workbook = $Excel.Workbooks.Open($Filepath)
$Table =$workbook.Worksheets.Item(1)

 
#PropertyBag für SCOM
$api = New-Object -comObject “MOM.ScriptAPI”  
$PropertyBag = $api.CreatePropertyBag()


# Datei auslesen
$VDN2 = $Table.Cells.Item($Zeile,$Spalte).Text
$VDN3 = $Table.Cells.Item($Zeile,$Spalte2).Text
$VDN4 = $Table.Cells.Item($Zeile,$Spalte3).Text

if($VDN2 -eq 1){ 
$PropertyBag.AddValue("State",$VDN2)
}
elseif($VDN2 -eq 2){
$PropertyBag.AddValue("State",$VDN2}
elseif($VDN2 -eq 3){
$PropertyBag.AddValue("State",$VDN2)
$PropertyBag.AddValue("VDN_number:",$VDN3)
$PropertyBag.AddValue("VDN_customer:",$VDN4)
}

#Daten an SCOM senden


$PropertyBag

Hi

The Code above shows my simple script. I try to monitor a value in an Excel-Sheet. When i give some explicit values like (1,2,3) for my variable $VDN2 the Monitor works fine. But when i try to read that value from my Excel-Sheet it doesn’t. Does anybody has an idea?

My Excel-Sheet contains a simple table where i want to check only the value ($VDN2 wich is 1, 2 or 3)

1 Like
2

thx for your help…first i wanna try to run the script manually and look for the values in the propertybag…how can i show this values?

When i run the script i only get System.__ComObject

3

You can start by just adding

write-host $VDN2

write-host $VDN3

write-host $VDN4

To see that it picks up the values from the excelfile.

1 Like
4

I already tryed this and it worked fine.

It would be nice if i can check the value in the propertybag. So is there any way to read out the values from the propertybag? (write-host $propertybag shows System.__ComObject)

5

Never tryed logging something with PS. Can you help me with that?

6

Hi,
tryed that way. And the result was, that the values were not picked up.

So maybe the runas account does not have the permissions.

But i do not know why because thats the Standard Action Account. Do you know how to change the runas account in scom for a specific monitor? So maybe that i can test the script with my Domain-Admin.

7

Hi,

the Monitor works fine now. Thank you for your help.

Kind regards

1 Like
8

Great! Was the problem related to permissions?

9

If you run the script manually does it pick up the values from the excel file?
One guess is that the monitoring account does not have permissions to open the Excelfile. Could you setup so that the monitor logs out the values it picks up from the excel file.
Try running procmon when the script runs to see if you run into any access denied or file not found.

10

If you log those values from $vdn2,3,4 to a file or eventlog when the script runs from scom you can see if scom is successfull with picking up the values from the excelfile or if it fails there.

I’m not sure how you can show the content of system._comobject from powershell. If it was vb you could run $x.return($propertybag) the get the xml. But that does not work in powershell.

11

An easy way to log via powershell is the add-content function.

Something like this:

add-content c:\admin\logfile.txt -Value “Value 1:$a”
and then add all the values you want to log.

just make sure that system or your runas account has full access to the logfile.

12

Try running procmon to see when and if you get an access denied.
https://docs.microsoft.com/en-us/sysinternals/downloads/procmon
The problem could be that the runas account cant start Excel (log on locally?). One workaround would be to save the file as an csv instead. Then you could use import-csv instead.