Kaseya Community

License Count via Database

This question is not answered

Hello all!

I'm looking for a way to find the amount of available licenses via the Kaseya database - does anyone know where this information is stored or how to retrieve it?

Thanks!

All Replies
  • Josh,

    We don't have an available license count, but there is a used license count that comes directly from the database. Mouse over the Licence Counts text at the top of the Security>Install/Remove page. The tooltip breaks down the total used license count.

    -Nick

  • Yeah, I found it already...

  • Hi Nick,

    In what database table can I find this info? I'm trying to build a report with Crystal Reports for our financial department. I found the table 'dbo.avSigData' but that's incomplete. For example I'm missing the expiration date, partial and unused licenses.

  • if you do a search for SQL queries on the forum there is a sql query already here that will show you what you need. I think the heading was sharing SQL / custom reporting or something.. I will have a quick peep.

  • This is one we use, you will need to modify as it wont work straight away but should give you an idea of what you need to get the data..

     

    SELECT

    mt.machname,mt.groupname,CASE WHEN vm.osinfo LIKE '%Server%' THEN 'Server' ELSE 'Workstation' END  as osinfo,

    CASE avf.InstallStatus

    WHEN 0 THEN 'Uninstalled'

    WHEN 1 THEN 'Installed'

    WHEN 2 THEN 'Failure'

    WHEN 3 THEN 'Removed by the user'

    ELSE 'Not Installed'

    END [InstallationStatus]

    ,avf.LastScan

    ,avp.ProfileName

    ,avf.ProtectionEnabled

    ,avf.KaseyaAVVersion

    ,avf.AVClientVersion

    ,avf.SignatureVersion

    ,avf.SignaturePublishTime

    ,avf.LastUpdateTime

    ,avf.FileMonitorStatus

    ,avf.MailMonitorStatus

    ,avf.RebootNeeded

    ,avf.FullScanBeganAt

    ,avf.FullScanEndedAt

    ,

    CASE WHEN LastUpdateTime <= DATEADD(d, -4, GETDATE()) THEN 'Out of Date' ELSE 'Up to Date' END as UpToDate,

    (SELECT DATEADD(yy,1, MAX(avs.EventTime))

    FROM [TT-KASEYA-SQL].[ksubscribers].dbo.AVSigData avs

    WHERE avf.AgentGuid = avs.SigNum

    AND avs.SigStatus = 1 ) [Expire Date]

    , (Select Count(avs.SigNum)

    FROM [TT-KASEYA-SQL].[ksubscribers].dbo.Avsigdata avs

    WHERE avs.Sigstatus = 4

    AND avf.agentguid = avs.signum) [Extended]

    , (Select DATEADD(yy,(Select Count(avs.SigNum)

    FROM [TT-KASEYA-SQL].[ksubscribers].dbo.Avsigdata avs

    WHERE avs.Sigstatus = 4

    AND avf.agentguid = avs.signum),(SELECT DATEADD(yy,1, MAX(avs.EventTime))

    FROM [TT-KASEYA-SQL].[ksubscribers].dbo.AVSigData avs

    WHERE avf.AgentGuid = avs.SigNum

    AND avs.SigStatus = 1))) [Extended Expiry]

    FROM [TT-KASEYA-SQL].[ksubscribers].dbo.machnametab mt

    INNER JOIN [TT-KASEYA-SQL].[ksubscribers].dbo.vMachine vm

    ON mt.agentGuid = vm.agentGuid

    LEFT OUTER JOIN [TT-KASEYA-SQL].[ksubscribers].dbo.AVFeature avf

    ON mt.agentguid = avf.agentguid

    LEFT OUTER JOIN [TT-KASEYA-SQL].[ksubscribers].dbo.AVProfile avp

    ON avf.ProfileId = avp.Id

    WHERE mt.groupName IN (select * from dbo.Split(@GroupName, '|'))

  • Thank you for your help mmartin. I will try this.