Alisha Posted January 16, 2020 Posted January 16, 2020 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 Posted January 16, 2020 Author Posted January 16, 2020 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 Posted January 18, 2020 Posted January 18, 2020 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 Posted February 18, 2020 Author Posted February 18, 2020 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 Posted February 18, 2020 Posted February 18, 2020 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 1
Alisha Posted February 19, 2020 Author Posted February 19, 2020 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 Posted February 19, 2020 Posted February 19, 2020 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 1
Bob Dickinson Posted February 19, 2020 Posted February 19, 2020 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 1
Alisha Posted April 23, 2020 Author Posted April 23, 2020 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 Posted April 23, 2020 Posted April 23, 2020 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 1
Alisha Posted April 24, 2020 Author Posted April 24, 2020 @Bob Dickinson It works! That makes me so happy, thank you so much, Bob! It's been bugging me for so long! 1
Michael Sharp Posted May 4, 2020 Posted May 4, 2020 Thanks @Bob Dickinson , helped me with something else!! 1
Alisha Posted June 30, 2020 Author Posted June 30, 2020 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 Posted June 30, 2020 Posted June 30, 2020 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 1
Alisha Posted June 30, 2020 Author Posted June 30, 2020 @Bob Dickinson That's perfect! Thank you so much!
Alisha Posted June 22, 2021 Author Posted June 22, 2021 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 Posted June 23, 2021 Posted June 23, 2021 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 1
Alisha Posted June 24, 2021 Author Posted June 24, 2021 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 Posted June 24, 2021 Posted June 24, 2021 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 1
Alisha Posted June 24, 2021 Author Posted June 24, 2021 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 Posted June 24, 2021 Posted June 24, 2021 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
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now