Velocity Report showing licence usage
Options
Harald Hotz-Behofsits
Member Posts: 157
I have created a velocity report showing the usage of float and creator. It shows the usage for the last 30 days.
#set($org=$project.getOrganization().getId())<html>
<head1>Daily Usage of Jama Licenses usage last 30 days</head1>
<br><br>
Started print $dateTool
<br><br>
<table border=1 cellspacing=0 cellpadding=0>
<tr>
<td>Date</td>
<td>Used</td>
<td>Failed Logins</td>
<td>Max Available</td>
<td>Used Named</td>
<td>Assigned Named</td>
</tr>
#set($licSvc = $applicationContext.getBean("dwrLicenseService"))
#set($LicLastDays =$licSvc.getLicenseDataForLastNDays("last30Days",$org,"FLOAT"))
#set($NamedLastDays =$licSvc.getLicenseDataForLastNDays("last30Days",$org,"NAMED"))
#set($cnt=0)
#foreach($LicDay in $LicLastDays)
#set($NamedDay = $listTool.get($NamedLastDays,$cnt))
<tr>
<td>
$dateTool.format('yyyy-M-d',$LicDay.getIntervalStart())
</td>
<td>
$LicDay.getNonReservedActiveLicenseCount()
</td>
<td>
$LicDay.getNumberRejected()
</td>
<td>
$LicDay.getMaxLicenseCount()
</td>
<td>
$NamedDay.getNonReservedActiveLicenseCount()
</td>
<td>
$NamedDay.getReservedLicenseCount()
</td>
</tr>
#set($cnt=$cnt+1)
#end
</table>
<br><br>
Finished print $dateTool
<br><br>
</html>
#set($org=$project.getOrganization().getId())<html>
<head1>Daily Usage of Jama Licenses usage last 30 days</head1>
<br><br>
Started print $dateTool
<br><br>
<table border=1 cellspacing=0 cellpadding=0>
<tr>
<td>Date</td>
<td>Used</td>
<td>Failed Logins</td>
<td>Max Available</td>
<td>Used Named</td>
<td>Assigned Named</td>
</tr>
#set($licSvc = $applicationContext.getBean("dwrLicenseService"))
#set($LicLastDays =$licSvc.getLicenseDataForLastNDays("last30Days",$org,"FLOAT"))
#set($NamedLastDays =$licSvc.getLicenseDataForLastNDays("last30Days",$org,"NAMED"))
#set($cnt=0)
#foreach($LicDay in $LicLastDays)
#set($NamedDay = $listTool.get($NamedLastDays,$cnt))
<tr>
<td>
$dateTool.format('yyyy-M-d',$LicDay.getIntervalStart())
</td>
<td>
$LicDay.getNonReservedActiveLicenseCount()
</td>
<td>
$LicDay.getNumberRejected()
</td>
<td>
$LicDay.getMaxLicenseCount()
</td>
<td>
$NamedDay.getNonReservedActiveLicenseCount()
</td>
<td>
$NamedDay.getReservedLicenseCount()
</td>
</tr>
#set($cnt=$cnt+1)
#end
</table>
<br><br>
Finished print $dateTool
<br><br>
</html>
0
Comments
-
AWESOME. Thanks for sharing this!0
-
This velocity report does not work any more due to unknown changes.
I use now this SQL query that works with ms sql server.
select top 30 licusageNamed.usage used_named, licusageFloat.usage used_float, licusageFloat.snapshotDate
from
(SELECT TOP 1000000 max(cast(propertyvalue as integer)) usage,propertyname,CAST(ls.createdDate AS DATE) snapshotDate FROM license_snapshot ls,license_snapshot_properties lsp
where ls.id=lsp.licenseSnapshotId
and propertyname ='FLOAT-non-reserved-active'
group by propertyname,CAST(ls.createdDate AS DATE)) as licusageFloat,
(SELECT TOP 1000000 max(cast(propertyvalue as integer)) usage,propertyname,CAST(ls.createdDate AS DATE) snapshotDate FROM license_snapshot ls,license_snapshot_properties lsp
where ls.id=lsp.licenseSnapshotId
and propertyname = 'NAMED-non-reserved-active'
group by propertyname,CAST(ls.createdDate AS DATE)) as licusageNamed
where licusageNamed.snapshotDate=licusageFloat.snapshotDate
order by licusageFloat.snapshotDate desc0 -
0