Widget help!

[Alisha]

Hello,

I have two questions please.

1. Is there a way to display the Status in a widget with just 'Open', 'Closed', etc. rather than 'status.open' and 'status.closed'?
2. I have created a widget to show the most recent calls logged by analysts today. I have used h_createdby, but I would also like to use the Handle, which I understand is stored in h_name in the h_sys_accounts table. How would I do the join to display the Handle please?

Any guidance would be much appreciated.

Many thanks,
Alisha

[Alisha]

Don't worry about this, I have managed to use the nested REPLACE function to get what we need for both, but it was a bit tricky with all the brackets!

However, if there is a cleaner way of doing this, it would be good to know!

 

[Bob Dickinson]

Hi @Alisha

I think the answer to this might be dependant on the type of widget you are creating here. 

If you could post some screenshots (ensuring to obfuscate any sensitive customer information), then we may be able to advise further, as we have recently introduced some Look Up functionality but it's only available in certain areas. 

Kind Regards

Bob

[Alisha]

Hi @Bob Dickinson,

I used the following REPLACE function.
 

REPLACE(REPLACE(h_status, 'status.open', 'Open'), 'status.onHold', 'On-Hold') AS 'Status'

I'm now having difficulties with using h_fk_priorityname. This is my SQL query. When I replace 'High' with 'Critical', it works. But it doesn't work for just the 'High' priority. It also works with the 'Low' priority, but not the 'Medium' priority.
 

SELECT h_pk_reference, h_summary, h_fk_team_name, DATE_FORMAT(h_fixby, '%a %D %b %Y - %H:%i') AS 'Fix By', 
REPLACE(REPLACE(h_status, 'status.open', 'Open'), 'status.onHold', 'On-Hold') AS 'Status'
FROM h_itsm_requests 
WHERE h_fk_servicename ="IT Services"
AND h_requesttype = "Incident"
AND h_fk_priorityname = "Critical" 
AND h_status IN ("status.new", "status.onhold", "status.open") 
ORDER BY h_pk_reference DESC

Please could you tell me what I'm doing wrong?

Many thanks,
Alisha

[Bob Dickinson]

Hi @Alisha

If the query runs and works with Critical and Low then I don't see why Medium and High wouldn't work. 

Perhaps the best thing to do to get a clear view of this is run the request WITHOUT the h_fk_priorityname in the WHERE CLAUSE, and include h_fk_priorityname in the SELECT part of the statement - so it will return ALL of the requests logged today, regardless of the priority. Then from the results, so can scan through and see if:
a) There actually are any Medium or High tickets in the result set
b) Check to see what the spelling/format is of the Medium/High ones to make sure the right thing is being added into the where clause perhaps?

So it would be this:

SELECT h_pk_reference, h_fk_priorityname, h_summary, h_fk_team_name, DATE_FORMAT(h_fixby, '%a %D %b %Y - %H:%i') AS 'Fix By', 
REPLACE(REPLACE(h_status, 'status.open', 'Open'), 'status.onHold', 'On-Hold') AS 'Status'
FROM h_itsm_requests 
WHERE h_fk_servicename ="IT Services"
AND h_requesttype = "Incident"
AND h_status IN ("status.new", "status.onhold", "status.open") 
ORDER BY h_pk_reference DESC

It might help diagnose the issue

Let me know what results you get

Kind regards

Bob

[Alisha]

Hi @Bob Dickinson,

I used that query but it gives me an error straight away: "Error in SQL query, see log for more details". I don't understand why!

Many thanks,
Alisha

[Bob Dickinson]

Hi @Alisha

