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 godsendRemember Info center > reports don't do custom field reporting at the moment.
select * from ksubscribers.kasadmin.sdincident ainner join ksubscribers.kasadmin.SDIncidentJoined b on a.id=b.idleft 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
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)
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
sPath = "C:\DASHBOARD\"
sFile = "Dashboard_" & Format(Now, "YYYYMM") & ".CSV"
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
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(
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
SELECT m.groupName, 0 AS [MAX], COUNT(m.groupName) AS [OFFLINE] FROM machNameTab m
GROUP BY m.groupName, a.online
HAVING a.online = 0
GROUP BY z.groupName
GROUP BY K.groupName, K.MAX, K.OFFLINE
HAVING K.MAX = K.OFFLINE
Logged on users:
where sessionExpiration >= GETDATE()
order by adminName
where disableUntil >= getdate()
Count of failed outbound e-mails last 24h (all with status other than 'Success'):
where sendStatus <> 2 and statusTime >= dateadd(hour,-24,getdate())
Find Disks with less than 1 GB free
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 NameFROM [ksubscribers].[dbo].[vDiskSpace]join vAgentLabel vl on vl.agentGuid = [ksubscribers].[dbo].[vDiskSpace].[agentGuid]WHERE [freeSpace] <= 1000
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.
Does anyone have the a SQL Script which would return the Standard Dashboard Group Alarm Status results?