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?
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 ?
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.