For lack of a better place to put them....
Alot of people have been digging around the SQL database to find the info that they need. These can be used for reports or custom dashboard screens. I have become fairly familiar with the location of information but finding things can be laborious. I'm hoping that if we create a thread for people to post their SQL queries that more people can get started digging out the info that they want.
Notes:- Try and post them in a way that simply requires a cut/paste for the users to run- Post only SELECT queries- Post only queries that will work on all systems - no custom views/tables/etc- Users will need some basic knowledge on SQL. There can be no liability for the poster - its up you to not break your system.- Its possible that queries might break in future versions (ie 6.1)
If you have found something cool to pick out of the database - POST IT!
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 usersjoin 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 Namefrom usersjoin vAgentLabel vl on vl.agentGuid = users.agentGuidjoin patchStatusTotals ps on ps.agentGuid = users.agentGuidright join agentState st on st.agentGuid = users.agentGuidwhere 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 testwhere lastAuditDate is null
Find all SUSPENDED agents
SELECT displayName, groupName, u.suspendAgent, OsInfoFROM users uLEFT JOIN vAgentLabel aLbl ON u.agentGuid=aLbl.agentGuidINNER JOIN vmachine M ON u.agentGuid = m.agentGuidWHERE 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
WHEN 0 THEN 'Uninstalled'
WHEN 1 THEN 'Installed'
WHEN 2 THEN 'Failure'
WHEN 3 THEN 'Removed by the user'
, (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)
AND avf.agentguid = avs.signum),(SELECT DATEADD(yy,1, MAX(avs.EventTime))
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
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
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)"
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.....
thanks for the reply but it returns the same error. I tried both of your suggestions.
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 'firstname.lastname@example.org'
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
But there are loads, for quick reports it is a pretty handy tool - the hardest part is the SQL query.