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


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.



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