Kaseya Community

SQL Query Problems

  • Hi,

    We have almost 30,000 agents online (Kaseya 5.2) and have them separated into relevant companies. As you can imagine the amount of agents can get overwhelming in terms of keeping on top of patching. What I'm looking to do is get a SQL query to pull the total 'missing' patch results from the database for each company and sort them (in a report) from highest to lowest (by group). I am not the most advanced user in SQL but I have came up with this:

    DECLARE @patchtotals integer;

    SET @patchtotals = sum(a.missingapproved)

    SELECT DISTINCT a.agentguid, a.missingapproved, b.machinename, b.groupname

    from dbo.patchstatustotals as a inner join

    dbo.vpatchstatus as b on a.agentguid = b.agentguid

    where groupname like '%sc2%'

    group by a.agentguid,b.machinename, a.missingapproved, b.groupname

    Unfortunately I get the error:

    Msg 4104, Level 16, State 1, Line 2

    The multi-part identifier "a.missingapproved" could not be bound.

    HELP!!! I've scoured the forums all over the world and can't figure it out.....What am I doing wrong.

  • The basic "Select" query works for me.  You might not need the DECLARE and SET.

    ~ tommy

  • I wonder if you're maybe getting the error because the SET statement is declaring "@patchtotals" as a SUM before the SELECT statement has run? Try this:

    SELECT DISTINCT a.agentguid, SUM(a.missingapproved) AS patchtotal, b.machinename, b.groupname

    from dbo.patchstatustotals as a inner join

    dbo.vpatchstatus as b on a.agentguid = b.agentguid

    where groupname like '%sc2%'

    group by a.agentguid,b.machinename, a.missingapproved, b.groupname

    ORDER BY patchtotal DESC