SLA breaches on wallboard

[m.vandun]

Good Morning,

I'm looking for a way to view tickets reaching the SLA limit on a slideshow. Is there a way to do this?

I've already created a breach board which is working fully, I want to have this info also on the wallboard.

Kind regards,

Mark

[Guest]

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:

And this results in my widget looking like this:

I hope this helps,

Kind Regards

Bob

 

[m.vandun]

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

[Guest]

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

[m.vandun]

Hi @bob_dickinson,

That seems to have solved the issue. Thanks!

Is there also a way to report on request ID's that are about to breach?

Mark

[Guest]

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:

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

 

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

Bob

[m.vandun]

Hi @bob_dickinson,

Thanks for your help! This is a great addition to monitoring the SLA's.

Mark

[TSheward_SGW]

This is really helpful, is there a way to display an alias instead of the exact column name?

"Reference ID" instead of "h_pk_reference" for example.

Many thanks,

Tom

[Guest]

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". 

 

 

Would this work for you?

Kind Regards

Bob

[TSheward_SGW]

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". 

 

 

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

[TSheward_SGW]

Hi @Bob Dickinson,

I'm trying to display just the time from a date field which outputs as 2017-03-27T08:42:52.961Z.
Can't seem to get MID or SUBSTRING working to extract just the middle 5 characters, any ideas how I could achieve this in the custom SQL widget?

Many thanks,

Tom

[Guest]

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

[Steve Giller]

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.

[TSheward_SGW]

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

[Everton1878]

@Bob Dickinson I had been hoping to be able to embed the webpage for the board in a custom widget but it doesn't allow it, looks like I'll have to do something like this instead but it wont be as nice as showing the board and I wont be able to show the change calendar