Kaseya Community

General Purpose SQL Queries - Post them here!

  • Appreciate the quick response. Will investigate this today and will post any information I find. Thanks for that.

  • agreed with mmartin.

    There are loads of resourses for SSRS out there.  make sure you have the reporting services installed/configured on your server first.  Once this is confirmed you can go to http://[server]/reports and you should see a gui.  This is where you reports can be run from once created.  From there you have a learning curve of building reports using one of the visual studio tools.  It can be hard initially but its very rewarding once you get it sorted.  form here you can export into many different formats, inc PDF.

    As for excel spreadsheets?  You can map directly to the data in an SQL server from within excel itself.  This is pretty quick and easy once you get it sorted.  look for "data sources" in excel and do some searching online.

  • Also agreed with mmartin

    I really do like excel :) Here is another query that i use very extensively through excel. Its GREAT for service desk reporting, pretty complex joins on 3 tables to get everything you need.

    I use this query to get service desk hours per job out. Also CUSTOM FIELDS can be stripped down in excel, for those of you who have custom fields in the your service desk this might be a godsend
    Remember Info center > reports don't do custom field reporting at the moment.

    select * from ksubscribers.kasadmin.sdincident a
    inner join ksubscribers.kasadmin.SDIncidentJoined b on a.id=b.id
    left outer join ksubscribers.kasadmin.SDIncidentActivityHours c on a.sdIncidentActivityFK=c.sdIncidentActivityFK

    Please note this is a HUGE data dump, i would strongly recommend putting a date range on it, we are a relatively small shop so i didn't bother just yet, but if you have thousands of tickets, expect the query to take a while. 

     

  • Here's a SQL query Im now using to show open Monitoring alarms.  Once you close the alarms in the Alarm Summary page they will also disapear from your dashboard - hope it helps others with their dashboards

    select top 12 al.message as Message

    from Users

    join userIpInfo ip on ip.agentGuid = users.agentGuid

    join vAgentLabel vl on vl.agentGuid = users.agentGuid

    right join monitoralarm al on al.agentguid = users.agentguid

    where ip.osInfo like '%server%' and monitoralarmstateid=1 and (users.suspendAgent is null or users.suspendAgent = 0)

    Dave

  • And one other thing Im doing in my Dashboard (based in Excel), is writing a score I have on the dashboard to a CSV every time the worksheet gets updated.  Then I can review the whole day/week/month to see how the team fared with our Dashboard score.  Some may not want this but thought it was worth contributing to this forum topic.

    Dim sFile As String

    Dim sPath As String

    Dim sLine As String

    Dim tstamp

    Dim dstamp

    sPath = "C:\DASHBOARD\"

    sFile = "Dashboard_" & Format(Now, "YYYYMM") & ".CSV"

    Close #1

    tstamp = Time

    dstamp = Date

    Open sPath & sFile For Append As #1

    Print #1, dstamp & " " & tstamp & "," & Cells(1, 7) 'Cell 1,7 is the Excel cell with the Dashboard Score

    Close #1

  • Any chance you could explain how you have Excel setup as a dashboard (don't want to take up much of your time) as we can only pull all information from Database views at the moment.

  • Here's a SQL-query that returns machine groups where ALL agents are offline. Useful if you have divided agents into machine groups by geograhical location, and want to spot internet connection problems etc:

    SELECT K.groupName FROM(

    SELECT z.groupName, SUM(z.MAX) AS [MAX], SUM(z.OFFLINE) AS [OFFLINE] FROM(

    --MAX

    SELECT m.groupName, COUNT(m.groupName) AS [MAX], 0 AS [OFFLINE] FROM machNameTab m

    RIGHT JOIN agentState a ON a.agentGuid = m.agentGuid

    GROUP BY m.groupName

    UNION

    --Offline

    SELECT m.groupName, 0 AS [MAX], COUNT(m.groupName) AS [OFFLINE] FROM machNameTab m

    RIGHT JOIN agentState a ON a.agentGuid = m.agentGuid

    GROUP BY m.groupName, a.online

    HAVING a.online = 0

    ) z

    GROUP BY z.groupName

    ) K

    GROUP BY K.groupName, K.MAX, K.OFFLINE

    HAVING K.MAX = K.OFFLINE

  • Logged on users:

    SELECT [adminName]

     FROM [ksubscribers].[dbo].[administrators]

     where sessionExpiration >= GETDATE()

    order by adminName

     

    Disabled users:

    SELECT [adminName]

     FROM [ksubscribers].[dbo].[administrators]

     where disableUntil >= getdate()

    order by adminName

     

    Count of failed outbound e-mails last 24h (all with status other than 'Success'):

    SELECT COUNT(*)

     FROM [ksubscribers].[dbo].[emailStatus]

     where sendStatus <> 2 and statusTime >= dateadd(hour,-24,getdate())

  • *BUMP*

    Any chance you could explain how you have Excel setup as a dashboard (don't want to take up much of your time) as we can only pull all information from Database views at the moment.

  • Find Disks with less than 1 GB free

    SELECT COUNT(*)

     FROM [ksubscribers].[dbo].[vDiskSpace]

     WHERE [freeSpace] <= 1000

  • Didn't see this thread - Missing KES Report - Machines without KES

  • Here is an Update to the Disk Drives under 1 GB that when used with Power Gadgets looks like this

     

     

    Here is the SQL code

    SELECT [freeSpace], [usedSpace], [displayName] as Name
    FROM [ksubscribers].[dbo].[vDiskSpace]
    join vAgentLabel vl on vl.agentGuid = [ksubscribers].[dbo].[vDiskSpace].[agentGuid]
    WHERE [freeSpace] <= 1000

    Enjoy

     

  • Hi David.

    Excel as Dashboard? Have you a small example for us? ;)

  • @ David, i am going to be working on documentation for this tonight, hopefully i have something for you in the next 24 hours. It is easy but its a lot to get your head around.

    For Everyone else, this is a "better" disk space utilisation query, it formats really nicely, create a pivot table from this data, it blows customers away.

    select machName, DriveLetter, TotalSpace, UsedSpace, FreeSpace, VolumeName, FormatType from ksubscribers.dbo.vCurrDiskInfo
       where DriveLetter <>  'A' and
       DriveLetter <>  'D' and
       DriveType <> 'CDROM'
    order by machName asc

    Select all those fields where drive doesn't equal A,, D or Drivetype doesn't = CDROM. Enjoy.

  • Hi guys,

    Does anyone have the a SQL Script which would return the Standard Dashboard Group Alarm Status results?

    Thank you