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
