Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Help with license usage monitoring

SOLVED
Go to solution
Highlighted
Jan Gunnar Helg
Senior Member

Help with license usage monitoring

I found this SQL query on another post:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[log_license_users] as
insert into dbo.ak_session_log
SELECT
getdate(),
REP_ACCOUNTS.ACC_SHOWNAME, -- SD account name
ITSM_PERSONS.PER_EMAIL, --SD person email
REP_SESSIONS.SES_CLIENT_IPADDRESS --logon user computer ip
FROM
REP_ACCOUNTS, --Table with information about accounts in SD
ITSM_PERSONS, --Table with information about persons in SD
REP_SESSIONS --Table with information about current logon users
WHERE
( REP_ACCOUNTS.ACC_OID=ITSM_PERSONS.PER_ACC_OID ) --connection between account and person
AND ( REP_ACCOUNTS.ACC_OID=REP_SESSIONS.SES_ACC_OID ) --connection between account and current logon


It works well but it doesnt tell me if the user is concurrent or named, how do I add this?
3 REPLIES
Mihai Tita
Occasional Visitor

Re: Help with license usage monitoring

Hi,

Try the following:

...
REP_ACCOUNTS.ACC_SHOWNAME, -- SD account name

REP_ACCOUNTS.acc_nameduser,
REP_ACCOUNTS.acc_concurrentuser,

ITSM_PERSONS, --Table with information about persons in SD
...

Regards,
Mihai
Jay Mistry
Frequent Visitor

Re: Help with license usage monitoring

hi,

have you automated this script to run at certain time intervals or do you run the SQL manually?

What is your SD setup? do you use Oracle or SQL server?

Jay
AndrewB
Regular Collector
Solution

Re: Help with license usage monitoring

Hi Folks,

First of all, thanks for this code!

We run 5.1 and so this code needs some tweaks, which I have done. I also added in fields for named and concurrent users. There is a second statement which just presents the total for each account type. Enjoy!
-------------------------------------------
SELECT
getdate(),
a.acc_nameduser,
a.acc_concurrentuser,
a.acc_showname, -- SD account name
p.per_primaryemailaddress, --SD person email
s.ses_client_ipaddress --logon user computer ip
FROM
ovsd.rep_accounts a, --Table with information about accounts in SD
ovsd.cdm_persons p, --Table with information about persons in SD
ovsd.rep_sessions s --Table with information about current logon users
WHERE
( a.acc_oid=p.per_acc_oid ) --connection between account and person
AND ( a.acc_oid=s.ses_acc_oid ) --connection between account and current logon
order by
a.acc_nameduser desc,
a.acc_concurrentuser asc,
a.acc_showname

-------------------------------------------
SELECT
sum(a.acc_nameduser) as Named,
sum(a.acc_concurrentuser) as Concurrent

FROM
ovsd.rep_accounts a, --Table with information about accounts in SD
ovsd.rep_sessions s --Table with information about current logon users
WHERE
( a.acc_oid=s.ses_acc_oid ) --connection between account and current logon
//Add this to "OnDomLoad" event