Service Manager Reports - How to exlcude certain catalog items from "all open incidents" report

Hi,

We have a custom designed workflow used for certain requests which started with SDXXXXX and realated with CXXXXX records.

On the dashboard these SD's records  count as normal users interactions.

I would like to remove/exclude requests comes from certain Catalog Items on dashboard report.

I was trying something like this; 

(open~="Closed") and $L.file.displayName ~="Yazılım Değişiklik Talebi"

but its not working. 

 

Yazılım Değişiklik Talebi = is the Catalog item which we dont want to see in the all open incidents statistics.

Any help appreciated. 

  • Are you certain that the "Yazılım Değişiklik Talebi" value is populated to the Incident?

    If it is already populating from the cart item to the Incident, then this is a simple matter of excluding that Incident field value from the report. Can you see that field value on the IM ticket?

    If it is not populating from the cart item to the Incident, then you will need to make it populate. This could be done several ways. What version of SM are you running?

  • SM version is 9.41. 

    I was looking like this before. Would you help about how to check related value especially which tables  should I check. and how populate if its missing.

  • Thank @AChandler for clarifying the requirement. And from 9.32, it's not necessary to populate the value from one table to another. As cross table query is enabled in 9.32.

    Regarding the requirement, I did a test, just put the query like below in your reporting defination, it shall work.

    incidents;incident.id;svcCartItem;sdID;svcCartItem;item.description="XXXX"

    the query means, incident.incident.id = svcCartItem.sdID and svcCartItem.item.description="XXXX".

    The example of OOTB system to query all the interactions which order "New Employee Bundle":The report looks like below:

     

    It's using the cross table query function introduced in 9.32, you can find more details at:

    http://community.hpe.com/t5/IT-Service-Management/HP-Service-Manager-SM-9-32-How-to-improve-query-or-table-join/ba-p/6217221#.Vzl2c5F96hc

    Official online help which includes many examples:

    https://s3.amazonaws.com/smhelpcenter/smhelp941/codeless/index.html#database/reference/cross_table_examples.htm?TocPath=System%20Administration|Database%20Administration|Data%20persistence|Queries|Cross-table%20join%20query%20improvements|Cross-table%20join%20query%20examples%20and%20use%20cases|_____0

    Other 3rd party examples:

    http://www.antavis.com/2014/08/05/cross-table-queries-in-hp-servicemanager/

    https://ernestodisanto.wordpress.com/2015/04/03/hpsm-query-on-multiple-tables-using-javascript/

    Thanks,

    Ling-Yan

  • My first example is find the interactions includes specific cart items. And exclude the interactions includes specific cart items, it's not possible in Out-of-Box by one single query. As one interaction can link to many svcCartItem.

    To exclude values, the right database query is like below:

    SELECT *
    FROM incidentsm1 i1
    WHERE i1.incident_id NOT IN
    (SELECT sdid
    FROM svcCartItemm1 s1
    WHERE S1.ITEM_DESCRIPTION = 'New Employee Bundle');

    instead of 

    SELECT *
    FROM incidentsm1 i1, svcCartItemm1 s1
    WHERE I1.INCIDENT_ID = S1.SDID
    AND S1.ITEM_DESCRIPTION != 'New Employee Bundle';

    I need more time to find a solution.

    Thanks,

    Ling-Yan

  • Thank you Ling-yan. I'll read the links you've provided. 

    btw, If I'm right related cart name field is not available at the incidents table. Actually I really dont know that cart name populated at where. I'm gonna search it a bit more. But still I couldnt find a way to filter statistics of dashboard report. 

  • Verified Answer

    Yes, you're right that the "incidents" table does not contain name of the catalog item.

    The problem is, that when viewing the ticket, you are able to see the catalog item. But it's retrieved dynamically from another table and what comes to Reports or views, the display name of catalog item is not available in "incidents" table.

    SM's "shopping cart system" is powerful, but complicated. There are two principal tables: svcCart and svcCartItem. svcCart holds the basic info of the shopping cart and svcCartItem the details that the cart holds. So basically every SD ticket created by an catalog order creates one svcCart line to table and 1-n lines to svcCartItem table. (Please use Database manager to inspect these tables closely.)

    Only link from Interaction to the shopping cart is by id called "cartId". So Interaction knows which cart it uses, cart knows what items it holds.

    So a better approach would be following:
    - check whether the cart referenced in the Interaction contains the item you want to exclude

    - mark a field in the "incidents" table true/false based on that information

    - use that field in your Reports to exclude unwanted Interactions

    This means that you need to write some JavaScript code....

  • Even this thread was marked as solved, I'd like to share another way to (maybe) achieve a same result without DB modification. Side effect is CPU load and probably slower reports, but I feel the trick suits to situations where the subset of checked records won't be a huge one.

    I found out that it's possible to create a query that includes a call to JavaScript function. For example, at least version SM 9.41 contains a report named "Update from customer - Problem".

    Its query is:

    (rcStatus~="Closed") and (assignment isin $lo.pm.assignments) and id isin jscall("viewFunctions.ActivityCustomerUpdated","activityproblem")

    Basically "isin" operator checks whether a certain ID is in an array of IDs returned by a JS function. Please see a JS function in SM for more details to see how the subset of IDs to be checked against is formed.

    Maybe the usage of jscall in queries is common knowledge, but wanted to share this, anyway.