none
Creating new monitor rule using Powershell Script forrunnig sql query to find number of connection

    Question

  • Hi,

    I have tried to create Powershell Script to run sql query but it not working  i am not able to fix to change the monitor  from healthy expression to unhealthy. I wrote simple sql query that count number of connection and run it in powershell and it return  the number of connection to 3.

    Then i change it to script including property bag .

    Below is the screenshot .Can u check and see where is the error,please.

    Here is the script:

    Dim oAPI, oBag

    Set oAPI = CreateObject("MOM.ScriptAPI")
    Set oBag = oAPI.CreatePropertyBag()

    [string]$SQLServer = "sqlserver"

    $SQLString = "SELECT  COUNT(dbid) as NumberOfConnections FROM abc WHERE DB_NAME(dbid)='xyz'  GROUP BY dbid"

    $out = invoke-sqlcmd -query $SQLString -ServerInstance $SQLServer

    $count=$out.NumberOfConnections

    if($count -eq 10 )
    {
    Call oBag.AddValue("State","OK")

    }
    else
    {
    Call oBag.AddValue("State","ERROR")

    }


    Call oAPI.Return(oBag)


    Thursday, March 31, 2016 12:12 PM

Answers

  • Hello,

    We do not need to create any run as account. You Need to Makesure NT_Authority/System has SysAdmin access tothe SQL Server or DB (By default it has Public Access to the DBs).

    As I looked into your screenshots, your configuration looks correct. Nothing much that is needed apart from the permission.

    For Functionality please login to any one Server, which part of Class SQL 2012 DB object and look for Event 21406 and description should have information about your Script Name and execution. If there are no Warning Event for 21406, then look for Event ID 1, Which should have error description from the script.

    Ignore Override concept for a Server, I meant to do it for a Particular DB. otherwise it would generate alert for all the DBs in Class SQL 2012 DB.


    Thanks, Goutam

    Thursday, April 07, 2016 6:46 AM

