Colour Code VIP Incidents/Requests

Alex_Marsh

Had a request from a customer whereby if a user is marked as a VIP in the CMDB then the ticket for that user should be highlighted within the my work/team work/active work views. Is this possible?

Martin_Blomgren

On the top of my head you have the following choices I think:

(All based on JS)
1. A static list which is used to check if the affected user is VIP and then update CSS.
2. An  API call per WI to check if affected user is VIP and then update CSS.
3. Create your own webservice which returns this info and then update CSS.

Nicholas_Velich

All great suggestions from @Martin_Blomgren above. No matter the solution, it will use JavaScript. The difficult part here is that these grids bring in a canned set of data, so you can only work with what is available (mostly in the columns you see).

If your expertise lies more on the PowerShell side than the web-side, you could instead incorporate a PowerShell/Orchestrator runbook that sets the "Priority" field of the corresponding Work Item to "VIP" (or similar) when the corresponding Affected User is considered a VIP. That way, the only JavaScript bits you would need to write would be to highlight a row when "VIP" is present in the "Priority" column. You could also take a similar approach by concatenating the "Title" field with some sort of keyword, or use another out-of-box field.

Thanks,
Nick

Martin_Blomgren

Great suggestions from @Nicholas_Velich which will take the load of the portal server, perhaps you could go for a Powershell workflow which triggers on the affected user relationship. The JS part is of course the same here as well.

Alex_Marsh

Thanks guys, will have a play around with these and go from there

Tom_Hendricks

This is good advice that I do not mean to take anything away from, but as @Nicholas_Velich points out above, the portal uses canned data for those views (and similarly, for work item search, if you wanted to create your own).  This is at the root of many questions, irritations, and negative feedback that I receive with increasing frequency from our users.

Being able to provide extended properties and related object properties--two or three at the absolute most--would turn most of my dissatisfied users into satisfied users.  Promoted console views can do this, but do not work on mobile.  Dashboard views can do this, but do not contain the activity or inactive checkbox filters.  SSRS reports against the data warehouse can do this, but are not current and are not even technically part of the portal.  The suggestions here may work (and I hope they do--good luck @Alex_Marsh), but it is something that we frankly should not have to do in my opinion.

Is there any chance that the root cause could get addressed, rather than having to come up with complicated methods of using external systems to compensate for this?  There are already feature requests out there for this, I believe.

Alex_Marsh

Completely agree with @Tom_Hendricks here. The ability to add a custom column from existing accessible data in the default views would make most issues go away

Alex_Marsh

Finally got the authorisation to go ahead and start developing this item, have to say it's pretty much impossible to use anything in the default grid views to target this (a VIP priority for example is fine for SR but doesn't work for IR and having a custom priority (i.e. 6) when most are already taken will look odd to analysts and users. I would imagine the code to evaluate each work item in the page to check if the user is a VIP, or getting the urgency value from the displayed work item would be fairly substantial (and very heavy on data usage).

Any further suggestions from anyone? The gridviews are now becoming a fairly big limitation in moving things forward

Alex_Marsh

Seem to have fallen at the first hurdle, I can't seem to find a way of getting the work item properties by looking up the ID (i.e. SR1234) as the getobjectproperties requires the guid which isn't visible in the grid view.
I believe if I can do this I can lookup each work item that has an urgency (for example) of VIP and apply the respective colour coding to a designated field

Alex_Marsh

Right, some progress!

I've managed to come up with the following based on the post here which is working, however I'm having issues attaching it to a mutation observer so it refreshes with the gridview refresh. Any help on that would be appreciated!

