Systems Management

Hi Erik,

I believe the details of how the widgets calculate the data that is presented in the Dashboard.  Would not be documentation, as this would be something that is not typically configurable by the customer.

If you provide more details about the exact widgets you are interested in and what data output you are trying to retrieve, we may be able to help with the required SQL.

I'm doing an exercise to get more familiar with KACE's database and our new BI platform, so I'm trying to recreate the entire dashboard.  I'll go through each widget in our dashboard:

Tickets Opened Today - This one is relatively easy, although the widget appears to be showing tickets opened only for some queues, not all.  Is that configurable somewhere?  It seems to show only the IT-based subset of queues, nothing dev-related.

Active Tickets by Owner - I am currently calculating based on tickets that have a TIME_OPENED but not a TIME_CLOSED, but I'm definitely getting a whole lot more than what the widget in KACE is reporting.  I would definitely be interested in the SQL behind this, as I assume there's a column used that I'm missing.

Active Tickets by Category - Same thing, and same problem.  I can get the Owner and Category easily enough, but because I'm using all tickets with a TIME_OPENED and no TIME_CLOSED I'm getting a lot more tickets than the KACE widgets are reporting.

Active Tickets by Priority - Same thing.  I'm using HD_PRIORITY.NAME which doesn't seem to get the same list of priorities, as I'm only seeing Blank, Low, and High, although this may be just the tool I'm using only displaying those three because there are so many Blank (99.74%) as a result of my improper query and/or our database having data going back to 2004 (I believe).

Tickets Overdue - For this calculation I am looking at tickets with a DUE_DATE prior to today, a blank TIME_CLOSED, and an OWNER_ID set.  Again, I am coming up with a larger number than what the widget in KACE reports.

Overdue Tickets by Owner - Same calculation at Tickets Overdue, displayed by USER.FULL_NAME.  Because my calculation for Tickets Overdue is off I get way more tickets than the widget in KACE displays.

Any clues as to proper queries would be greatly appreciated, and thank you for your feedback!

-Erik

Erik, we have been putting a fair amount of work into Power BI over the last six months and have got some pretty good results, if you’d like to take a look, then please get in touch. We learned pretty quickly that you need good experience in all of the following areas to get good results on a power BI dashboard for KACE

• Power BI (obviously)
• KACE database and configuration knowledge
• SQL reporting
• Service Desk, in your case, Industry experience