Kaseya Community

Free Query - Tracking licensed KNM Assets with a SQL Query

  • Hi all, been a while since i posted as i moved off the tools, 

    I was recently asked by my bosses to reconcile licensing to make sure we were compliant with our agreements. I've never known a way to see in the VSA which devices were consuming a license in KNM, and i also didn't realise that if you've deployed monitoring to an asset in KNM, but deactivate it, it still consumes the license. 

    I began to wonder how many people using Kaseya were consuming licenses in KNM without knowing they were - we were consuming 100+ licenses without a way to tell in the VSA which assets were consuming a license for example. After a quick exchange with Kaseya support, we came up with the following query that shows all assets that are currently consuming a license. 

    Wondering if this will help anyone out looking to reconcile their licenses?

    SELECT distinct a.address AS 'DNS Name / I.P Address',
    c.id as 'id',
    a.name as 'Friendly Name',
    a.description as 'Operating System',
    a.ip_address as 'IP Address',
    a.mac_address as 'MAC Address',
    c.groupName as 'Company'
    FROM knm.objects as a
    JOIN knm.monitors as b on a.id=b.object_id
    JOIN inventory.asset c on a.name =c.name
    WHERE c.agentguid is null

  • Your query returns 7 results. However, In our VSA - System - licence manager - licences tab - reports KNM Assets: 3.

    Checking the 7 assets in monitor - none are active or have any monitors assigned. All 7 are devices we did once monitor, but no longer do.

    I have a VMWare ESXi server we DO monitor in KNM - it is not one of the 7 results returned.

    So, you're kind of close, but I think your query isn't quite correct.

  • Hello Mark,

    Thanks for the query we were searching a way to get the results back what we monitoring in KNM and this will help a lot with identify the assets.

  • @Mark, do you know how to get the SNMP values from the Database. We Monitoring Version numbers and many other SNMP OID and we searching where Kaseya store the data get from SNMP and other sets.

    Thanks in advance.

  • HI Craig,

    Not sure why you're getting the results your seeing. When i look in System -> License Manager -> Licenses it tells me i have used 223 KNM assets - when i run the query above, the count of results is exactly 223.

    From there i went to KNM and cross referenced all assets in KNM (including the disabled ones) and found that when i reactivated the disabled assets they still had the old monitoring. When i removed it, the license was freed up.

    One thing to note (from my discussions with Kaseya at Connect was that the license refresh rate for KNM assets isn't instant either.

    Also, it was Kaseya support that sent me the query, i just added columns. Interesting that you're getting different results and i'd be interested to know how it's coming up different?

    Mark

  • Thanks PJorritsma!

  • I'm not sure myself as i haven't gone to that level, there is a thread kicking around on these forums that explains how to do it, but Kaseya stores it in a different DB

    The way to get the data out i believe is via an agent procedure that pushes the data to another SQL table / view custom added to your DB.

    I know Kaseya Professional Services have been able to do this in the past, worth hitting them up.

    Mark

  • Support gave me this query:

    select distinct cast(a.id as varchar(50)),b.name from

    inventory.knmLicenseUsage a, inventory.asset b, machnametab c

    where b.hwdevicefk = a.id and b.name != c.machName

    I haven't tried it yet, but i will be interesting to compare results.

  • Hi Mark,

    Thanks for the information we have to edit the queries and we now getting the information we needed, we can now see the Assets by 'Router, Switch, ESX, and Storage component etc' this gives up a lot of good views what's in the VSA.

    , did you migrate from KNM to KNM integrated we got double information and I think this because of a migration. I submitted a support ticket to clean the old data and give us only actual assets. I can do this by myself but always ask support for assists.

    Regards

  • no we never had stand-alone KNM. The query I posted works perfectly for me; it shows only the active assets we have including our ESXi hosts. The query Mark posted only displays outdated data - i.e. assets that may once have had a monitor, but now do not; Mark's query does not pick up our ESXi hosts either

    I looked carefully at the tables Mark queries and I think he's only partly picking up the correct data as he's just linking the objects table to the monitors table (without knowing if that object is "active" or not - i.e. it's not filtering for active, enabled or currently assigned), and not actually querying licence data. There's also separate monitor tables for different monitor types, which is why he misses ESXI hosts.

    I'm sticking with the kaseya query for licencing purposes - if I wanted a way to know which monitors may be linked to an asset, then Mark has made a partial start on that, but it needs work such as determining if the link is active or not.