Jump to content

Monitoring Service Manager Subscriptions

Martyn Houghton

Recommended Posts

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
a.h_class=1 and
a.h_account_status <=1 and 


  • 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?



Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 weeks later...

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:


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


  • Like 1
Link to comment
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...