Kaseya Community

SQL Query for Organizations, OrgTypes, and system counts

  • I wish that Kaseya had a standard report for this but figured I would save someone time that was looking to gather this kind of info.  The SQL Query will gather a list of your organizations and the orgTypes (contracts) then list out a system count for Linux, Mac, Server, and Workstation:

    SELECT kasadmin.vbo_OrgMachine_List.orgName, o.orgType,

    sum(CASE  WHEN vMachine.Osinfo LIKE '%Mac%' THEN 1 ELSE 0 END) AS Mac,

    sum(CASE  WHEN vMachine.OsType LIKE '%linux%' THEN 1 ELSE 0 END) AS linux,

    sum(CASE  WHEN vMachine.Osinfo LIKE '%Server%' THEN 1 ELSE 0 END) AS Server,

    sum(CASE  WHEN vMachine.Osinfo Not LIKE '%Server%' and vMachine.Osinfo Not LIKE '%Mac%' and vMachine.Osinfo Not LIKE '%linux%' THEN 1 ELSE 0 END) AS Workstation

    FROM     kasadmin.vbo_OrgMachine_List INNER JOIN

            dbo.vMachine ON kasadmin.vbo_OrgMachine_List.agentGuid = dbo.vMachine.agentGuid

      LEFT JOIN kasadmin.org o on o.id = kasadmin.vbo_OrgMachine_List.OrgID

    group by kasadmin.vbo_OrgMachine_List.orgName, o.orgType

    order by orgName

  • If someone does have a report for this would love to see it

  • Hi Tim,

    You can use the convert the query to a report part. Then use the it to create a report do that with data we need in reports ;)

  • Where is this 'Convert the Query' that you are referring to?  Report Parts can only return a single value....?

  • Hello Tim,

    No, you can create you own report part (with multiply records) and then post them into Info Center to create report like you do with the other parts. But for this you need to write a XML file and import it. Only doesn't provide Kaseya information for this......