3 Replies Latest reply on Jul 22, 2013 4:07 PM by tdanner

    Is it possible to use queryxml like a trigger from Advance Alerts?

    Korey Hubbard

      I am trying to replicate an existing advance alert trigger using queryxml using the c# sample provided in the SDK.

      Ultimately I am wanting to query Orion database and store the results in a local database to be used by another program to create tickets.

       

      This code works when querying only one table:

       

      void Main()



      const string hostname = "*********";


      const string username = "***********";


      const string password = "***********";



      ServicePointManager.ServerCertificateValidationCallback = (sender, certificate, chain, errors) => true;



      var client = new InformationServiceClient("BasicHttpBinding_InformationService",


      string.Format("https://{0}:17778/SolarWinds/InformationService/OrionBasic", hostname));






      var result = client.QueryXml("SELECT TOP 1 A.AlertDefID, A.ActiveObject, A.ObjectType, A.Acknowledged, A.AcknowledgedBy, A.AcknowledgedTime, A.TriggerTimeOffset, A.TriggerTimeStamp FROM Orion.AlertStatus A WHERE A.Acknowledged = 0 RETURN XML AUTO", null);


      //var result = client.QueryXml("SELECT I.FullName, ic.ASSYST_CSG, ic.Assyst_short_code, ic.InterfaceID, I.NodeID, N.DNS, N.IPAddress, N.IPAddressType, N.MachineType, N.Status AS Node_Status, N.StatusDescription AS Node_Description, I.Status As Interface_Status, I.StatusDescription AS Interface_Description, nc.PassivePoll FROM Orion.NPM.InterfacesCustomProperties ic INNER JOIN Orion.NPM.Interfaces I ON (ic.InterfaceID = I.InterfaceID) INNER JOIN Orion.Nodes N ON (I.NodeID = N.NodeID) INNER JOIN Orion.NodesCustomProperties nc ON (nc.NodeID = N.NodeID) WHERE  (  (I.Status <> '1') AND   (I.Status <> '4') AND   (I.Status <> '9') AND  (I.InterfaceName <> 'VPN') AND  (ic.Assyst_Short_Code <> 'NONE') AND  (nc.PassivePoll <> 'YES') AND  (ic.ASSYST_CSG <> 'CORE')) ");


      var element = result.XPathSelectElement("//*[local-name()='A']");

       

      var alert = new AlertInfo



      "AlertDefID").Value,


      "ActiveObject").Value,


      "ObjectType").Value,


      "Acknowledged").Value,


      "AcknowledgedBy").Value,


      "AcknowledgedTime").Value,


      "TriggerTimeOffset").Value,


      "TriggerTimeStamp").Value,

       

      var alerts = new[] {alert};

      var dcs = new DataContractSerializer(alerts.GetType());

      var doc = new XmlDocument();

      using (var writer = doc.CreateNavigator().AppendChild())

      /*

      //Console.Write(result2);


      Console.WriteLine("Alert Def ID: " + alert.AlertDefID);


      Console.WriteLine("Active Object: " + alert.ActiveObject);


      Console.WriteLine("Object Type: " + alert.ObjectType);


      Console.WriteLine("Acknowledged: " + alert.Acknowledged);


      Console.WriteLine("Acknowleged by: " + alert.AcknowledgedBy);


      Console.WriteLine("AcknowledgedTime: " + alert.AcknowledgedTime);


      Console.WriteLine("TriggerTimeOffset: " + alert.TriggerTimeOffset);


      Console.WriteLine("TriggerTimeStamp: " + alert.TriggerTimeStamp);



      Console.ReadLine();




      DataContract(Name = "AlertInfo", Namespace = "http://schemas.solarwinds.com/2008/Orion")]


      public class AlertInfo



      DataMember(IsRequired = true, Order = 1)]


      public string AlertDefID { get; set; }



      DataMember(IsRequired = true, Order = 2)]


      public string ActiveObject { get; set; }



      DataMember(IsRequired = true, Order = 3)]


      public string ObjectType { get; set; }



      DataMember(IsRequired = true, Order = 4)]


      public string Acknowledged { get; set; }



      DataMember(IsRequired = true, Order = 5)]


      public string AcknowledgedBy { get; set; }



      DataMember(IsRequired = true, Order = 6)]


      public string AcknowledgedTime { get; set; }



      DataMember(IsRequired = true, Order = 7)]


      public string TriggerTimeOffset { get; set; }



      DataMember(IsRequired = true, Order = 8)]


      public string TriggerTimeStamp { get; set; }

       

       

       

      static IEnumerable<XElement> ElementsAnyNS(this XElement source, string localName)



      return source.Elements().Where(e => e.Name.LocalName == localName);




      public static XElement ElementAnyNS(this XElement source, string localName)



      return source.Elements().FirstOrDefault(e => e.Name.LocalName == localName);


       

       

      However I am wanting to replace the current Query with :

       

      SELECT I.FullName, ic.ASSYST_CSG, ic.Assyst_short_code, ic.InterfaceID, I.NodeID, N.DNS, N.IPAddress, N.IPAddressType, N.MachineType, N.Status AS Node_Status, N.StatusDescription AS Node_Description, I.Status As Interface_Status, I.StatusDescription AS Interface_Description, nc.PassivePoll

      FROM Orion.NPM.InterfacesCustomProperties ic

      INNER JOIN Orion.NPM.Interfaces I

      ON (ic.InterfaceID = I.InterfaceID)

      INNER JOIN Orion.Nodes N

      ON (I.NodeID = N.NodeID)

      INNER JOIN Orion.NodesCustomProperties nc

      ON (nc.NodeID = N.NodeID)

      WHERE 

      (

        (I.Status <> '1') AND

        (I.Status <> '4') AND

        (I.Status <> '9') AND

        (I.InterfaceName <> 'VPN') AND

        (ic.Assyst_Short_Code <> 'NONE') AND

        (nc.PassivePoll <> 'YES') AND

        (ic.ASSYST_CSG <> 'CORE')

      )

       

      and when I do, the code does not work, is it possible to replicate the advance alert manager using the SDK to suit the required query (above)?

       

      Thank you