query Monitoring Service Manager Subscriptions

[Martyn Houghton]

I am trying to determine a way to monitor the number of Service Manage Subscriptions using a SQL Measure, so that I can keep a track of our subscription usage and also allow me to predict/plan for any required increases.

I have got as far as below, but still are returning more rows than the subscription count, so I must be missing or have a invalid criteria.

SELECT distinct a.h_user_id FROM h_sys_accounts a
left join h_sys_accounts_roles rg on a.h_user_id=rg.h_user_id
left join h_sys_roles r on rg.h_role=r.h_role
where 
a.h_class=1 and
a.h_account_status <=1 and 
r.h_application='com.hornbill.servicemanager'

Checking

• Account is a Full User
• Account is Active or Suspended
• Has a Group membership which references the Service Manager application

Any idea what I am missing?

Cheers

Martyn

[AndyGilly]

@Martyn Houghton

a mechanism for knowing who in the full user subscription pool is a service manager agent is something that would help us also

+1 from us

Andy

[nasimg]

We use a report (with some custom fields to record extra info) -  for telling us status, which teams, last logon time.

 

We use this it recharge, also useful when trying to swap a subscription.

[Martyn Houghton]

@nasimg

Thanks, we do a similar process to view user's last login. However in the case of the measure I trying to record/track the number of licenses assigned over time.

Hopefully someone at Hornbill will be able to advise on the exact criteria used by the subscription check.

Cheers

Marty

[Martyn Houghton]

@Bob Dickinson

Would this be something you would be able to advise on or should I raise a support incident?

Cheers

Martyn

[Bob Dickinson]

Hi @Martyn Houghton

Sorry I missed this. 
If I understand correctly, I think you want to report on the information thats stored in the "Application Subscriptions" section of service manager (for example, I have 3 subscribers out of 100 set here)

 

The SQL you have created so far is pretty much just based on who has a Service Manager specifc role. 

If you would like to perform any reporting on the above you need to include the following table in your SQL:

h_sys_app_users

This contains:

• A unique ID (h_id)
• The application it relates to e.g. com.hornbill.servicemanager (h_application)
• The User ID of the user subscribed (h_user_id)

 

Hopefully this can be included into your statement to extract the info you require

Kind Regards

Bob

[Martyn Houghton]

@Bob Dickinson

Thanks. That makes it a lot simpler. I can put a SQL Measure to track our subscription allocation based on the count of the table with a selection on the h_application field.

Cheers

Martyn

[Martyn Houghton]

@Bob Dickinson

This would work for service manager, customer manager etc. How is the base collaboration subscriptions handled?

Cheers

Martyn