Jump to content

Recommended Posts

Hi all,

I am trying to have a live dashboard to display in the service desk with stats such as:

Number of tickets open - total

Number of tickets logged - daily

Number of tickets open - per analyst

Number of tickets breached - total

However it seems that the measures will only rescan once per day so all of the widgets are a day behind.

I have a widget set up to show open tickets (attached) and even though it seems incorrect (47 open tickets, I can only see 38 in service manager) it has captured the three I logged yesterday, just a day behind.

I am certain it is something I have done (or not done) as is usually the case.

Any ideas?

Thanks

Dan

Capture.PNG

Capture1.PNG

Share this post


Link to post
Share on other sites

@Dan Munns I assume the img with "47" is a widget... if a widget is using a measure then yes, you will have previous period records (in this case since period is "daily", you will have yesterday numbers). However if the widget is using an SQL query then this will have real time values....

 

(thanks to @Steven Boardman for this, I had no idea how to get real time numbers on widgets :D)

Share this post


Link to post
Share on other sites

@Victor So there is no way for me to create the attached dashboard?

The only way I can see you can specify a SQL query is to use 'data chart' from the widgets creation menu (which is either a bar or pie chart). However the attached image would seem to be target counters. The names of at least 4 of the 5 would suggest to me that they are live numbers. So has this functionality been removed or am I just looking in the wrong place?

Thanks

Dan

Capture.PNG

Share this post


Link to post
Share on other sites

@Victor Ok cancel my last.

I feel that I should have a special "I have found what I was looking for after moaning I couldn't find it" button.

The only issue I have now is the fact that it seems to be showing tickets which I cannot find anywhere. As far as I can see in the request list we have 40 requests of all types. But the widget is showing as attached.

Any ideas where they may be hiding? I am a member of all teams and services but still they are incorrect. If it helps it is just the service requests which is wrong. Incidents are correct at 15.

Thanks (for your enduring patience mainly)

Dan

Capture.PNG

Share this post


Link to post
Share on other sites

@Dan Munns

Could you share your query which sits behind this widget?

Are you specifically referencing the statuses to include or exclude?  have you factored in any cancelled statuses which might not show on the request list view?

Hopefully your query will shed some light?

Steve

Share this post


Link to post
Share on other sites

hi @Steven Boardman

I have attached the SQL query. I am targeting only certain statuses and request types but I am very new to this whole SQL game so may have done something wrong. I did wonder if it was because we have done a clear down just before Christmas and when I spoke to Bob Dickinson he did mention that when I do the clear down for go live I need to ask someone at Hornbill to do something that the clear down app doesn't yet do so I wondered if it was anything to do with that?

Thanks

Dan

Capture.PNG

Share this post


Link to post
Share on other sites

@Dan Munns first try set up the WHERE clause like this:
(h_status = 'status.new' OR h_status = 'status.new' OR h_status = 'status.new') AND (h_requesttype = 'Incident' OR h_requesttype = 'Service Request')

Brackets are important when combining AND and OR operators...

 

If it still shows incorrect numbers try isolate the culprit by running each criteria independently and make a note of the numbers for each criteria like all requests (incidents + service requests with status new... then incidents + service requests with status open, etc.) and compare with what you see in the request list...

Share this post


Link to post
Share on other sites

@Dan Munns uh... I have a tab open with you....!!! Thanks! :D ...right... ermm... 

(*mumble to myself: what do I need, what do I need...ermm.. could use a new gaming machine, that, mkay...maybe a bigger TV...mkay...*continues to mumble unintelligible...)

Share this post


Link to post
Share on other sites

Ok so I now have 2 pages of widgets for a wallboard screen. I am now trying to create a widget to show resolved calls per analyst per week. I can set it to show the last X days but as this is a rolling X days it isn't what I need. I need it just to show all calls resolved since the Monday to CURRENT_DAY but cant work it out. The CURRENT_WEEK bit is where I need a hand

Any help @Victor? Might get you a step closer to the cuddly toy... (I am sure the TV will come eventually :) )

 

Capture.PNG

Share this post


Link to post
Share on other sites

I may be wrong but the syntax looks slightly wrong. I thought you needed brackets around each criteria 

(h_requesttype = 'Service Request'OR (h_requesttype = 'Incident') AND ...

Share this post


Link to post
Share on other sites

This syntax works for all the other widgets (thanks Victor)

I believe you can bunch OR together but have to specify groups if using AND

(SQL Newbie so I am sure I will be corrected shortly :) )

Share this post


Link to post
Share on other sites

@Dan MunnsI think you probably know more SQL than me :)

I wish I knew more..

Im sure Victor will give us the answer.

Share this post


Link to post
Share on other sites

@Kelvin @Dan Munns it depends on how you want the expression to be evaluated... you would need to be aware of logical operators precedence as AND and OR have different levels of precedence... To make a simple comparison think of arithmetic operations for multiply or, let's say, addition/subtraction. Example: 2 + 4 x 3 -1 equates to 2 + (4 x 3) - 1 which = 13 - you would not need the brackets because of multiply precedence. However if you want the addition to evaluate first then you would do (2 + 4) x 3 -1 = 17. So you can see you will get different results depending on how you configure operators precedence using brackets.

Going back to your example, if you put (h_requesttype = 'Service Request') OR (h_requesttype = 'Incident') AND (<more_citeria>) 
then the first expression that evaluates is: (h_requesttype = 'Incident') AND (<more_citeria>) - because AND has a higher precedence than OR. The result of this expression, let's call it <result_1>, is used for the next evaluation: (h_requesttype = 'Service Request') OR <result_1>.

Hope this makes sense :)

 

 

Share this post


Link to post
Share on other sites

@Victor Ok that makes some sense. Looks like I will be reading up on SQL in my time off (haha)

Getting back to my original issue (today anyway) any help with the current week part?

Share this post


Link to post
Share on other sites

@Dan Munns sorry, missed that part... so much for my new TV :D ...

Try this: (  h_dateresolved > (CURDATE() - INTERVAL (WEEKDAY(CURDATE())) DAY)  )

Share this post


Link to post
Share on other sites

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