Join on field h_itsm_servicesubscription.h_fk_serviceid

[mojahidm]

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

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

 

 

 

[Martyn Houghton]

@mojahidm

Looks like you need to use h_sys_org_contacts to link the organisation and contact.

Cheers

Martyn

 

[mojahidm]

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

 

 

[Martyn Houghton]

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

Cheers

Martyn

[mojahidm]

Thank you @Martyn Houghton

I've tried to have another go at this, but no data was returned when I ran the SQL query. I managed to narrow down the issue to the h_sm_catalog_subs_exclusions.h_catalog_id

I jigged the SQL I had to try to figure out if the h_sm_catalog_subs_exclusions.h_catalog_id matches up to the  h_itsm_service_catalog.h_id, but it doesn't appear to

SELECT
    hsc.h_firstname, hsc.h_pk_id, hsc.h_email_1, hisc.h_id, hisc.h_catalog_title, hiss.h_fk_subscriberid, hscse.h_subscriber_id, hscse.h_catalog_id
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
JOIN
    h_sm_catalog_subs_exclusions hscse ON hsc.h_pk_id = hscse.h_subscriber_id
    and hscse.h_subscriber_type = 'Contact'
    ORDER BY hsc.h_firstname

The hisc.h_id was different to the hscse.h_cataglog_id

Side Note: The hsc.h_pk_id, hiss.h_fk_subscriberid and hscse.h_subscriber_id all match up - so this part is good :-)

 

[HHH]

There is one hscse.h_catalog_id per language in h_itsm_service_catalog so you'd probaby want to limit yourself to those where hisc.h_request_catalog_id = hisc.h_id