Jump to content
mojahidm

Join on field h_itsm_servicesubscription.h_fk_serviceid

Recommended Posts

Hi,

I'm trying to find a way to pull out all contacts/organizations/etc... who have visibility to a particular catalog item

I saw this on the forums:

SELECT
    h_sys_contact.h_firstname, 
    h_sys_contact.h_lastname, 
    h_sys_contact.h_email_1, 
    h_sys_organizations.h_organization_name, 
    h_itsm_services.h_servicename
FROM 
    h_itsm_servicesubscriptions 
JOIN 
    h_container ON h_container.h_id = h_itsm_servicesubscriptions.h_fk_subscriberid
JOIN 
    h_sys_organizations ON h_sys_organizations.h_organization_id = h_container.h_type_id
JOIN
    h_itsm_services ON h_itsm_services.h_pk_serviceid = h_itsm_servicesubscriptions.h_fk_serviceid 
JOIN 
    h_sys_contact ON h_sys_contact.h_organization_id = h_sys_organizations.h_organization_id
WHERE 
    h_itsm_servicesubscriptions.h_fk_serviceid = 113

Which is almost what I want, but I need it at the catalog item level

I've tried joining table h_itsm_service_catalog, but I'm getting contacts that I'm not expecting

So far I have this:

SELECT
    h_sys_contact.h_firstname, 
    h_sys_contact.h_lastname, 
    h_sys_contact.h_email_1, 
    h_sys_organizations.h_organization_name, 
    h_itsm_services.h_servicename,
    hisc.h_id, hisc.h_catalog_title,
    h_sys_contact.h_pk_id, h_itsm_servicesubscriptions.h_pk_id, h_itsm_servicesubscriptions.h_fk_subscriberid
FROM 
    h_itsm_servicesubscriptions 
JOIN 
    h_container ON h_container.h_id = h_itsm_servicesubscriptions.h_fk_subscriberid
JOIN 
    h_sys_organizations ON h_sys_organizations.h_organization_id = h_container.h_type_id
JOIN
    h_itsm_services ON h_itsm_services.h_pk_serviceid = h_itsm_servicesubscriptions.h_fk_serviceid 
JOIN 
    h_sys_contact ON h_sys_contact.h_organization_id = h_sys_organizations.h_organization_id
JOIN
    h_itsm_service_catalog hisc ON hisc.h_service_id = h_itsm_services.h_pk_serviceid
    and hisc.h_catalog_title = 'Manage Users and Terminals'
WHERE 
    h_itsm_servicesubscriptions.h_fk_serviceid = 28
    and h_request_type = 'Service Request'

I tried to trace where the issue might be. I found that within the h_itsm_servicesubscriptions table

image.png.8ebd1a108d40815511ac1ee12755d233.png

the above h_pk_id has h_fk_serviceid of 28, but it shouldn't...

Could I confirm the following:

h_pk_id = h_sys_contacts.h_pk_id
h_fk_serviceid = h_itsm_services.h_pk_serviceid

 

 

 

Share this post


Link to post
Share on other sites

Thank you @Martyn Houghton

I decided to forgo the organization info and strip it to the bare necessities to make it easier

I've now got the below:

SELECT
    hsc.h_firstname, hsc.h_pk_id, hsc.h_email_1, hisc.h_catalog_title
FROM 
    h_itsm_servicesubscriptions hiss
JOIN 
    h_sys_contact hsc ON (hsc.h_pk_id = hiss.h_fk_subscriberid AND hiss.h_subscribertype = 'Contact')
JOIN
    h_itsm_service_catalog hisc ON hisc.h_service_id = hiss.h_fk_serviceid
    and hisc.h_catalog_title = 'Manage Users and Terminals'
    and hiss.h_fk_serviceid = 28

Which gives me all subscribers to the serviceid 28, but I'm stuck on how to get a list of the subscribers that can view a particular CI (such as a SR CI called 'Manage Users and Terminals'

I can't figure out which table might hold the data for which contact can and cannot view a particular CI...

 

 

Share this post


Link to post
Share on other sites

@mojahidm

That's where it gets a bit harder, as unlike the service subscriptions which list those who are subscribed, the catalog subscriptions are the reverse, in that to determine if a contact has access to a Catalog Item them must be subscribed to the service and not excluded from accessing the catalog item. The latter is held in h_sm_catalog_subs_exclusions.

Therefore you will probably have to do some 'NOT IN' sub queries to check the organisation or contact is not excluded from the catalog item.

image.thumb.png.45264f4a28cf97f36f72e670fe388ef8.png

Cheers

Martyn

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...