Kaseya Community

General Purpose SQL Queries - Post them here!

  • Hi all, im after a sql query to display server disk information? Craig I have tested you agent disk information and it seems all fine. But I need a query to display disk usage and only alert if disk is

    Thank you
  • Show Servers down but ignore suspend agent and suspended alarms;

    SELECT displayName as 'Agent',
    agentstate.online as 'Online',
    offlinetime as 'Last Online',
    startSuspend as 'Suspend Monitors From',
    endSuspend as 'Suspend Monitors To',
    suspendagent as 'Suspend Agent',
    osInfo as 'OS Info'
    FROM [ksubscribers].[dbo].[vAgentLabel]
    left JOIN monitorSuspend ON [monitorSuspend].agentguid = vagentlabel.agentguid
    left JOIN users ON users.agentguid = vAgentLabel.agentGuid
    left JOIN agentstate ON agentstate.agentguid = vAgentLabel.agentGuid
    left JOIN userIpInfo ON useripinfo.agentguid = vAgentLabel.agentGuid
    WHERE osInfo like '%server%'
    and agentstate.online = 0
    and (suspendAgent is NULL or suspendagent = 0)
    and (startsuspend >= getdate() or startSuspend is NULL)
    and (endsuspend <= getdate() or endSuspend is Null)

    Corrected NULL query
    [edited by: Paul Wilkinson at 4:14 AM (GMT -7) on Apr 1, 2016]
  • I wrote some queries in Database Kaseya, and want to deploy it in Kaseya Environment as report

    Is there any chance anybody help me?


  • Cloud Backup (Acronis) Status:

    USE [ksubscribers] -- Acronis Backup Status (Agree's with Kaseya > Cloud Backup > Show > Machines (filter on Install Status)

    select mg.GroupName, mt.machName, ui.lastLoginName AS LastLoggedOnUser,  

    dbo.fn_GetTenantDatePartition(u.firstCheckin, mt.partitionId) AS firstCheckin,

    CASE WHEN s.online = 1 THEN dbo.fn_GetTenantDatePartition(CURRENT_TIMESTAMP, mt.partitionId)

    ELSE dbo.fn_GetTenantDatePartition(s.offlineTime, mt.partitionId)

    END AS lastCheckin,

    a.dateModified AS InstalledOn, a.installStatusDomainItemRef, aas.startTime AS BackupStart, aas.finishTime AS BackupFinished,

    aas.state, aas.status, aas.effect, aas.reason, aas.cause, aas.title AS BackupPlanTitle, aas.initiatedBy,

    CASE WHEN ui.osType IN ('XP', '2008', '7', '2003', 'Vista', '2000', '8', '8.1', '2012', '10', '2016') THEN 'Windows ' + ui.osType

    ELSE ui.osType

    END AS OperatingSystem,

    ui.osInfo AS OSInformation, mt.agentGuid

    FROM dbo.machNameTab AS mt WITH (NOLOCK)

    LEFT OUTER JOIN dbo.users AS u WITH (NOLOCK) ON u.agentGuid = mt.agentGuid

    INNER JOIN dbo.userIpInfo AS ui WITH (NOLOCK) ON ui.agentGuid = mt.agentGuid

    INNER JOIN dbo.machGroup AS mg WITH (NOLOCK) ON mt.machGroupGuid = mg.machGroupGuid

    LEFT OUTER JOIN dbo.agentState AS s ON mt.agentGuid = s.agentGuid AND u.agentGuid = s.agentGuid

    INNER JOIN KCB.Asset AS a WITH (NOLOCK) ON mt.agentGuid = a.agentGuid

    LEFT OUTER JOIN KCB.AcronisActivity AS aas WITH (NOLOCK) ON a.lastActivityId = aas.id --((a.lastActivityId = aas.id) OR (a.lastSuccessfulActivityId = aas.id))

    LEFT OUTER JOIN dbo.lastReboot AS r WITH (NOLOCK) ON r.agentGuid = mt.agentGuid

    order by mg.GroupName, mt.machName