Change Control Widget - filter on status

[QEHNick]

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!

[SamS]

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.

[NeilWJ]

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()

 

[QEHNick]

@NeilWJ

That is just pure magic Neil, thanks very much, just the ticket.

[QEHNick]

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')