8 Replies Latest reply on Oct 9, 2014 2:58 PM by gtalton

    Need to do a select on a GUID datatype using SWIS .. not having much success

    gtalton

      I am doing some integration work with our back end ticketing system for our support teams.

       

      This integration step will implement ticket auto-closure .. so that when an advanced alert is cleared .. then the Solarwinds server will signal to corresponding ticketing system to close the associated ticket.

       

      I already have the first part of this working using the Powershell examples of SWIS using the AddNote method on the Orion.AlertStatus object to stuff data about the ticket receipt from the ticketing system.

       

      So now when a ticket is opened I now have a record in Solarwinds of what the corresponding ticket in my ticketing system is.  All good .. now the difficult part.

       

      The SELECT statement to go back into the Orion.AlertStatus and get the receipt out of the alert would be :

       

      SELECT Notes FROM Orion.AlertStatus WHERE ObjectType = 'Node' AND ActiveObject = 9 AND AlertDefID = '68C0553A-1303-41BA-924F-07D1C4CE5A98'

       

      Except, this doesn't work if I add the "AND AlertDefID = '68C0553A-1303-41BA-924F-07D1C4CE5A98'" .. and I know why ... because the PK Field AlertDefID is not a string but in fact a System.Guid datatype.

       

      How do I do a SWIS select statement and search on this non-string datatype?

       

      I do have the option of brute forcing this and simply doing the --- WHERE ObjectType = 'Node' AND ActiveObject = 9   --- and then just walk though the result set that comes back, but I'd prefer to do this work with TSQL/SWIS if possible.

       

      Does anyone have any examples of working in the confines of SWIS with the System.Guid datatype?

       

      Thanks for any help,

       

      Gerald

        • Re: Need to do a select on a GUID datatype using SWIS .. not having much success
          tdanner

          Nothing special should be required. It should be able to compare the string to the GUID without issue. Your query works fine for me when I change the actual guid and nodeID to reflect an actual active alert in my test system.

           

          What are you seeing? An error from SWIS? No result when you expect a result?

           

          Also, what product/version are you using?

          • Re: Need to do a select on a GUID datatype using SWIS .. not having much success
            RomeoG

            This may be over my head, but I've been playing with SWQL studio lately and you've piqued my curiosity. What is that specific alert you are using in your example?

             

            When I run a SWQL query filtering only on the AlertDefID string, I do get matches:

            SELECT Notes,AlertDefID,ActiveObject,ObjectType from Orion.AlertStatus

            WHERE AlertDefID = '3B7A0991-DF61-417C-AA3E-393325977CD6'

             

            I get 6 matches for that query, BUT I noticed the object type for my test AlertDefID is Interface.

            • Re: Need to do a select on a GUID datatype using SWIS .. not having much success
              gtalton

              So here's the other data point .. I am really simply just trying to get my lame Powershell script below to do this query and this seems to always fail if I try to do the select using the UUID in the WHERE clause:

               

              ------------------------- LAME NEWBIE POWERSHELL SCRIPT ------------------------

              Add-PSSnapin SwisSnapin

               

              $alertDefId, $nodeid, $objectName = $args

               

              Write-Host "alertDefId is : " $alertDefId

              Write-Host "nodeid is : " $nodeid

              Write-Host "objectType is : " $objectType

               

              $swis = Connect-Swis -UserName "admin" -Password "" –Hostname localhost

               

              $queryString = "SELECT Notes FROM Orion.AlertStatus WHERE AlertDefID='$alertDefId' AND ActiveObject = $nodeid  AND AlertDefID = '$objectType'"

               

              Write-Host $queryString

               

              $invokeRslt = Get-SwisData $swis $queryString

               

              Write-Host $invokeRslt

              ------------------------- LAME NEWBIE POWERSHELL SCRIPT ------------------------

               

              Running this in the Powershell ISE I get this:

               

              ------------------------- LAME NEWBIE POWERSHELL TEST RUN -----------------------------

              ______________________________________________________________________________________________________________________________________________________

              PS C:\Users\Administrator\Documents> .\findRForceTicketId.ps1 68C0553A-1303-41BA-924F-07D1C4CE5A98 9 Node

              Add-PSSnapin : Cannot add Windows PowerShell snap-in SwisSnapin because it is already added. Verify the name of the snap-in and try again.

              At C:\Users\Administrator\Documents\findRForceTicketId.ps1:1 char:13

              + Add-PSSnapin <<<<  SwisSnapin

                  + CategoryInfo          : InvalidArgument: (SwisSnapin:String) [Add-PSSnapin], PSArgumentException

                  + FullyQualifiedErrorId : AddPSSnapInRead,Microsoft.PowerShell.Commands.AddPSSnapinCommand

               

              alertDefId is :  68C0553A-1303-41BA-924F-07D1C4CE5A98

              nodeid is :  9

              objectType is :

              SELECT Notes FROM Orion.AlertStatus WHERE AlertDefID='68C0553A-1303-41BA-924F-07D1C4CE5A98' AND ActiveObject = 9  AND AlertDefID = ''

              Get-SwisData : The communication object, System.ServiceModel.Security.SecuritySessionClientSettings`1+ClientSecurityDuplexSessionChannel[System.ServiceMo

              del.Channels.IDuplexSessionChannel], cannot be used for communication because it is in the Faulted state.

              At C:\Users\Administrator\Documents\findRForceTicketId.ps1:36 char:27

              + $invokeRslt = Get-SwisData <<<<  $swis $queryString

                  + CategoryInfo          : NotSpecified: (:) [Get-SwisData], CommunicationObjectFaultedException

                  + FullyQualifiedErrorId : System.ServiceModel.CommunicationObjectFaultedException,SwisPowerShell.GetSwisData

              ------------------------- LAME NEWBIE POWERSHELL TEST RUN -----------------------------

               

              Again thanks for the help.

               

              Gerald

                • Re: Re: Need to do a select on a GUID datatype using SWIS .. not having much success
                  tdanner

                  You have a typo in your script. You have this:

                   

                  $queryString = "SELECT Notes FROM Orion.AlertStatus WHERE AlertDefID='$alertDefId' AND ActiveObject = $nodeid  AND AlertDefID = '$objectType'"

                   

                  When what you meant to say was this:

                   

                  $queryString = "SELECT Notes FROM Orion.AlertStatus WHERE AlertDefID='$alertDefId' AND ActiveObject = $nodeid  AND ObjectType= '$objectType'"

                   

                  Also, the line in your script for unpacking the command line arguments has a typo. You have this:

                   

                  $alertDefId, $nodeid, $objectName = $args

                   

                  But what you meant to say was this:

                   

                  $alertDefId, $nodeid, $objectType = $args

                • Re: Need to do a select on a GUID datatype using SWIS .. not having much success
                  gtalton

                  Yep, I noticed that right before you posted, rather embarrassing but fixed .. I am still getting these errors however and that's even after a restart

                   

                  cannot be used for communication because it is in the Faulted state

                  • Re: Need to do a select on a GUID datatype using SWIS .. not having much success
                    gtalton

                    Here's the latest test run ... where I print the Select before I run it:

                     

                    PS C:\Users\Administrator\Documents> .\findRForceTicketId.ps1 68C0553A-1303-41BA-924F-07D1C4CE5A98 9 Node

                    alertDefId is :  68C0553A-1303-41BA-924F-07D1C4CE5A98

                    nodeid is :  9

                    objectType is :  Node

                    SELECT Notes FROM Orion.AlertStatus WHERE AlertDefID='68C0553A-1303-41BA-924F-07D1C4CE5A98' AND ActiveObject = 9  AND AlertDefID = 'Node'

                    Get-SwisData : There was no endpoint listening at net.tcp://localhost:17777/SolarWinds/InformationService/v3/Orion/ssl that could accept the message. Thi

                    s is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details.

                    At C:\Users\Administrator\Documents\findRForceTicketId.ps1:34 char:27

                    + $invokeRslt = Get-SwisData <<<<  $swis $queryString

                        + CategoryInfo          : NotSpecified: (:) [Get-SwisData], EndpointNotFoundException

                        + FullyQualifiedErrorId : System.ServiceModel.EndpointNotFoundException,SwisPowerShell.GetSwisData

                    • Re: Need to do a select on a GUID datatype using SWIS .. not having much success
                      gtalton

                      This seems to be working now .. thanks tdanner for all your help.