Sql Query script

Question

Sql Query script

0

Erenay Tozun 3 Rep. 30 Jan, 2019

Hey Guys,

I made a script for Scom monitoring with powershell.

 $ScomAPI = New-Object -comObject "MOM.ScriptAPI"
                    $PropertyBag = $ScomAPI.CreatePropertyBag()

  
    
        $sqlConn = New-Object System.Data.SqlClient.SqlConnection
            $sqlConn.ConnectionString = “Server=*****\*******;Integrated Security=true;Initial Catalog=n*****”
                $sqlConn.Open()

        $sqlcmd = New-Object System.Data.SqlClient.SqlCommand
            $sqlcmd.Connection = $sqlConn
                    $query = "
                               SELECT count(*)
                                 FROM HL7_Outbox
                             "  
                             
        $sqlcmd.CommandText = $query

        $adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
           
              $SqlQeuryOutput = $sqlcmd.ExecuteScalar()
  
        
             
                                
$Result = "bad"
       
      if ($SqlQeuryOutput -lt "1" ) {

        $Result = "good"
        
          
    } 




    
   Write-EventLog -LogName 'Operations Manager' -Source "MyScript" -EventId 1 -EntryType Warning -Message "This is a test 2.0, the state of this monitor is $Result, $SqlQeuryOutput, "
    
    

      $sqlConn.Close()

# fill pb
$PropertyBag.AddValue("Result",$Result)
             
# Send output to SCOM
$PropertyBag

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

$ScomAPI = New-Object -comObject "MOM.ScriptAPI"

$PropertyBag = $ScomAPI.CreatePropertyBag()

$sqlConn = New-Object System.Data.SqlClient.SqlConnection

$sqlConn.ConnectionString = “Server=*****\*******;Integrated Security=true;Initial Catalog=n*****”

$sqlConn.Open()

$sqlcmd = New-Object System.Data.SqlClient.SqlCommand

$sqlcmd.Connection = $sqlConn

$query = "

SELECT count(*)

FROM HL7_Outbox

"

$sqlcmd.CommandText = $query

$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd

$SqlQeuryOutput = $sqlcmd.ExecuteScalar()

$Result = "bad"

if ($SqlQeuryOutput -lt "1" ) {

$Result = "good"

}

Write-EventLog -LogName 'Operations Manager' -Source "MyScript" -EventId 1 -EntryType Warning -Message "This is a test 2.0, the state of this monitor is $Result, $SqlQeuryOutput, "

$sqlConn.Close()

# fill pb

$PropertyBag.AddValue("Result",$Result)

# Send output to SCOM

$PropertyBag

The entire script works perfect in powershell.

after i made it the script work on powershell, i made a two-state monitor from it. but once the monitor was put in scom, the monitor is giving me the exact opposite awnser i am getting from powershell, so powershell and the two-state monitor have the exact same script, but different answers. how is this possible?

2-state-monitor-alerting-with-powershell powershell powershell-mp sql sql-query

Erenay Tozun edited question 20 Nov, 2019

Toggle fullscreen