SDM 14.1.x (various customers)
Xtraction Version 2016.4.0111.01
I am having an issue with filtering lists that contain SLA fields when the tickets are inactive. If I only use 'SLA Violation' or "SLA Violation (Yes/No)' it works, but when I try to add the SLA Event Name or SLA Event Status, it does not return the data. This is needed to report on which SLA event a ticket violated even if it is closed, e.g., "All Priority 3 tickets which violated the 12 Hour Resolution SLA last month".
Steps to reproduce:
Select OK to run the report.
is this working as designed or do I need to modify the model? Any help would be appreciated.
A Support case is opened for this. For now it seems when some fields are added to the DISPLAY panel the app
automatically adds some extra where clause to the query assuming it is for open tickets and as a result, no tickets return. We will work on this and later update this thread.
CA has opened a support case with Ivanti. In the meantime, Gregg has looked as this and the workaround is to remove the table-level filter in the Data Model and this will resolve the restrictions.
(image and quoted text from Ivanti)
"Open the property dialog for the SLA_EVENT_ATTRIBUTES table and delete the AUTOMATIC filter entry on the Filters tab. This filter logic should have been added as an additional join condition, instead of as an automatic filter condition. So, simply deleting it may cause your records to reappear, but also event records that you don't want to see. I would need to investigate what that filter is trying to accomplish to determine if it is appropriate to move it to the join conditions instead of just outright deleting."
There could be issues with changing this default behavior, but so far my customers have not mentioned any.
I will mark this as the answer until we get an update from support.
Did you get a response from your post on the Ivanti Community Forum?
We might be better off opening a CA Support ticket so that we can open a ticket with Xtraction Support to confirm if this is working as designed or not.
Please do not mark a question as "Assumed Answered" without contacting the OP - especially if the question has been up for less than a week!
Chi and I were able to determine the actual queries being sent. Adding one of fields as a column to return in step 2 above modifies the WHERE clause in such a way that it only matches event status associated with active tickets.
For example, after adding one of the columns and then searching for active requests, this is the WHERE clause:
WHERE ((CASE REQUEST.ACTIVE_FLAG WHEN 1 THEN 'Yes' ELSE 'No' END = 'Yes')) AND (REQUEST.TYPE = 'R') AND (SLA_MACRO.SYM LIKE 'Set SLA Violation%') AND (SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 2 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 3 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 12 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 13)
and changing this to searching for inactive requests results in:
WHERE ((CASE REQUEST.ACTIVE_FLAG WHEN 1 THEN 'Yes' ELSE 'No' END = 'No')) AND (REQUEST.TYPE = 'R') AND (SLA_MACRO.SYM LIKE 'Set SLA Violation%') AND (SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 2 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 3 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 12 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 13)
However, the possible event status for inactive tickets are 0 ( Cancelled ) or 1 ( Closed) and these are not included. This explains why no results are returned.
Note that this is not something that can be changed by the user because the application is adding the WHERE clause when the column(s) are added and is NOT part of the filter as defined by the user. However, when we changed this in SQL Management Studio as a new query to:
WHERE ((CASE REQUEST.ACTIVE_FLAG WHEN 1 THEN 'Yes' ELSE 'No' END = 'No')) AND (REQUEST.TYPE = 'R') AND (SLA_MACRO.SYM LIKE 'Set SLA Violation%') AND (SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 0 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 1 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 2 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 3 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 12 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 13)
or when we eliminated the check for event status altogether:
WHERE ((CASE REQUEST.ACTIVE_FLAG WHEN 1 THEN 'Yes' ELSE 'No' END = 'No')) AND (REQUEST.TYPE = 'R') AND (SLA_MACRO.SYM LIKE 'Set SLA Violation%')
Then this returns the correct results and matches the ones from SDM.
Chi is working on duplicating this in his environments to document the case for the vendor to verify but I think we have the cause. Now we need a solution.
As a workaround, I can create a new report in Report Designer using SQL on behalf of the user but this is not the expectation of how the product should work.
I have had some responses asking for additional background but nothing suggesting a cause.
I did open a CA Support case and Chi and I worked this yesterday. We isolated the queries actually sent to MS SQL and, as he explains below, adding the fields as columns to the report causes the query to add an incomplete WHERE clause. This results in it only matching data when the event status matches those possible for active tickets only.
I am updating the posts here and there with this while Chi replicates it internally.
I've posted this at the same time to the Ivanti Xtraction community in case Gregg or others already know the issue. If there are no updates from here or there, then I will open a support case.
Hi JW - We try to keep questions moving along. There maybe times that it happens too quickly. Please know a question can always be reopened for further input. Regards and thank you for your feedback! Chris
Thanks for the update Jeff.
Please keep us posted once a solution has been identified.
It almost seems as if its hard coded to only grab active tickets, but I am not sure how they did this. Gregg would know best - not sure if he follows this community anymore but he will def. answer you on the Ivanti community for sure