I need to pull the most recent Log Comment from a Service Desk incident.
It looks like I can get them all by using the Select Object operator with:
Object Type = alg
Where Clause = "call_req_id = '" + Process.persid + "'"
But I do not see any way to add an order by to the query.
Anyone know of a better way to get the most recent than manually looping through the results and comparing time stamps?
You are right, I don't think we can construct where clause with order by (example similar like sql server query).
The best possible approach is read the log comments to array and applying sorting on Timestamps.
waiting if some one have better approach for this.
CA Service Management
Afaik the list results in SDM are by default sorted following the rules defined in the backend Majic code. For ALG that should be newest first.
So I would assume the list is already sorted correctly for you. Your question was to get all Log comments, so you would still need to add type = 'LOG' to your query and get the first result in that list.
I only just now realized you are probably trying to do this in ITPAM. Apologies.
So to recap, the alg list is already sorted by most recent on top, so you should be able to use the XML parameter to your web service call to tell it to return only 1 row and that should fix your issue.
Use an XML extration operator to parse the result into dataset variables that you can use later in your process if necessary.
Thanks. This much better than the looping solution I am currently using. Are the default sort orders defined somewhere? I would hate to depend on an undefined default behavior that could change in the future without warning.
the sort default sort orders are defined on the SDM object layer. You can see these in the .maj and .mod files, and probably also when executing a bop_sinfo -pf alg command in a command prompt on the SDM server.
for alg, this sort order (newest on top) has been there since forever. Of course if your company would chose to create their own mod file to sort differently then you would have an issue indeed.
I do not have access to the sdm web services documentation right now, I wanted to verify if you can force the sort order there to make sure that is possible or not, as I don't think I've needed that much before.
You can pull the most recent log comment description as below:
select top(1)description from dbo.act_log where call_req_id='"+ Process.persid +"' AND TYPE='LOG' ORDER BY id desc;
Hope it works.