Jump to content

SLA breaches on wallboard


m.vandun

Recommended Posts

Hi @m.vandun

The best way to achieve this is to actually create a widget that reports on the board you have already created.

Let say you had a list on your existing board called "Breaching in 30 minutes" and there was currently 3 requests on it. We could create a widget that shows this count, which could then be placed onto a dashboard/slideshow to show on your wallboard. To do this:

1) Create a new "List of Counters" Widget

2) Design it with colours and an icon, and give it a name - e.g. "Requests within 30 Mins of Breaching Resolution SLA"

3) In the widget properties, you need to include the SQL to get this count. The table this information is stored within is called "h_itsm_board_cards". 

4) In the "where clause" enter the list ID that you want to perform the count on (To find the list ID, you may have to use Database Direct to query the h_itsm_boards table which gives you information about all your boards and their associated lists names and IDs. You could also create a standard report on this table through "Reporting" to gather this information)

In my example, the config looks like this:

Screenshot_5.png

And this results in my widget looking like this:

Screenshot_6.png

I hope this helps,

Kind Regards

Bob

 

Link to comment
Share on other sites

Hi @bob_dickingson,

Thanks for your reply.

I've tried this and I do get an amount in the widget but it does not correspond with the amount on the board for that list. Any idea why this could be.

Is there also a way to see the request ID's?

Kind regards,

Mark

2016-09-29_1345.png

2016-09-29_1346.png

2016-09-29_1346_001.png

2016-09-29_1346_002.png

Link to comment
Share on other sites

Hi @m.vandun

So I forgot a key part on my example - when a card is removed from the board, it actually remains as a row in the database table - but it changes an attribute called h_archived from 0 to 1 (no to yes). Because we are not filtering on this, its showing a count of everything thats ever been in that list. 

So to fix your issue, in your SQL query pop up, in the WHERE clause, change it to:

h_list_id = '10_1474289846443' AND h_archived = 0

My test instance must never have had any cards removed from it so I completely missed this! I hope this fixes the issue for you. 

Bob

Link to comment
Share on other sites

Hi @m.vandun

Glad to hear it's working. 

To show the actual request IDs as well will require a separate widget with a type of "List of Data". 
Using this one, you add very similar criteria - but this time, instead of getting a count, it will list the columns that you choose. An example config here is as follows:

Screenshot_1.png

Then, you can use a dashboard to place your widgets side by side:

Screenshot_2.png

 

Hopefully this will help you achieve what you are looking to do!

Bob

Link to comment
Share on other sites

  • 5 months later...

Hi @TSheward_SGW

I'm glad to hear this post has been useful to you. 

There isn't a way to alias the columns using the List Type above (Single Table Query). But if you change the list type to "Custom SQL Query" you have the ability to write out a SQL statement and include the Alias you need. 

So in my screenshot below, I have taken the example above and converted it into a free text SQL statement - including the Alias to call the result "Reference". 

 

Screenshot_4.png

 

Would this work for you?

Kind Regards

Bob

Link to comment
Share on other sites

5 hours ago, Bob Dickinson said:

Hi @TSheward_SGW

I'm glad to hear this post has been useful to you. 

There isn't a way to alias the columns using the List Type above (Single Table Query). But if you change the list type to "Custom SQL Query" you have the ability to write out a SQL statement and include the Alias you need. 

So in my screenshot below, I have taken the example above and converted it into a free text SQL statement - including the Alias to call the result "Reference". 

 

Screenshot_4.png

 

Would this work for you?

Kind Regards

Bob

Bang on the money @Bob Dickinson, exactly what I was after, worked perfectly!

Thank you so much for your help, have a great weekend!

Tom

Link to comment
Share on other sites

Hi@TSheward_SGW

I'm not aware of a way to do this - there may be a SQL command but its not something I've tested. Could you provide the name of column that contains the data in this format, and from which table?

Kind Regards

Bob

Link to comment
Share on other sites

SELECT SUBSTRING(h_datelogged, 12, 5) FROM h_itsm_requests

works in Database Direct (assuming you're after the time in HH:mm) so in principal that's what you need - however without seeing the rest of the query I can't be certain it'll be that simple.

Link to comment
Share on other sites

Thanks for the help guys really appreciated.

@DeadMeatGF that worked a charm thanks so much! Really weird, I tried MID and SUBSTRING in database direct over the weekend but kept getting an error saying sgw.MID or sgw.SUBSTRING not found. Tried your query just now and worked fine so unsure what I was doing wrong... might just be me going mad. 

Displaying perfectly now, thanks again.

Tom

  • Like 1
Link to comment
Share on other sites

  • 2 weeks later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...