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

Write

Answer this question

To reply or comment, use the 'Comment' link on the relevant answer or question.

Sql Query script

Answer this question

Related Questions