Kaseya Community

General Purpose SQL Queries - Post them here!

  • 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 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)



    [edited by: jdvuyk at 5:35 PM (GMT -8) on 1-26-2011] .
  • Machines that are not assigned to a group

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



    [edited by: jdvuyk at 5:41 PM (GMT -8) on 1-26-2011] .
  • 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.....



    [edited by: TEAMnetwork at 6:03 PM (GMT -8) on 1-27-2011] I still refuse.
  • 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.