Find all currently OFFLINE agents:

select vl.displayName as MachineName, st.online as IsOnline, st.offlinetime as TimeOffline, DATEDIFF(HOUR,st.offlineTime, getdate()) as HoursOffline
from users
join userIpInfo ip on ip.agentGuid = users.agentGuid
join vAgentLabel vl on vl.agentGuid = users.agentGuid right
join agentState st on st.agentGuid = users.agentGuid
where ip.osInfo like '%server%' and st.online = 0 and (users.suspendAgent  is null or users.suspendAgent = 0)

Machines that are not assigned to a group

SELECT [Machine_GroupID] as Machine
  FROM [ksubscribers].[dbo].[vMachine]
  Where groupname like '%unnamed'

Machines pending reboots

select vl.displayName as Name
from users
join vAgentLabel vl on vl.agentGuid = users.agentGuid
join patchStatusTotals ps on ps.agentGuid = users.agentGuid
right join agentState st on st.agentGuid = users.agentGuid
where ps.rebootPending > 0  and st.online <> 0

Machines that have never had an audit run:

Select displayName as Name
from
    (select st.online , vl.displayName,
            (select top 1 auditRsltDate.date from auditRsltDate
            where auditRsltDate.agentGuid = users.agentGuid
            and locked = 0  ) as lastAuditDate
        from users
        join vAgentLabel vl on vl.agentGuid = users.agentGuid
        join agentState st on st.agentGuid = users.agentGuid
        join userIpInfo ip on ip.agentGuid = users.agentGuid 
        where st.online = 1) as test
where lastAuditDate is null

Find all SUSPENDED agents

SELECT displayName, groupName, u.suspendAgent, OsInfo
FROM users u
LEFT JOIN vAgentLabel aLbl ON u.agentGuid=aLbl.agentGuid
INNER JOIN vmachine M ON u.agentGuid = m.agentGuid
WHERE u.agentGuid IN (SELECT a.agentGuid FROM machNameTab a WHERE (a.machName LIKE '%') AND ((a.groupName LIKE '%')) )
AND u.suspendAgent = 1 --AND OsInfo LIKE '%Server%'
ORDER BY aLbl.displayName

Kes licensing and status

SELECT

mt.machname,mt.groupname,CASE avf.InstallStatus

WHEN 0 THEN 'Uninstalled'

WHEN 1 THEN 'Installed'

WHEN 2 THEN 'Failure'

WHEN 3 THEN 'Removed by the user'

END [InstallationStatus]

,avf.LastScan

,avp.ProfileName

,avf.ProtectionEnabled

,avf.KaseyaAVVersion

,avf.AVClientVersion

,avf.SignatureVersion

,avf.SignaturePublishTime

,avf.LastUpdateTime

,avf.FileMonitorStatus

,avf.MailMonitorStatus

,avf.RebootNeeded

,avf.FullScanBeganAt

,avf.FullScanEndedAt

, (SELECT DATEADD(yy,1, MAX(avs.EventTime))

FROM AVSigData avs

WHERE avf.AgentGuid = avs.SigNum

AND avs.SigStatus = 1 ) [Expire Date]

, (Select Count(avs.SigNum)

FROM Avsigdata avs

WHERE avs.Sigstatus = 4

AND avf.agentguid = avs.signum) [Extended]

, (Select DATEADD(yy,(Select Count(avs.SigNum)

FROM Avsigdata avs

WHERE avs.Sigstatus = 4

AND avf.agentguid = avs.signum),(SELECT DATEADD(yy,1, MAX(avs.EventTime))

FROM AVSigData avs

WHERE avf.AgentGuid = avs.SigNum

AND avs.SigStatus = 1))) [Extended Expiry]

FROM AVFeature avf

INNER JOIN machnametab mt

ON mt.agentguid = avf.agentguid

INNER JOIN AVProfile avp

ON avf.ProfileId = avp.Id

Hi guys

Below is a query Ive worked on to show data to be replicated using BUDR.  It works fine, except if the amount of data to be replicated is greater than 2GB, I get an error stating that its too big for an integer.  

Run-time error '-2147217833 (80040e57)

The conversion of the varchar value '102796273897' overflowed an int column.

Is there a way I can make the Bytesremaining variable a large integer type so that it can handles values of hundreds of GB's?

Select query below....

select vl.displayName as MachineName, os.bytesremaining as Bytesremaining

from Users

join userIpInfo ip on ip.agentGuid = users.agentGuid

join vAgentLabel vl on vl.agentGuid = users.agentGuid

right join offsiteclient os on os.agentguid = users.agentguid

where os.bytesremaining > 0 and ip.osInfo like '%server%' and (users.suspendAgent is null or users.suspendAgent = 0)"

Thanks

Dave

Use CAST(os.bytesremaining as bigint). You can also convert this to Mb or Gb e.g. (CAST(os.bytesremaining as bigint) / 1048576) as MBytesremaining

BIGINT will handle 2^63-1 (9,223,372,036,854,775,807) i.e. quite a lot.

Incidentally, you are assuming that the box used to do the local->offsite thing is a server. Not so in my case and may be the same for others.....

Useful query.....

Hey TEAMnetwork,

thanks for the reply but it returns the same error.  I tried both of your suggestions.  

thanks agian

Dave

Dave

It definitely worked on my server. PM me your full query and I'll find out what's going on.

Simple enough but: Select everything from Monitor Alarm to gather insight into high volumes of alarms per customer

select * from ksubscribers.dbo.monitorAlarm

where alertEmail not like 'connect@kaseya.com'

and  eventDateTime between '2011-01-010 00:00:00.000' and '2011-01-30 00:00:00.000'

and alertSubject like '%customer%'

order by alertSubject asc

Change your date range for a month / year / decade or whatever you want.

AlertEmail is not like because we don't want our customers seeing the noc team alerts (for example)

%customer% goes without saying

Order by alertSubject because alert subject ALWAYS contains the customer machine name then customer name, it looks nice.

We run this through a connection to excel, then do all sorts of things to the data to make it look pretty

- pivot table by customer, machine > count of cpu alerts in 24hr period, 48 hour period etc

- pivot table by customer, machine > count of times RAM below XYZ etc

sorry if those last bits other than the SQL command was beyond the scope of discussion, i might start a new thread to get a discussion happening on valuable metrics that we can turn into reporting, or does anyone know if that already exists?

I love everything about this thread !   I just used mmartin's KES query to create a little program so we could find expiring agents faster for KAV upgrades.  Thanks guys!

Hi all. I have been working through a few of your queries over the past day and have managed to get the basics down, but would appreciate any help as to how I can create a custom graphical report using SQL Reporting Services.

So far I have been able to run the query in SQL itself, but have no idea how to export the data into a working excel spreadsheet or PDF report?

you can copy and paste from the sql query window into excel.

SRSS is the best option if it is a regular report you want to run - there are lots of resources on the web on how to use SRSS

Example of one is

alexduggleby.com/.../sql-server-reporting-services-2008-tutorial-in-5-minutes-or-how-to-send-my-boss-a-report-from-a-view-quickly

But there are loads, for quick reports it is a pretty handy tool - the hardest part is the SQL query.