Kaseya Community

SQL Tables for 3rd party reporting

  • We are trying to create our own reports using SQL report builder and attempting to create a list of managed and unmanaged devices per client which has been discovered by the discovery module. We have been able to get the list of manged verses unmanaged but are having difficulties linking this to the client groups, particularly for the network devices that cannot have an agent installed.

     

    Has anyone tried to do this?

  • that way?

    USE ksubscribers;

    select mg.groupName, dev.*

    from inventory.discoveredDeviceSummary as dev inner join machGroup as mg on dev.machGroupGuid = mg.machGroupGuid order by mg.groupName, dev.displayName

  • thats basically it. kaseya's SQL design relies heavily on table joins to extract any useful information.

    e.g. to get a list of server uptimes:

    Select distinct t1.machName, t1.groupName, t1.lastReboot, st.online, st.currentLogin

     from vAgentConfiguration as t1

     join vdb_Scopes_Machines foo on foo.agentGuid = t1.agentGuid

     join users on users.agentGuid = t1.agentGuid

     join userIpInfo ip on ip.agentGuid = t1.agentGuid

     join vAgentLabel st on st.agentGuid = t1.agentGuid

     where ip.osInfo like '%server%' and st.online <> 0

     order by lastReboot desc

  • can this be used in agent procs ?

  • http://help.kaseya.com/WebHelp/EN/VSA/6030000/index.asp#2855.htm

    Use the GetVariable command with the SQL View Data option to create a new procedure variable and set it to the value of a dbo.SqlView.Column value. Use the format SqlViewName/ColumnName/mach.groupID or SqlViewName/ColumnName. If the optional machine ID is omitted, then the value for the agent executing the procedure is retrieved...

    Since you can define your own views, this can be used to read a single value.

    System scripts often use direct sql commands with a "+++SQLCMD" parameter, but the built in editor prohibits to enter this and you cannot import this construct as xml either. If you really know what you are doing, you could change your script directly on the sql server (table scriptThenElse).

    But I see no way to make use of result lists in agent procedures.

    We have used sql queries occasionally to set a flag (a custom Kaseya variable) for all appropriate machines and let Kaseya execute some procedures on these marked machines later.