Agent Status Count for Dashboards
Product Name: Agent Status Count for Dashboards
Description : This procedure will read the Ksubscribers database for the status of Servers and Workstations, then provide numbers for: Online, Offline, Active, Idle, Suspended
Results are written to individual columns inside a separate SQL database so that historical reports can be built using SSRS or a 3rd party reporting tool, or we used them to compile line charts for use on a dashboard.
Detailed instructions are included explaining how to create the table for storing the data and the field types required, and steps to edit the XML Files to point them at your new database/table.
Instructions :
1. Create somewhere to put the data. For this you'll need a separate database on your Kserver (or wherever your Ksubscribers database is held).
Create the table using the script below if you prefer, but the column names/types must remain the same:
USE [YourDatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KServerAgents](
[eventTime] [datetime] NULL,
[sv0] [numeric](18, 0) NULL,
[sv1] [numeric](18, 0) NULL,
[sv2] [numeric](18, 0) NULL,
[sv198] [numeric](18, 0) NULL,
[st1] [numeric](18, 0) NULL,
[st2] [numeric](18, 0) NULL,
[wv0] [numeric](18, 0) NULL,
[wv1] [numeric](18, 0) NULL,
[wv2] [numeric](18, 0) NULL,
[wv198] [numeric](18, 0) NULL,
[wt1] [numeric](18, 0) NULL,
[wt2] [numeric](18, 0) NULL,
[tcount] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
2. Copy the file 'GetAgentsOnline.xml' into "C:\Kaseya\xml\Procedures\AgentProcSQL\0\SQLRead"
Copy the file 'WriteAgentsOnline.xml' "C:\Kaseya\xml\Procedures\AgentProcSQL\0\SQLWrite"
(Your '\Kaseya' folder might be on a drive other than C:)
3. Edit the XML files and replace the database/table name of "M247Data.dbo.KServerAgents" with your own database and table names. If you used the 'Create Table' script above then you only need to change the database name. Make sure you edit both XML files!
4. Import the Agent Procedure after adding the SQL XML files to ensure the Agent procedure has detected the correct SQL procedures, if not edit the script and replace the broken lines with 'sqlRead' and 'sqlWrite' steps and select the appropriate script from the dropdown menu on the right. If all looks good save and close, now you're ready to test..
5. Run the Agent Procedure on your Kaseya server and the results will appear in the script log similar to the example below:
Query Agents Online Script Summary: Success THEN
Query Agents Online EventTime: 2017-06-10 15:47:20.450 (date/time - you'll need this for your report/dashboard filters!)
Query Agents Online 12 Agent TCount: 3109 (Total Agent Count)
Query Agents Online 11 WS TCount: 2408 (Total Endpoints with agents)
Query Agents Online 10 WS TOnline: 685 (Total Endpoints checking-in)
Query Agents Online 9 Svr TCount: 701 (Total Servers with agents)
Query Agents Online 8 Svr TOnline: 650 (Total Servers checking-in)
Query Agents Online 7 WS Suspended: 0 (Endpoints Suspended)
Query Agents Online 6 WS Idle: 316 (Endpoints not logged on/in use)
Query Agents Online 5 WS Online: 369 (Endpoints Online)
Query Agents Online 4 WS Offline: 1723 (Endpoints Offline)
Query Agents Online 3 Svr Suspended: 39 (Servers Suspended)
Query Agents Online 2 Svr Idle: 222 (Servers not logged on/in use)
Query Agents Online 1 Svr Online: 428 (Servers Online)
Query Agents Online 0 Svr Offline: 12 (Servers Offline)
Check the database table you created to store the results and a new row should exist containing the same as the procedure log. This is now ready for you to use in a report/dashboard.
Note: Apologies for calling the Agent Procedures "scripts" throughout this. Kaseya need to understand that we all still call them scripts - as they used to be in VSA. We don't care what goes on in the backend - and for those of us that go there we know it's a scary and frequently changing place! - but visually these are scripts. Get over it :-)