Could you send me a screenshot if possible so I cabn establish exactly where this is happening (Feel free to direct message this to me if it contains anything you don't want to be publically visible)

Thanks

Bob

[Bob Dickinson]

Hi @Alisha

Thanks for the details. I have been able to replicate this now - quite simply, we have a 100 result (row) limit on a List Of Data Widget. This is to prevent a huge amount of server load and potentially cause performance issues on the rest of the platform. Also, the list of Data on a Dashboard will potentially become very small in text size if it needs to be condensed to fit within your widget. 

To establish this, I had a look at the logs on your instance and noticed this error when the widget was being created:

The maximum number of rows allowed (100) has been exceeded, query aborted

Perhpas we can make this a little clearer to an end user, so I'll feed that back. But we are unlikely to expand the number of rows returned because of the potential of the performance issues mentioned above. 
But this explains why some criteria (e.g. Critical, Low priorities) work - because they return fewer than 100 results, whereas High and Medium have more than 100 so fail. 

I guess the next step is to consider why you need to show 100+ results in this way on a Dashboard widget (rather than a standard Report) - and if there is a way of potentially reducing this down to a more manageble amount which would work better on a List of Data widget. 

I hope this helps, let me know if you have any more questions around this

Kind Regards

Bob

 

[Alisha]

Hi @Bob Dickinson,

Thank you for the above explanation. We have now limited our results to 10.

I'm now having difficulty with another query and was wondering if you could help please?

I am trying to remove results from a Count widget that have the sub-status 'With Third Party'. I have tried different operators such as
!=
<>
NOT LIKE
NOT IN
NOT CONTAINS

I have tried using h_sub_status and h_sub_status_id, but all return a result of 0.

Is there anything else that I can try please?

Many thanks,
Alisha

[Bob Dickinson]

HI @Alisha

I think this might be because when you use a Not Equals operator, it also removes any values that are NULL. So if you don't use any other substatuses in this result set (i.e. every substatus is either "With Third Party" or Empty) - then the filter you've used above will result in 0 results. 

Try this - and see if it gives you a count closer to what you would be expecting:

 WHERE (h_sub_status != 'With Third Party' OR h_sub_status IS NULL)

Let me know if that works

Kind Regards

Bob

[Alisha]

@Bob Dickinson It works! That makes me so happy, thank you so much, Bob! It's been bugging me for so long! 

[Michael Sharp]

Thanks @Bob Dickinson , helped me with something else!!

[Alisha]

Hi @Bob Dickinson,

I'm creating a chart widget and I do now need to show the analysts' names underneath the columns. At the moment I'm using h_createdby, but that only shows their User ID. Is there a way to do this please?

Many thanks,
Alisha

[Bob Dickinson]

Hi @Alisha

When you are creating your widget, if you click on the "Chart Settings" button, you should see an option called Label Display Lookup. 

Because h_createdby is the User ID, you need to select the option: "User Name from Id"

This should then map all of the user IDs to their real names and make it look a lot nicer. 

 

Hope this helps

Bob

[Alisha]

@Bob Dickinson That's perfect! Thank you so much! 

[Alisha]

Hi @Bob Dickinson,

I'd like to put our Changes and Releases into one widget but am not sure how to join the tables. Please could you help?

So this is what I tried to do but it doesn't work:

SELECT 
h_pk_reference, h_summary, DATE_FORMAT(DATE_ADD(h_start_time, INTERVAL 1 HOUR), 
'%a %d - %H:%i') AS 'Start Time', 
DATE_FORMAT(DATE_ADD(h_end_time, INTERVAL 1 HOUR), '%H:%i')  AS 'End Time'
FROM h_itsm_requests
Inner Join h_itsm_changerequests on h_itsm_requests.h_pk_reference=h_fk_reference
Inner Join h_itsm_releases on h_itsm_requests.h_pk_reference=h_fk_reference
WHERE h_requesttype IN ('Change Request', 'Release')  
AND h_status='status.open' 
AND h_start_time >= CURDATE() + INTERVAL 1 DAY
AND h_end_time < CURDATE() + INTERVAL 2 DAY

Many thanks,
Alisha

[Bob Dickinson]

Hi @Alisha

I don't believe you would need JOINS to achieve this as the request type is stored in the main request table. 
Does the following return what you need?

SELECT h_pk_reference, h_fk_priorityname, h_summary, h_fk_team_name, DATE_FORMAT(h_fixby, '%a %D %b %Y - %H:%i') AS 'Fix By', 
REPLACE(REPLACE(h_status, 'status.open', 'Open'), 'status.onHold', 'On-Hold') AS 'Status'
FROM h_itsm_requests 
WHERE h_requesttype IN("Change Request", "Release")
AND h_status IN ("status.new", "status.onhold", "status.open") 
ORDER BY h_pk_reference DESC

Kind regards

Bob

[Alisha]

Hi @Bob Dickinson,

Yes, that's what we are after, to display Changes and Releases in one widget. We would like to display them according to the Start Time. We would only want today's and tomorrow's to show. Is this possible please?

Thank you very much for your help.

Kind regards,
Alisha

[Bob Dickinson]

Hi @Alisha

Ok sorry I missed that bit. In that case you DO need the joins. 
Try this one - this returns any Changes or Releases that are due to start AND end between the Start of Today and the End of Tomorrow:

SELECT a.h_pk_reference, a.h_fk_priorityname, a.h_summary, a.h_fk_team_name, DATE_FORMAT(a.h_fixby, '%a %D %b %Y - %H:%i') AS 'Fix By', 
REPLACE(REPLACE(a.h_status, 'status.open', 'Open'), 'status.onHold', 'On-Hold') AS 'Status', b.h_start_time as ChangeStart, b.h_end_time as ChangeEnd, c.h_start_time as ReleaseStart, c.h_end_time as ReleaseEnd
FROM h_itsm_requests a
LEFT JOIN h_itsm_changerequests b ON a.h_pk_reference = b.h_fk_reference
LEFT JOIN h_itsm_releases c ON a.h_pk_reference = c.h_fk_reference
WHERE a.h_requesttype IN("Change Request", "Release")
AND a.h_status IN ("status.new", "status.onhold", "status.open") 
AND 
((b.h_start_time >= CURDATE() AND b.h_end_time < DATE_ADD(CURDATE(),INTERVAL 2 DAY)) OR (c.h_start_time >= CURDATE() AND c.h_end_time < DATE_ADD(CURDATE(),INTERVAL 2 DAY)))
ORDER BY a.h_pk_reference DESC

 

Kind Regards

Bob

[Alisha]

That's just what we need! Thank you @Bob Dickinson

I don't suppose there's a way to combine the Start Time and End Time for the Changes and Releases so they're both under the relevant column (so we don't have 4 columns in total for those)?

[Bob Dickinson]

Hi @Alisha

Glad it's helped 

In answer to your questions, not that I'm aware of unfortunately - they are two separate columns in two separate tables so I believe they cannot be merged/displayed as one