function SetStatusPriorityColors() {
	var $headers = $('table thead tr th.k-header');
	if ($headers === undefined || $headers.length == 0) { return }
	
	var count = 0;
	var idLoc = 0;
	$headers.each(function() {
		count++;
		var $this = $(this);
		var $dText = $this.find('.k-link');
		//alert($dText)
		if ($dText.text().indexOf('ID') ==0 ) {
			idLoc = count;
			//alert(idLoc)
		}
	});
	//var idLoc = 2
	var $tRows = $('table tbody tr');
	if (idLoc == 0 || $tRows === undefined || $tRows.length == 0 ) { return }
		
	$tRows.each(function() {
		var $this = $(this);
		if (idLoc > 0) {
			var $pCell = $($this.find('td')[idLoc -1]);
			//alert($pCell.text());
			var workItemId = $pCell.text()
			if (workItemId.indexOf("IR") >= 0) {
                        projectionTypeId = "2d460edd-d5db-bc8c-5be7-45b050cba652";
                    } else if (workItemId.indexOf("SR") >= 0) {
                        projectionTypeId = "7ffc8bb7-2c2c-0bd9-bd37-2b463a0f1af7";
                    }
			$.getJSON('/api/V3/Projection/GetProjection', 
                    { 
                    "id": workItemId,
                    "typeProjectionId": projectionTypeId
                    },
					function (data){
					var x = a(data)
					}
					);
	var a = function(x){
		if(x.Urgency.Name == "VIP"){
	//alert(x.Title);
	$("[role=gridcell]:Contains('" + x.Title + "')").css("background-color", "red", "!important");};
			}
		}
})
};

Nicholas_Velich

Hi Alex,

I'm not sure how well a mutation observer would work here, as there isn't a clear-cut element to monitor to determine if the data refresh has occurred. You might end up executing your code much more than intended with this approach.

As an alternative, if your issue is only getting the customization to re-apply after a grid refresh (this occurs every 60 seconds by default, but is a configurable setting), the simple solution might be to use an interval in line with that refresh: https://www.w3schools.com/jsref/met_win_setinterval.asp

As another alternative, you could look into the kendo grid's change events: https://demos.telerik.com/kendo-ui/grid/events

Thanks,
Nick

Tom_Hendricks

the literal "change" event fires when a row is selected in the grid by the user.  (So intuitive, right...?)

I believe you would want to look at the .databound event instead.  My assumption here is that a .fetch() is performed when the grid is refreshed, but that is only a guess.

That said, I have had difficulties overriding these events (also with removing them, etc.--it's like they don't exist) in the dashboards, so hopefully you do not encounter that here.  I ended up needing to kill the pointerdown event on the table element instead of interacting with the kendo control the way you are "supposed to."  I do not have this issue with any other kendo controls in the portal, so there clearly must be a reason for it.

Alex_Marsh

Yeah the other option (which was working) was just to set it up on a timer so might just stick with that and see how we get. I've also discovered that I should be able to access the VIP parameter directly so may be able to skip the workflow element. Just waiting to load the data into the DB.

Jeff_Lang

i actually was trying something very similar to this the last couple weeks, and we put it into production on Thursday. what we were doing was adding an extra icon before the workitem icon to indicate it was a VIP incident. all our testing etc seemed to go well, but withing 3 hours of it going to production and it being used, we found the cpu usage on IIS and Service Manager processes went through the roof as it was going out to check each line to see if it needed to set the vip icon.  we did have it set to only check if the grid view itself was changed, and to only check on incidents, not SR's or CR's.

for now we've removed it from production again and gone back to the drawing board to come up with a different way to do this, so that the server cpu doesn't max out and slow everyone using the portal down.

Alex_Marsh

Jeff_Lang said:

i actually was trying something very similar to this the last couple weeks, and we put it into production on Thursday. what we were doing was adding an extra icon before the workitem icon to indicate it was a VIP incident. all our testing etc seemed to go well, but withing 3 hours of it going to production and it being used, we found the cpu usage on IIS and Service Manager processes went through the roof as it was going out to check each line to see if it needed to set the vip icon.  we did have it set to only check if the grid view itself was changed, and to only check on incidents, not SR's or CR's.

for now we've removed it from production again and gone back to the drawing board to come up with a different way to do this, so that the server cpu doesn't max out and slow everyone using the portal down.

How were you checking the grid view changed? That seems to be the thing I'm having an issue with currently is attaching to it. I managed to use the smallest type projection possible (work item and affected user) which seemed OK in testing but based on what you've stated could be an issue in a live environment.
Might have to go back to the workflow as I'd (hope) that just looking up the work item only (no affected user) would be more efficient, which was the original plan but I couldn't find a flat incident or service request projection to use!

Jeff_Lang

