Jump to content

Change Control Widget - filter on status


QEHNick
 Share

Recommended Posts

Ok, so I'm working on a widget to be displayed on a dashboard, this will give our service desk team an active list they can glance at (should the effluent hit the rotating blades) and gauges if it's linked to an ongoing Change.

Yes, I am aware of the change calendar, and yes, I know how easy it is to flick to it etc, but I want to give them a quick (literal) heads-up on the office flat screen.

Anyway, preamble done with, this is my SQL so far.

Select h_fk_reference AS Reference, h_custom_j AS 'Summary', DATE_FORMAT(h_start_time,'%d/%m/%Y @ %h:%m') AS 'Start Time', DATE_FORMAT(h_end_time,'%d/%m/%Y @ %h:%m') AS 'End Time', h_change_type AS 'Change Type'
FROM h_itsm_changerequests 
WHERE h_end_time > now()

My issue is that the table h_itsm_changerequests does not have any natural way of getting the status of the change request. I've had to fiddle getting the summary across as it is (hence the custom variable).

Can anyone guide me as to where I can get these absent fields (summary, Status etc.) which are not in the h_itsm_changerequests table?

 

Many thanks!

Link to comment
Share on other sites

Hi @QEHNick,

Unfortunately, it is currently not possible to connect to a second table within the measures. The table one would need is the h_itsm_requests table.

Another solution would be to allow the creation of measures based on "Entity" - a "Change Request" Entity would do the back-end database hook-up for you.

Either solution would be considered a Feature Request.

Link to comment
Share on other sites

Hi @QEHNick,

Are you using the advanced analytics - Datalist widget and using the raw sql option for that? If so something like like below where join on the h_istm_requests table to get h_summary and h_status...

Cheers

 

SELECT 	changereq.h_fk_reference AS Reference, 
			requesttbl.h_summary AS 'Summary', 
			requesttbl.h_status AS 'Status' , 
			DATE_FORMAT(changereq.h_start_time,'%d/%m/%Y @ %h:%m') AS 'Start Time', 
			DATE_FORMAT(changereq.h_end_time,'%d/%m/%Y @ %h:%m') AS 'End Time', changereq.h_change_type AS 'Change Type'
			
FROM h_itsm_changerequests AS changereq 
INNER JOIN h_itsm_requests AS requesttbl ON requesttbl.h_pk_reference=changereq.h_fk_reference
WHERE h_end_time > now()


 

Capture.PNG

Link to comment
Share on other sites

Made a final tweak to filter out the closed/cancelled ones. This is the final script. Once again, thanks very much Neil.

 

SELECT     changereq.h_fk_reference AS Reference, 
            requesttbl.h_summary AS 'Summary', 
            requesttbl.h_status AS 'Status' , 
            DATE_FORMAT(changereq.h_start_time,'%d/%m/%Y @ %h:%m') AS 'Start Time', 
            DATE_FORMAT(changereq.h_end_time,'%d/%m/%Y @ %h:%m') AS 'End Time', changereq.h_change_type AS 'Change Type'
            
FROM h_itsm_changerequests AS changereq 
INNER JOIN h_itsm_requests AS requesttbl ON requesttbl.h_pk_reference=changereq.h_fk_reference


WHERE h_end_time > now() AND requesttbl.h_status IN ('status.open', 'status.new')

  • Like 1
Link to comment
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
 Share

×
×
  • Create New...