Jump to content

Monitoring Service Manager Subscriptions


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

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.

image.thumb.png.84cfb1c89ac1719bfe29be50c52a39b6.png

 

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

Link to comment
Share on other sites

@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

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)

Administration___Application_Subscriptions.jpg

 

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

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