we actually have the incidents extended with a flag (Boolean) to say they are vip incidents, so were just looking at the incident workitems themselves with nothing else related. even with this it hammered the server cpu.

as to the grid changing, we cheated massively and had a procedure run periodically, that got the html for the grid, and checked if it had changed from the previous html, yes i know it's not the best way to do it, but could come up with nothing else in the time i had to achieve what was wanted. before checking the html, the extra icon and rows being selected etc were removed from the copy of the html being checked, so that it only activated the checking of the workitems themselves if the items displayed actually changed

Alex_Marsh

That's not good. Might just have to have a workflow append [VIP] to the ticket title and just have a piece of code which looks for it on the gridview and marks it.
It's frustrating when all this could be avoided just by being able to add a custom column to the gridview (even something standard like urgency!)

Alex_Marsh

Cracked it! Just need to perform some higher volume testing to make sure it doesn't swallow too much data

$(document).ready(function(){
	if(session.user.Analyst){
 var url = window.location.href;
        // Verify we are on team work or active work page with a gridview.
        if(url.indexOf("f94d43eb-eb42-4957-8c48-95b9b903c631") > -1 || url.indexOf("62f452d5-66b5-429b-b2b1-b32d5562092b") > -1 || url.indexOf("9a06b0af-2910-4783-a857-ba9e0ccb711a") > -1) { 
            //The navigation node doesn't load immediately. Get the main div that definitely exists.
            var mainPageNode = document.getElementById('main_wrapper');
            
            // create an observer instance
            var assignToMeObserver = new MutationObserver(function(mutations) {
                //The page changed. See if our title exists. If it does, then our gridview should also exist.
                var titleElement = $(".page_title"); //The title always exists. If this page has a gridview, then it also exists as soon as the title does.
                
                if (titleElement.length > 0) { //An element with class of page_title exists.
                    var gridElement = $('[data-role=grid]') // Get the grid object
                    if (gridElement.length > 0) {
                        VIPEventHandler(gridElement);
                    }
                    
                    //We are done observing.
                    assignToMeObserver.disconnect();
                }
                
            });
            
            // configure the observer and start the instance.
            var observerConfig = { attributes: true, childList: true, subtree: true, characterData: true };
            assignToMeObserver.observe(mainPageNode, observerConfig);
        }

    var VIPEventHandler = function(gridElement) {

        var kendoGridElement = gridElement.data('kendoGrid'); //...as a kendo widget
        
        //Whenever the data changes, add assign button again.
        kendoGridElement.bind("dataBound", SetVIPColor); 
        //Note to self. the dataBound fires after the rows are returned, and dataBinding fires before. 
    }

    var SetVIPColor = function() {
	var $headers = $('table thead tr th.k-header');
	if ($headers === undefined || $headers.length == 0) { return }
	
	var count = 0;
	var idLoc = 0;
	var titleLoc = 0;
	$headers.each(function() {
		count++;
		var $this = $(this);
		var $dText = $this.find('.k-link');
		//alert($dText)
		if ($dText.text().indexOf('ID') ==0 ) {
			idLoc = count;
		} else if ($dText.text().indexOf('Title') ==0 ) {
			titleLoc = count;
		}
	});
	//var idLoc = 2
	var $tRows = $('table tbody tr');
	if (idLoc == 0 || $tRows === undefined || $tRows.length == 0 | $tRows.length == 0) { return }
		
	$tRows.each(function() {
		var $this = $(this);
		if (idLoc > 0) {
			var $pCell = $($this.find('td')[idLoc -1]);
			//alert($pCell.text());
			var workItemId = $pCell.text()
			if (workItemId.indexOf("IR") >= 0) {
                        projectionTypeId = "2d460edd-d5db-bc8c-5be7-45b050cba652";
                    } else if (workItemId.indexOf("SR") >= 0) {
                        projectionTypeId = "e2d1b62b-939f-f17f-9d8e-462083b44d6a";
                    } else { return }
			$.getJSON('/api/V3/Projection/GetProjection', 
                    { 
                    "id": workItemId,
                    "typeProjectionId": projectionTypeId
                    },
					function (data){
					var x = a(data)
					});
	var a = function(x){
		if(x.RequestedWorkItem){
		if(x.RequestedWorkItem.IsVIP){
	//alert(x.Title);
		//$("[class=gridcell]:Contains('" + x.Id + "')").css("background-color", "red !important");
		$pCell.parent().children().css("background-color", "yellow !important")
		}};
			}
		}
});
};
}});

