CA Service Management

Expand all | Collapse all

Xtraction - SLA data on inactive tickets

  • 1.  Xtraction - SLA data on inactive tickets

    Posted Sep 19, 2017 11:18 AM

    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:

     

    1. Create a List dashboard from the Request data source.

     

     

     

    1. Add the following columns to the defaults:  SLA Violation (Yes/No), SLA Event Name, SLA Violation, SLA Event Status

     

     

    1. Check that there are no filters in place.

     

     

    Select OK to run the report.

     

    1. Results:

     

     

    1. Change filter to:  Active = No

     

     

     

    Select OK to run the report.

     

    1. Results:

     

     

    1. But in Service Desk, if I search for all inactive Requests with SLA violations, it returns:

     

     

     

    1. Sample detail:

     

     

     

    1. Looking at the definition of the SLA Event Name and SLA Event Status in the Data Model Editor, I do not see why this would cause the results in Xtraction to be filtered.

     

     

    is this working as designed or do I need to modify the model?  Any help would be appreciated.

     

    J.W.



  • 2.  Re: Xtraction - SLA data on inactive tickets

    Broadcom Employee
    Posted Sep 19, 2017 11:23 AM

    Jeff..........

     

    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.



  • 3.  Re: Xtraction - SLA data on inactive tickets

    Posted Sep 19, 2017 11:40 AM

    Thanks, Paul.

     

    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. 

     

    J.W.



  • 4.  Re: Xtraction - SLA data on inactive tickets

    Posted Sep 19, 2017 01:14 PM

    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

    Jon



  • 5.  Re: Xtraction - SLA data on inactive tickets

    Posted Sep 25, 2017 01:29 PM

    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!



  • 6.  Re: Xtraction - SLA data on inactive tickets

     
    Posted Sep 25, 2017 01:39 PM

    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



  • 7.  Re: Xtraction - SLA data on inactive tickets

    Broadcom Employee
    Posted Sep 25, 2017 03:02 PM

    Jeff.........

     

    Did you get a response from your post on the Ivanti Community Forum?



  • 8.  Re: Xtraction - SLA data on inactive tickets

    Posted Sep 26, 2017 07:29 AM

    Paul, 

     

    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.

     

    J.W.



  • 9.  Re: Xtraction - SLA data on inactive tickets

    Broadcom Employee
    Posted Sep 25, 2017 05:28 PM

    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.



  • 10.  Re: Xtraction - SLA data on inactive tickets

    Posted Sep 26, 2017 08:46 AM

    UPDATE:

     

    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.

     

    J.W.



  • 11.  Re: Xtraction - SLA data on inactive tickets

    Broadcom Employee
    Posted Sep 26, 2017 09:42 AM

    Thanks for the update Jeff.

     

    Please keep us posted once a solution has been identified.



  • 12.  Re: Xtraction - SLA data on inactive tickets
    Best Answer

    Posted Oct 03, 2017 08:54 AM

    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.

     

    J.W.