All replies

  • can you post the mp/xml of this rule? even though you have quite some screenshots you aren't showing all information needed.

    1 thing i notice is you have 0 state changes on the monitor, which means it might have never run correctly (just initialize more than 7 (or your retention settings) days ago).


    Rob Korving
    http://jama00.wordpress.com/

    Thursday, March 31, 2016 1:01 PM
  • Hi,

    I have again post all the step I did to make new monitor. 

    First I install  Sample Powershell Script so that i can run powershell script from the link:

    Powershell script 

    Then I followed this following steps:

    The Stage Changes from 0 to 1.

    And Here is the Script:

    Dim oAPI, oBag

    Set oAPI = CreateObject("MOM.ScriptAPI")
    Set oBag = oAPI.CreatePropertyBag()

    [string]$SQLServer = "servername"

    $SQLString = "SELECT  COUNT(dbid) as NumberOfConnections FROM abc WHERE DB_NAME(dbid)='xyz'  GROUP BY dbid"

    $out = invoke-sqlcmd -query $SQLString -ServerInstance $SQLServer

    $count=$out.NumberOfConnections

    if($count -eq 10 )
    {
    Call oBag.AddValue("State","OK")

    }
    else
    {
    Call oBag.AddValue("State","ERROR")

    }


    Call oAPI.Return(oBag)

    Here is the  mp/Xml File:

    <?xml version="1.0" encoding="utf-8"?><ManagementPack ContentReadable="true" SchemaVersion="2.0" OriginalSchemaVersion="1.1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <Manifest>
        <Identity>
          <ID>ForSQLQuery</ID>
          <Version>1.0.0.0</Version>
        </Identity>
        <Name>ForSQLQuery</Name>
        <References>
          <Reference Alias="MicrosoftWindowsLibrary7585010">
            <ID>Microsoft.Windows.Library</ID>
            <Version>7.5.8501.0</Version>
            <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
          </Reference>
          <Reference Alias="Powershell">
            <ID>Sample.Powershell.Monitor.Type.MP.v2</ID>
            <Version>1.0.1.19</Version>
            <PublicKeyToken>e382b51e36d2ad54</PublicKeyToken>
          </Reference>
          <Reference Alias="MicrosoftSQLServerLibrary6640">
            <ID>Microsoft.SQLServer.Library</ID>
            <Version>6.6.4.0</Version>
            <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
          </Reference>
          <Reference Alias="SQLServer">
            <ID>Microsoft.SQLServer.2012.Discovery</ID>
            <Version>6.6.4.0</Version>
            <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
          </Reference>
          <Reference Alias="SystemLibrary7585010">
            <ID>System.Library</ID>
            <Version>7.5.8501.0</Version>
            <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
          </Reference>
          <Reference Alias="SystemCenter">
            <ID>Microsoft.SystemCenter.Library</ID>
            <Version>7.0.8433.0</Version>
            <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
          </Reference>
          <Reference Alias="Health">
            <ID>System.Health.Library</ID>
            <Version>7.0.8433.0</Version>
            <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
          </Reference>
        </References>
      </Manifest>
      <Monitoring>
        <Monitors>
          <UnitMonitor ID="UIGeneratedMonitord36604c013d54f899bba7c30bbc2973d" Accessibility="Public" Enabled="true" Target="SQLServer!Microsoft.SQLServer.2012.Database" ParentMonitorID="Health!System.Health.ConfigurationState" Remotable="true" Priority="Normal" TypeID="Powershell!Wei.Out.There.TimedPowershell.v2.TwoStateMonitorType" ConfirmDelivery="false">
            <Category>Custom</Category>
            <AlertSettings AlertMessage="UIGeneratedMonitord36604c013d54f899bba7c30bbc2973d_AlertMessageResourceID">
              <AlertOnState>Error</AlertOnState>
              <AutoResolve>true</AutoResolve>
              <AlertPriority>Normal</AlertPriority>
              <AlertSeverity>Error</AlertSeverity>
            </AlertSettings>
            <OperationalStates>
              <OperationalState ID="UIGeneratedOpStateId351d96e2622b41409f838bc97a053438" MonitorTypeStateID="Success" HealthState="Success" />
              <OperationalState ID="UIGeneratedOpStateIdf8c5d29ec2974bb3ae40f8b39dfcaf3e" MonitorTypeStateID="Error" HealthState="Error" />
            </OperationalStates>
            <Configuration>
              <IntervalSeconds>120</IntervalSeconds>
              <SyncTime />
              <TimeoutSeconds>60</TimeoutSeconds>
              <ScriptName>MyScript.ps1</ScriptName>
              <Arguments />
              <ScriptBody>Dim oAPI, oBag

    Set oAPI = CreateObject("MOM.ScriptAPI")
    Set oBag = oAPI.CreatePropertyBag()

    [string]$SQLServer = "servername"

    $SQLString = "SELECT  COUNT(dbid) as NumberOfConnections FROM sys.sysprocesses  WHERE DB_NAME(dbid)='Tfs_Configuration'  GROUP BY dbid"

    $out = invoke-sqlcmd -query $SQLString -ServerInstance $SQLServer

    $count=$out.NumberOfConnections

    if($count -eq 10 )
    {
    Call oBag.AddValue("State","OK")

    }
    else
    {
    Call oBag.AddValue("State","ERROR")

    }


    Call oAPI.Return(oBag)</ScriptBody>
              <ErrorExpression>
                <SimpleExpression>
                  <ValueExpression>
                    <XPathQuery Type="String">Property[@Name='State']</XPathQuery>
                  </ValueExpression>
                  <Operator>Equal</Operator>
                  <ValueExpression>
                    <Value Type="String">ERROR</Value>
                  </ValueExpression>
                </SimpleExpression>
              </ErrorExpression>
              <SuccessExpression>
                <SimpleExpression>
                  <ValueExpression>
                    <XPathQuery Type="String">Property[@Name='State']</XPathQuery>
                  </ValueExpression>
                  <Operator>Equal</Operator>
                  <ValueExpression>
                    <Value Type="String">OK</Value>
                  </ValueExpression>
                </SimpleExpression>
              </SuccessExpression>
            </Configuration>
          </UnitMonitor>
        </Monitors>
        <Overrides>
          <MonitorPropertyOverride ID="OverrideForMonitorUIGeneratedMonitord36604c013d54f899bba7c30bbc2973dForContextMicrosoftSQLServer2012Database92a91bf161ee4ba79b9b2c42f3e4e1cc" Context="SQLServer!Microsoft.SQLServer.2012.Database" Enforced="false" Monitor="UIGeneratedMonitord36604c013d54f899bba7c30bbc2973d" Property="Enabled">
            <Value>true</Value>
          </MonitorPropertyOverride>
        </Overrides>
      </Monitoring>
      <Presentation>
        <Folders>
          <Folder ID="Folder_90c4aa6d729d4c289d6324fdb0428b15" Accessibility="Public" ParentFolder="SystemCenter!Microsoft.SystemCenter.Monitoring.ViewFolder.Root" />
        </Folders>
        <StringResources>
          <StringResource ID="UIGeneratedMonitord36604c013d54f899bba7c30bbc2973d_AlertMessageResourceID" />
        </StringResources>
      </Presentation>
      <LanguagePacks>
        <LanguagePack ID="ENU" IsDefault="false">
          <DisplayStrings>
            <DisplayString ElementID="ForSQLQuery">
              <Name>ForSQLQuery</Name>
              <Description>Test MP to run PowerShell SQL query</Description>
            </DisplayString>
            <DisplayString ElementID="Folder_90c4aa6d729d4c289d6324fdb0428b15">
              <Name>ForSQLQuery</Name>
            </DisplayString>
            <DisplayString ElementID="UIGeneratedMonitord36604c013d54f899bba7c30bbc2973d">
              <Name>TestToRunSqlQuery</Name>
            </DisplayString>
            <DisplayString ElementID="UIGeneratedMonitord36604c013d54f899bba7c30bbc2973d_AlertMessageResourceID">
              <Name>TestToRunSqlQuery</Name>
              <Description>Please see the alert context for details.</Description>
            </DisplayString>
            <DisplayString ElementID="UIGeneratedMonitord36604c013d54f899bba7c30bbc2973d" SubElementID="UIGeneratedOpStateIdf8c5d29ec2974bb3ae40f8b39dfcaf3e">
              <Name>Error</Name>
            </DisplayString>
            <DisplayString ElementID="UIGeneratedMonitord36604c013d54f899bba7c30bbc2973d" SubElementID="UIGeneratedOpStateId351d96e2622b41409f838bc97a053438">
              <Name>Success</Name>
            </DisplayString>
          </DisplayStrings>
        </LanguagePack>
        <LanguagePack ID="NLD" IsDefault="false">
          <DisplayStrings>
            <DisplayString ElementID="UIGeneratedMonitord36604c013d54f899bba7c30bbc2973d" SubElementID="UIGeneratedOpStateIdf8c5d29ec2974bb3ae40f8b39dfcaf3e">
              <Name>Error</Name>
            </DisplayString>
            <DisplayString ElementID="UIGeneratedMonitord36604c013d54f899bba7c30bbc2973d" SubElementID="UIGeneratedOpStateId351d96e2622b41409f838bc97a053438">
              <Name>Success</Name>
            </DisplayString>
          </DisplayStrings>
        </LanguagePack>
      </LanguagePacks>
    </ManagementPack>


    Thursday, March 31, 2016 2:02 PM
  • The last two images are here :

    Thursday, March 31, 2016 2:03 PM
  • Hi,

    You need to make sure the account used to run this monitor has the correct permissions on SQL. As you don't have a RunAs account configured it will try to run it onder the default account which is Local System in most cases. This account doesn't have access to SQL in most cases.

    Do you have any workflow failures on the server where it's targeted?

    Best regards,

    Marthijn.

    Monday, April 04, 2016 10:47 AM
  • By default, your custom monitor runas account is scom default action account and also your monitor involve SQL command and Powershell cmdlet. Make sure that your default action account has privilege in running SQL command and Powershell cmdlet.

    Roger

    Monday, April 04, 2016 12:28 PM
  • Hello,

    As per your Monitor, it validates execution of powershell Script for Error or Success, Does not depend on the parameters what you are using for Unhealthy or Healthy Condition. You can use Unit Monitor -> Scripting -> Generic -> Two State Monitor and proceed rest as usual.

    This should help you do it.


    Thanks, Goutam


    Monday, April 04, 2016 1:26 PM
  • Hi @Goutam,

    Does Unit Monitor -> Scripting -> Generic -> Two State Monitor  support powershell script also. As I know scom only support Vb script isn't it.

    Thanks,


    Tuesday, April 05, 2016 8:10 AM
  • You are correct. Two State Monitor only supports VB.

    However i found that your script is wrong. Here is the correct script:

    $Script:API             = new-object -comObject "MOM.ScriptAPI"
    $Script:Bag             = $Script:API.CreatePropertyBag()
    $Script:LOG_ERROR       = 1
    $Script:LOG_WARNING     = 2
    $Script:LOG_INFORMATION = 4
    add-pssnapin SqlServerCmdletSnapin100
    [string]$SQLServer = "Server Name"

    $SQLString = "Your Query"

    $out = invoke-sqlcmd -Server $SQLServer -database "OperationsManager" -query $SQLString

    if ($error.Number -gt 0)
    {
    $Script:Bag.AddValue("State","Failed")
    $Script:API.Return($Bag)
    }
    Else
    {
    if($out.count -le 10 )
    {

    write-host "Query execution is successfullyyyyyyyyy"
    write-host "The count is " $out.count
    $Script:API.LogScriptEvent("TestPS1.ps1",100,$Script:LOG_INFORMATION, "The count is " + $out.count)
    $Script:Bag.AddValue("State","OK")
    $Script:Bag.AddValue("ret","The count is "+$out.count)
    $Script:API.AddItem($Script:Bag)
    }
    else
    {

    write-host "Query execution is successfull"
    write-host "The count is " $out.count

    $Script:API.LogScriptEvent("TestPS1.ps1",103,$Script:LOG_INFORMATION, "The count is " + $out.count)
    $Script:Bag.AddValue("State","ERROR")
    $Script:Bag.AddValue("ret","The count is "+$out.count)
    $Script:API.AddItem($Script:Bag)
    }
    }
    remove-pssnapin SqlServerCmdletSnapin100
    $Script:API.Return($Script:Bag)

    Thanks, Goutam



    Tuesday, April 05, 2016 12:28 PM
  • Hello There,

    I have tested the Sample MP and the above script. The script is working fine and able to log event with the current value. But it does not change anything from the Alerting or health Perspective. From this we Landed into two situations.

    1. Create a new probe Action with above PS to have the monitoring enabled;

    or 

    2. Use below VB script to have your query count monitored. it is tested and you do not have to create any probe action.

    Here is the Script..

    'on error resume next            
        strComputer="Your SQL Server Name\instance Name"
                    strDB="Data Base Name"
                    strSQLQuery="SQL Query to be executed"
    intThreshold="Threshold Value to monitor"
                    Dim objCN, strConnection
                    Dim objAPI, oBag
                    Set objAPI = CreateObject("MOM.ScriptAPI")
                    Set objCN = CreateObject("ADODB.Connection")
                    Set objAPI = CreateObject("MOM.ScriptAPI")
                    Set oBag = objAPI.CreatePropertyBag()
                    'strConnParams = "Provider=SQLOLEDB;Data Source=" & strComputer &"; Initial Catalog=" & strDB & ";User Id=user;Password=Password"
    strConnParams = "Provider=SQLOLEDB;Data Source=" & strComputer &"; Initial Catalog=" & strDB & ";Integrated Security=SSPI;" 
                    Set objConn = CreateObject("ADODB.Connection")
                    objConn.Open strConnParams

                    
    intThreshold=cdbl(intThreshold)

                    strhostname=hostname
                    'wscript.echo strhostname
                    Dim strSQLQuery
                    

                    Dim objRS
                    Set objRS= CreateObject("ADODB.Recordset")

                    objRS.open strSQLQuery, objConn

          rowcount=cdbl(objRS.fields.item(0))


                        If Err.Number <> 0 Then
     
    oBag.AddValue "Error", Err.Description 
    oBag.AddValue "Db", strDB 
    oBag.AddValue "Query", strSQLQuery
    oBag.AddValue "Threshold", intThreshold
                    objAPI.AddItem(oBag)
    objapi.logscriptevent "SQLDBCount.vbs",1,0,"Row Count:" & rowcount &  vbcrlf & "Query: " & strSQLQuery & vbcrlf & "Errors: " & Err.Description 
      Err.Clear

       Else

    if rowcount > intThreshold Then
    State="BAD"
    else
    State="GOOD"
    End if

    oBag.AddValue "State", State
    oBag.AddValue "Rows", rowcount 
    oBag.AddValue "Error", "NO ERRORS"
    oBag.AddValue "Db", strDB 
    oBag.AddValue "Query", strSQLQuery
    oBag.AddValue "Threshold", intThreshold

                       objAPI.AddItem(oBag)
    objapi.logscriptevent "SQLDBCOunt.vbs",1,0,"Row Count:" & rowcount &  vbcrlf & "Query: " & strSQLQuery & vbcrlf & "Errors if any: " & Err.Description 
      Err.Clear
        End If

    End If
                    

                    objAPI.Return(oBag)
                    objRS.Close


    Thanks, Goutam

    • Proposed as answer by Goutam.Nepak Tuesday, April 05, 2016 4:36 PM
    Tuesday, April 05, 2016 4:34 PM
  • Hi @Goutam.Nepak,

    I have tried your vb script . First i only try the vbsdcript to connect to SQL server and it is returning the value the n i copied all your code but still it is not changing health state. Here is what i have done step by step :

    Other are in next comment


    Wednesday, April 06, 2016 1:31 PM
  •  rest are here :

    Here is the script i have used:

    'on error resume next            
        strComputer="myservername"
                    strDB="master"
                    strSQLQuery="Select Count (dbid) from [sys].[sysprocesses] where DB_NAME  (dbid)='Tfs_TFSTest' GROUP by dbid"
    intThreshold=10
                    Dim objCN, strConnection
                    Dim objAPI, oBag
                    Set objAPI = CreateObject("MOM.ScriptAPI")
                    Set objCN = CreateObject("ADODB.Connection")
                    Set objAPI = CreateObject("MOM.ScriptAPI")
                    Set oBag = objAPI.CreatePropertyBag()
                    'strConnParams = "Provider=SQLOLEDB;Data Source=" & strComputer &"; Initial Catalog=" & strDB & ";User Id=user;Password=Password"
    strConnParams = "Provider=SQLOLEDB;Data Source=" & strComputer &"; Initial Catalog=" & strDB & ";Integrated Security=SSPI;" 
                    Set objConn = CreateObject("ADODB.Connection")
                    objConn.Open strConnParams

                    
    intThreshold=cdbl(intThreshold)

                    strhostname=hostname
                    'wscript.echo strhostname
                    Dim strSQLQuery
                    

                    Dim objRS
                    Set objRS= CreateObject("ADODB.Recordset")

                    objRS.open strSQLQuery, objConn

          rowcount=cdbl(objRS.fields.item(0))


                        If Err.Number <> 0 Then
      
    oBag.AddValue "Error", Err.Description 
    oBag.AddValue "Db", strDB 
    oBag.AddValue "Query", strSQLQuery
    oBag.AddValue "Threshold", intThreshold
                    objAPI.AddItem(oBag)
    objapi.logscriptevent "SQLDBCount.vbs",1,0,"Row Count:" & rowcount &  vbcrlf & "Query: " & strSQLQuery & vbcrlf & "Errors: " & Err.Description 
      Err.Clear

       Else

    if rowcount < intThreshold Then
    State="BAD"
    else
    State="GOOD"
    End if

    oBag.AddValue "State", State
    oBag.AddValue "Rows", rowcount 
    oBag.AddValue "Error", "NO ERRORS"
    oBag.AddValue "Db", strDB 
    oBag.AddValue "Query", strSQLQuery
    oBag.AddValue "Threshold", intThreshold

                       objAPI.AddItem(oBag)
    objapi.logscriptevent "SQLDBCOunt.vbs",1,0,"Row Count:" & rowcount &  vbcrlf & "Query: " & strSQLQuery & vbcrlf & "Errors if any: " & Err.Description 
      Err.Clear
        End If

    End If
                    

                    objAPI.Return(oBag)
                    objRS.Close


    Wednesday, April 06, 2016 1:35 PM
  • I have first tried this vbscript to check whether it is connnected to database or not and it working and giving me value: 2

    Dim objConn,objRs,strSQLQuery


    strComputer="myservername"
    strDB="master"
    strSQLQuery="Select Count (dbid) from [sys].[sysprocesses] where DB_NAME  (dbid)='Tfs_TFSTest' GROUP by dbid"

    Set objConn=CreateObject("ADODB.Connection")

    strConnParams = "Provider=SQLOLEDB;Data Source=" & strComputer &"; Initial Catalog=" & strDB & ";Integrated Security=SSPI;" 

    objConn.Open strConnParams

     Set objRS= CreateObject("ADODB.Recordset")
     
     objRS.open strSQLQuery, objConn
     
     rowcount=cdbl(objRS.fields.item(0))
     
     msgbox(rowcount)

    Wednesday, April 06, 2016 1:38 PM
  • My Script Should Work as Charm.. You need to override it to the Server where you want to MOnitor and Enable it. You do not have to enable it for All the servers.

    Please let me know if it is creating alerts.


    Thanks, Goutam

    Wednesday, April 06, 2016 2:36 PM
  • Hi,

    Do we have to create any run as account for this. I have create new mp and add this monitor there . And what do you mean by override to server. There is no option for Server override or do you mean override for Window server operating system.

    And in the unhealthy expression do we can use rowcount or we have to use State only.

    Can u share your screenshot how you do it stepp by step  please,

    What is your healthy and unhealthy expression and override value?



    Wednesday, April 06, 2016 2:53 PM
  • Hello,

    We do not need to create any run as account. You Need to Makesure NT_Authority/System has SysAdmin access tothe SQL Server or DB (By default it has Public Access to the DBs).

    As I looked into your screenshots, your configuration looks correct. Nothing much that is needed apart from the permission.

    For Functionality please login to any one Server, which part of Class SQL 2012 DB object and look for Event 21406 and description should have information about your Script Name and execution. If there are no Warning Event for 21406, then look for Event ID 1, Which should have error description from the script.

    Ignore Override concept for a Server, I meant to do it for a Particular DB. otherwise it would generate alert for all the DBs in Class SQL 2012 DB.


    Thanks, Goutam

    Thursday, April 07, 2016 6:46 AM
  • Hi @Goutam,

     Now my code is working fine and i used just the basic code and how can i create log event number do we have to give event number random or there is some rule.

    I just use this code to monitor without log event and how can i use log event now in this code:

    Option Explicit
    Dim objConn,objRs,strSQLQuery,strComputer,strDB,strConnParams,rowcount,State,objAPI,objBag

    strComputer="servername"
    strDB="master"
    strSQLQuery="Select Count (dbid) from [sys].[sysprocesses] where DB_NAME  (dbid)='Tfs_TFSTest' GROUP by dbid"

    Set objConn=CreateObject("ADODB.Connection")
    Set objAPI=CreateObject("MOM.ScriptAPI")
    Set objBag=objAPI.CreatePropertyBag()

    strConnParams = "Provider=SQLOLEDB;Data Source=" & strComputer &"; Initial Catalog=" & strDB & ";Integrated Security=SSPI;" 

    objConn.Open strConnParams

     Set objRS= CreateObject("ADODB.Recordset")
     
     objRS.open strSQLQuery, objConn
     
     rowcount=cdbl(objRS.fields.item(0))
     
     Call objBag.AddValue("Value",rowcount)
     
     
     If rowcount > 10 Then     
         'State ="Good"
         'msgbox(rowcount&vbcrlf& State)
         Call objBag.AddValue("Status","Healthy")

     Else
     'State="Bad"
     'msgbox(rowcount&vbcrlf& State) 
     Call objBag.AddValue("Status","Critical")
     End If
     
     Call objAPI.Return(objBag)

    I mean you have used some code in above code sample this like (highlighted below) : what does it do  and why we need it.


    $Script:API             = new-object -comObject "MOM.ScriptAPI"
    $Script:Bag             = $Script:API.CreatePropertyBag()
    $Script:LOG_ERROR       = 1
    $Script:LOG_WARNING     = 2
    $Script:LOG_INFORMATION = 4
    add-pssnapin SqlServerCmdletSnapin100
    [string]$SQLServer = "Server Name"

    $SQLString = "Your Query"

    $out = invoke-sqlcmd -Server $SQLServer -database "OperationsManager" -query $SQLString

    if ($error.Number -gt 0)
    {
    $Script:Bag.AddValue("State","Failed")
    $Script:API.Return($Bag)
    }
    Else
    {
    if($out.count -le 10 )
    {

    write-host "Query execution is successfullyyyyyyyyy"
    write-host "The count is " $out.count
    $Script:API.LogScriptEvent("TestPS1.ps1",100,$Script:LOG_INFORMATION, "The count is " + $out.count)
    $Script:Bag.AddValue("State","OK")
    $Script:Bag.AddValue("ret","The count is "+$out.count)
    $Script:API.AddItem($Script:Bag)
    }
    else
    {

    write-host "Query execution is successfull"
    write-host "The count is " $out.count

    $Script:API.LogScriptEvent("TestPS1.ps1",103,$Script:LOG_INFORMATION, "The count is " + $out.count)
    $Script:Bag.AddValue("State","ERROR")
    $Script:Bag.AddValue("ret","The count is "+$out.count)
    $Script:API.AddItem($Script:Bag)
    }
    }
    remove-pssnapin SqlServerCmdletSnapin100
    $Script:API.Return($Script:Bag)

    Once again thank you very very much!!




    Friday, April 08, 2016 12:18 PM
  • You can't use powershell in a 2-state script monitor. You'll have to re-write it in VBS.

    &quot;Fear disturbs your concentration&quot;

    Friday, April 08, 2016 4:01 PM
  • well actually in 2012r2 I can use powershell in a 2 state monitor without issue. If you cast your mind back you remember not being able to previously, but I can anytime I want without modifying xml or using authoring tools.
    Monday, May 08, 2017 4:17 AM