Custom SWQL Query for Service Desk Incidents

I'm still learning how to speak SWQL and have never known how to speak SQL, so excuse the noobness of this question, but I'm trying to add fields for "created" and "updated" date/time for Service Desk incidents to my device summaries to give us a little more visibility into existing incidents while in the Orion platform. Currently, our widget is working off of the below query, but I just don't know how to add the other 2 fields to it. Hopefully one of you awesome Twack-ers can help me out.

SELECT IncidentNumber AS [Incident], ADDHOUR(-3,LastTriggerTime) AS [Triggered], IncidentUrl AS [_LinkFor_Incident], State AS Status, AssignedTo AS [Assigned To], OESI.Description
FROM Orion.ESI.AlertIncident OESI

inner join Orion.AlertObjects Object on Object.AlertObjectID=OESI.AlertObjectID

Where Object.RelatedNodeId=${NodeID} and IncidentNumber <> ''

Order By ID DESC

Find more posts tagged with

orion

swsd

SWQL

Accepted answers

All comments

stuartd

Welcome to the pain that is SWQL - SQL alike but not quite all the way

As an aside, I've been trying to get an ad-hoc SWQL study / learning group up and running but see this post for info.

As to your query, it should be fairly straightforward so long as the fields you want to add are being pulled from the same table in the DB. In your case that table is: Orion.ESI.AlertIncident

If they are, then simply add the field names in to the SELECT statement portion in an appropriate place. Bear in mind the field names need to be finished off with a comma except for the last one. So taking your above example (I'm sorry, we don't have SD so can't look for myself) it would be something like:

SELECT IncidentNumber AS [Incident], ADDHOUR(-3,LastTriggerTime) AS [Triggered], INSERTED_FIELD_ONE, IncidentUrl AS [_LinkFor_Incident], State AS Status, AssignedTo AS [Assigned To], INSERTED_FIELD_TWO, OESI.Description

If the data you need is from another table, then you will need to do a JOIN - but try the above just change the words in bold to your actual field name. To see what those are, then fire up SWQL Studio [if you haven't got this yet then I recommend it as an absolute must have], find your table, then right-click and 'Generate Select Statement'. You can choose to execute it at this stage, but all you need is the filed names.

To execute it, press F5 or the green triangle button.

HTH?

KMSigma.SWI

Noobness is a requirement. Everyone was a noob at one time or another.

If you haven't already explored the power of Navigation Properties, I'd suggest taking a look at Decreasing JOINs - the Power of Navigation Properties - Orion SDK.

Most data in the API already knows about it's connected data. For example, did you know that Orion.ESI.AlertIncident is natively connected to Orion.AlertObjects and you don't need a JOIN clause?