Highlighted
New Member.
1413 views

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

Jump to solution

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. 

0 Likes
1 Solution

Accepted Solutions
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Service Manager Reports - How to exlcude certain catalog items from "all open incidents&

Jump to solution

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....

---
Moving on, this account is no longer active. Best regards, Kelalek
- So Long, and Thanks for All the Fish

View solution in original post

7 Replies
Highlighted
Outstanding Contributor.. Outstanding Contributor..
Outstanding Contributor..

Re: Service Manager Reports - How to exlcude certain catalog items from "all open incidents&quo

Jump to solution

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?

0 Likes
Highlighted
New Member.

Re: Service Manager Reports - How to exlcude certain catalog items from "all open incidents&

Jump to solution

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.

sm1.jpg

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: Service Manager Reports - How to exlcude certain catalog items from "all open incidents&

Jump to solution

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":SNAG- 5-16-2016 3.23.20 PM MENGL7.pngThe report looks like below:

 

SNAG- 5-16-2016 3.24.16 PM MENGL7.png

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%3FTocPath%3DSystem%2520Administration%7CDatabase%2520Administration%7CData%2520persistence%7CQueries%7CCross-table%2520join%2520query%2520improvements%7CCross-table%2520join%2520query%2520examples%2520and%2520use%2520cases%7C_____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

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: Service Manager Reports - How to exlcude certain catalog items from "all open incidents&

Jump to solution

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

0 Likes
Highlighted
New Member.

Re: Service Manager Reports - How to exlcude certain catalog items from "all open incidents&

Jump to solution

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. 

0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Service Manager Reports - How to exlcude certain catalog items from "all open incidents&

Jump to solution

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....

---
Moving on, this account is no longer active. Best regards, Kelalek
- So Long, and Thanks for All the Fish

View solution in original post

Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Service Manager Reports - How to exlcude certain catalog items from "all open incidents&quo

Jump to solution

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.

---
Moving on, this account is no longer active. Best regards, Kelalek
- So Long, and Thanks for All the Fish
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.