You might need to know how many licenses of Microsoft Dynamics GP are in use. Maybe you’d like to purchase more licenses while you still have a couple extra, or you suspect you can give some back, or whatever.

A happy chef

You can quickly set up a repeating job to take snapshots of your license use, with whatever detail you wish. In our case we just wanted to know how close we were to hitting our license limit.

Make a table for the usage history. Since it applies across companies, I inserted it into the DYNAMICS database. I began the table name with SSC_, our company initials, to group it with the rest of our custom tables.

CREATE TABLE dbo.SSC_LicenseHistory (
	WhenMeasured datetime NOT NULL,
	LicensesInUse int NOT NULL,
 CONSTRAINT PK_SSC_LicenseHistory PRIMARY KEY CLUSTERED 
(
	WhenMeasured ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
  ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY
) ON PRIMARY;

Then I made a SQL Server Agent job to periodically save a snapshot of the license usage.

USE msdb;
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories 
  WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB',
  @type=N'LOCAL', @name=N'[Uncategorized (Local)]';
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

END;

DECLARE @jobId BINARY(16);
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Collect GP License Trends', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Occasionally count how many GP licenses are in use for later analysis of historical trends', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'SSCORP\rkohler', @job_id = @jobId OUTPUT;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Save license count', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'insert into SSC_LicenseHistory (WhenMeasured, LicensesInUse)
select current_timestamp,count(*)
from Activity;', 
		@database_name=N'DYNAMICS', 
		@flags=0;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Save License Count Schedule', 
		@enabled=1, 
		@freq_type=8, 
		@freq_interval=62, 
		@freq_subday_type=8, 
		@freq_subday_interval=1, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20131123, 
		@active_end_date=99991231, 
		@active_start_time=61500, 
		@active_end_time=183000, 
		@schedule_uid=N'9dde8acd-762b-4369-98bb-d3406c96f23f';
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)';
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
COMMIT TRANSACTION;
GOTO EndSave;
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION;
EndSave:
;
GO

So we have a table that looks like this:

Two columns, When Measured and Licenses in Use.

You can add to this. For example, you can save the number of people using multiple licenses.

Then we report on the license usage. For our internal use we just made a simple query that returns the min, mean , and max license count from the past 30 calendar days. Your needs may differ.

create view dbo.SSC_rpt_GpLicenseUse as
select top 2000000000 -- SELECT TOP allows me to use an ORDER BY clause in the view
  cast(WhenMeasured as date) as Date,
  min(LicensesInUse) as MinLicenseCount,
  avg(LicensesInUse) as MeanLicenseCount,
  max(LicensesInUse) as MaxLicenseCount
from Dynamics.dbo.SSC_LicenseHistory
where WhenMeasured >= dateadd(day,-30,cast(current_timestamp as date))
and LicensesInUse > 0
group by cast(WhenMeasured as date)
order by cast(WhenMeasured as date) desc;

That’s it! For a few minutes of work, you can start to see your license trends. The job keeps watch even when you are on vacation.

Bored Guy

Hope this helps!

Leave a Reply

Your email address will not be published. Required fields are marked *

 characters available