We extended the Domain User and Group class with the VIP field and retrieve it using a simple affected user and work item only class. As we only want this for analyst sessions I've put in additional filters to make sure it doesn't run for an end user in the hope of keeping the volume down

Nicholas_Velich

If you are seeing performance issues using some of the "Get Projection" types of calls at scale, you might want to consider switching the call you are using. The v7 versions of the Portal introduced the new Dashboard API and dashboard queries, which are particularly useful in this case as they put the workload on SQL, rather than SCSM and the SDK service (which is why you are seeing the CPU spike).

In this case, I would first create a SQL query that gets exactly the data you need, from any combination of the ServiceManager and ServiceManagement databases (assuming they are on the same server). Next, insert that SQL query into the ServiceManagement database (/Settings/Admin/DashboardQuerySettings via the UI). Finally, use the "api/V3/Dashboard/GetDashboardDataById" call with the queryId parameter to invoke that query and get your data.

The advantage of doing the above is (1) all the SQL joins and data gathering is done in SQL itself, which is more efficient, and (2) you only get the data you actually need, as you are not bringing in undesired relationships, nor undesired properties.

Alex_Marsh

Nicholas_Velich said:

If you are seeing performance issues using some of the "Get Projection" types of calls at scale, you might want to consider switching the call you are using. The v7 versions of the Portal introduced the new Dashboard API and dashboard queries, which are particularly useful in this case as they put the workload on SQL, rather than SCSM and the SDK service (which is why you are seeing the CPU spike).

In this case, I would first create a SQL query that gets exactly the data you need, from any combination of the ServiceManager and ServiceManagement databases (assuming they are on the same server). Next, insert that SQL query into the ServiceManagement database (/Settings/Admin/DashboardQuerySettings via the UI). Finally, use the "api/V3/Dashboard/GetDashboardDataById" call with the queryId parameter to invoke that query and get your data.

The advantage of doing the above is (1) all the SQL joins and data gathering is done in SQL itself, which is more efficient, and (2) you only get the data you actually need, as you are not bringing in undesired relationships, nor undesired properties.

That does seem like a much better way of doing it, however from looking at the API documentation I can't see how to add the required query filters in (i.e. the work item ID).
In this instance I would think the most efficient method would be to lookup the affected user via the work item ID and return the VIP value for that user from the servicemanager database and then apply the necessary formatting

Nicholas_Velich

You can add custom variables in the SQL query that are passed into the API call. For example, if you had the following query:

SELECT *
FROM WorkItem
WHERE WorkItemId = '{{placeholder}}'

You could then call that query as follows:

/api/V3/Dashboard/GetDashboardDataById?queryId=GUIDGOESHERE&placeholder=IR1234

Alex_Marsh

I can't get it to work to even test it

Query

select u.IsVIP_FC8E509D_4AE8_A098_AB09_E9FA18BB471E from RelationshipView r
inner join MTV_System$Domain$User u on r.TargetEntityId=u.BaseManagedEntityId and r.RelationshipTypeId = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE' and r.IsDeleted = 0
left join MTV_System$WorkItem$Incident i on r.SourceEntityId = i.BaseManagedEntityId
left join MTV_System$WorkItem$ServiceRequest sr on r.SourceEntityId = sr.BaseManagedEntityId
where i.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C = '{{placeholder}}' or sr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C = '{{placeholder}}'

JS

var workItemId = "T-SR10701"
var queryid = "8ed0b9f6-2c99-932d-1d38-c64538b05d7a"

$.getJSON('/api/V3/Dashboard/GetDashboardDataById', 
                    {"query": queryid
					"placeholder": workItemId
                    });

Just getting error, expected } every time

Nicholas_Velich

You are missing a comma between "queryid" and "placeholder"

Alex_Marsh

Eugh, stupid mistake, new error is "Unterminated string constant"

Roland_Kind

Hi,

maybe a "," is missing between the two parameters...

