Happy Independence Day from all of us at the Connect IT Community! Our US offices will be closed on Monday, July 4th, 2022 in recognition of the holiday. Limited Support staff in the US will be on-call and available for critical Service(s) Down issues only. Normal Support operations in the US will resume on Tuesday, July 5th, 2022.

Agent Status Count for Dashboards

Carl Chatten
Carl Chatten Member
edited January 24 in Solutions

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 :-)