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.
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
ORDER BY patchtotal DESC