$.getJSON('/api/V3/Dashboard/GetDashboardDataById', {"query": queryid , "placeholder": workItemId });

regards

Roland

Nicholas_Velich

One other issue you have in the JavaScript is the parameter should be "queryId," not "query."

I suggest starting small and replicate my example above. Once you get that working, you can turn your attention towards the SQL query itself. Not all SQL syntax will work through this call, so if you have a complicated query (lots of JOINs, aliases, UNIONS, etc.), I suggest inserting the finalized query as a SQL view, and just doing a simple SELECT statement from the view instead.

Alex_Marsh

Ignore me, can't see wood for the trees today been at this so long, all good now I think, though it did seem much slower than the original implementation. At least now I have options

Alex_Marsh

Right so the query is returning [{"VIP":null}] but when I go to use it I always get undefined. My adjusted js is as follows:

$(document).ready(function(){
	if(session.user.Analyst){
 var url = window.location.href;
        // Verify we are on team work or active work page with a gridview.
        if(url.indexOf("f94d43eb-eb42-4957-8c48-95b9b903c631") > -1 || url.indexOf("62f452d5-66b5-429b-b2b1-b32d5562092b") > -1 || url.indexOf("9a06b0af-2910-4783-a857-ba9e0ccb711a") > -1) { 
            //The navigation node doesn't load immediately. Get the main div that definitely exists.
            var mainPageNode = document.getElementById('main_wrapper');
            
            // create an observer instance
            var assignToMeObserver = new MutationObserver(function(mutations) {
                //The page changed. See if our title exists. If it does, then our gridview should also exist.
                var titleElement = $(".page_title"); //The title always exists. If this page has a gridview, then it also exists as soon as the title does.
                
                if (titleElement.length > 0) { //An element with class of page_title exists.
                    var gridElement = $('[data-role=grid]') // Get the grid object
                    if (gridElement.length > 0) {
                        VIPEventHandler(gridElement);
                    }
                    
                    //We are done observing.
                    assignToMeObserver.disconnect();
                }
                
            });
            
            // configure the observer and start the instance.
            var observerConfig = { attributes: true, childList: true, subtree: true, characterData: true };
            assignToMeObserver.observe(mainPageNode, observerConfig);
        }

    var VIPEventHandler = function(gridElement) {

        var kendoGridElement = gridElement.data('kendoGrid'); //...as a kendo widget
        
        //Whenever the data changes, add assign button again.
        kendoGridElement.bind("dataBound", SetVIPColor); 
        //Note to self. the dataBound fires after the rows are returned, and dataBinding fires before. 
    }

    var SetVIPColor = function() {
	var $headers = $('table thead tr th.k-header');
	if ($headers === undefined || $headers.length == 0) { return }
	
	var count = 0;
	var idLoc = 0;
	var titleLoc = 0;
	$headers.each(function() {
		count++;
		var $this = $(this);
		var $dText = $this.find('.k-link');
		//alert($dText)
		if ($dText.text().indexOf('ID') ==0 ) {
			idLoc = count;
		} else if ($dText.text().indexOf('Title') ==0 ) {
			titleLoc = count;
		}
	});
	//var idLoc = 2
	var $tRows = $('table tbody tr');
	if (idLoc == 0 || $tRows === undefined || $tRows.length == 0 | $tRows.length == 0) { return }
		
	$tRows.each(function() {
		var $this = $(this);
		if (idLoc > 0) {
			var $pCell = $($this.find('td')[idLoc -1]);
			//alert($pCell.text());
			var workItemId = $pCell.text()
			if (workItemId.indexOf("IR") >= 0 || workItemId.indexOf("SR") >= 0 ){
                        var queryid = "8ed0b9f6-2c99-932d-1d38-c64538b05d7a";
                    } else { return }
			$.getJSON('/api/V3/Dashboard/GetDashboardDataById', 
                    { 
                    "queryId": queryid,
                    "wi": workItemId
                    },
					function (data){
					var x = data
					if ('' !=x){
					console.log(x["vip"])
					if(x.vip){
						$pCell.parent().children().css("background-color", "yellow !important")
					}
					}});
			}
		})
};
};
});

logging to the console returns an object array but I can't address the VIP object inside it

Roland_Kind

Hi,

can you post the result from the query ? - just as an idea - is it a json string which had to be converted to a js object (JSON.parse ?)

regards

edit: just saw your frist line: --> JSON.parse('[{"VIP":null}] ') - will create an JS object

Alex_Marsh

I've cracked it now by addressing the array directly with the x[0].vip element in the script below.

$(document).ready(function(){
	if(session.user.Analyst){
 var url = window.location.href;
        // Verify we are on team work or active work page with a gridview.
        if(url.indexOf("f94d43eb-eb42-4957-8c48-95b9b903c631") > -1 || url.indexOf("62f452d5-66b5-429b-b2b1-b32d5562092b") > -1 || url.indexOf("cca5abda-6803-4833-accd-d59a43e2d2cf") > -1) { 
            //The navigation node doesn't load immediately. Get the main div that definitely exists.
            var mainPageNode = document.getElementById('main_wrapper');
            
            // create an observer instance
            var assignToMeObserver = new MutationObserver(function(mutations) {
                //The page changed. See if our title exists. If it does, then our gridview should also exist.
                var titleElement = $(".page_title"); //The title always exists. If this page has a gridview, then it also exists as soon as the title does.
                
                if (titleElement.length > 0) { //An element with class of page_title exists.
                    var gridElement = $('[data-role=grid]') // Get the grid object
                    if (gridElement.length > 0) {
                        VIPEventHandler(gridElement);
                    }
                    
                    //We are done observing.
                    assignToMeObserver.disconnect();
                }
                
            });
            
            // configure the observer and start the instance.
            var observerConfig = { attributes: true, childList: true, subtree: true, characterData: true };
            assignToMeObserver.observe(mainPageNode, observerConfig);
        }

    var VIPEventHandler = function(gridElement) {

        var kendoGridElement = gridElement.data('kendoGrid'); //...as a kendo widget
        
        //Whenever the data changes, add assign button again.
        kendoGridElement.bind("dataBound", SetVIPColor); 
        //Note to self. the dataBound fires after the rows are returned, and dataBinding fires before. 
    }

    var SetVIPColor = function() {
	var $headers = $('table thead tr th.k-header');
	if ($headers === undefined || $headers.length == 0) { return }
	
	var count = 0;
	var idLoc = 0;
	var titleLoc = 0;
	$headers.each(function() {
		count++;
		var $this = $(this);
		var $dText = $this.find('.k-link');
		//alert($dText)
		if ($dText.text().indexOf('ID') ==0 ) {
			idLoc = count;
		} else if ($dText.text().indexOf('Title') ==0 ) {
			titleLoc = count;
		}
	});
	//var idLoc = 2
	var $tRows = $('table tbody tr');
	if (idLoc == 0 || $tRows === undefined || $tRows.length == 0 | $tRows.length == 0) { return }
		
	$tRows.each(function() {
		var $this = $(this);
		if (idLoc > 0) {
			var $pCell = $($this.find('td')[idLoc -1]);
			//alert($pCell.text());
			var workItemId = $pCell.text()
			if (workItemId.indexOf("IR") >= 0 || workItemId.indexOf("SR") >= 0 ){
                        var queryid = "8ed0b9f6-2c99-932d-1d38-c64538b05d7a";
                    } else { return }
			$.getJSON('/api/V3/Dashboard/GetDashboardDataById', 
                    { 
                    "queryId": queryid,
                    "wi": workItemId
                    },
					function (data){
					var x = data
					if ('' !=x){
					//console.log(x[0].VIP)
					if(x[0].VIP){
						$pCell.parent().children().css("background-color", "yellow !important")
					}
					}});
			}
		})
};
};
});

This way is definitely more efficient than the original method from the response times so I think I'll be sticking with this method for the time being unless something better comes along.

The network response to the query is: [{"VIP":null}] or [{"VIP":true}] and if I dump x to console.log I get [object array] and under element 0 is the data I require. Not sure how I put JSON.parse into here as when i've tried var x = JSON.parse(data) I just get a syntax error

Roland_Kind

ok - it is already an object - sorry, I didn't saw it 

so no need to use json.parse - only right formated string data can be converted to an object