BMC Remedy Assigned Group Incident Transfer(s) Query

 Computer, Software  Comments Off on BMC Remedy Assigned Group Incident Transfer(s) Query
Jan 222015
 
Share

Had a request from a customer for a report showing the number of Remedy incidents transferred from his managed group to other groups during a given date range.
I figured out a pseudo lead/lag query for SQL Server 2008. This creates an output that aligns the current row with the previous row. This allows you to see FROM which group an incident was transferred TO.

--- Just for testing; these will be the parameters for the SSRS report.
declare @StartDate datetime
declare @EndDate datetime
set @StartDate = '1/1/2015'
set @EndDate = '1/15/2015'

-- Get Incidents from Assignment Log where ET did something and put them into a temporary table.
select distinct(incident_number)
into #tempincidents
from View_HPD_Help_Desk_Assignment_Log
where assigned_group = 'Enterprise Telecomm'
and submit_date between @StartDate and @EndDate
order by incident_number

Here’s the magic, query the Assignment Log again using the Incident numbers from above and assign a row number.

select
incident_number
, Assignee
, assigned_group
, submit_date
, ROW_NUMBER() over(order by incident_number, submit_date) Serial
into #tempxfersA
from View_HPD_Help_Desk_Assignment_Log
where Incident_Number in (select * from #tempincidents)

select a.Incident_Number
-- 'Previous' Assigned Groups
, b.Assigned_Group as AGroup
, b.Assignee as AssigneeB
, b.Serial as SerialB
-- 'Current' Assigned Groups
, a.Assigned_Group as BGroup
, A.Assignee
, a.Serial
into #tempxfersB
from #tempxfersA a
-- Join the table to itself matching the Row number minus 1 and Incident number for quality.
left join #tempxfersA b on a.Serial - 1 = b.Serial and a.Incident_Number = b.Incident_Number
order by a.Incident_Number, a.submit_date

Your query output should look like
Incident Number, ‘First Assigned Group’, Assignee, Row # from Table ‘A’, ‘Second Assigned Group’, Assignee, Row # from Table ‘B’

Lets count them up… This returns the overall counts for Enterprise Telecomm incident transfers to another group.
select
--- Count where Enterprise Telecomm transferred an incident to another group.
COUNT(Case when AGroup = 'Enterprise Telecomm' and BGroup <> 'Enterprise Telecomm' then 1 end) as GroupXfers ,
--- While we are at it, count where a member of Enterprise Telecom transfered (re-assigned) an incident to someone else in the Telecomm group.
COUNT(Case when AGroup = 'Enterprise Telecomm' and BGroup = 'Enterprise Telecomm' and Assignee <> AssigneeB then 1 end) as InternalXfers
from #tempxfersB

And now let’s tally up transfers by the Assignee.
select
AssigneeB,
COUNT(Case when AGroup = 'Enterprise Telecomm' and BGroup <> 'Enterprise Telecomm' then 1 end) as GroupXfers ,
COUNT(Case when AGroup = 'Enterprise Telecomm' and BGroup = 'Enterprise Telecomm' and Assignee <> AssigneeB then 1 end) as InternalXfers
from #tempxfersB
group by AssigneeB

Caveats: This just shows transfers to other groups and not necessarily for resolution. In my case, transfers can happen multiple times between groups on one incident without resolution.

TransferTicketCounts

Populating Notes field from Cascading Dropdown Selections

 Computer, Sharepoint, Software  Comments Off on Populating Notes field from Cascading Dropdown Selections
Apr 302012
 
Share

This was a solution I figured out for populating a notes field from cascading drop-downs. I’ve had some folks e-mail me asking for the actual implementation.

In my scenario, I was creating a Sharepoint form front-end that would create an Incident Ticket in BMC Remedy.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Here’s what I did.

1. Created an initial Sharepoint list (this was for the first drop-down) where a user selected a top-level category for tech help. This was a one colum list with just “Title” that looked like this:

Hardware Failure
Install Software
Password Reset
Service Request

2. Created a second Sharepoint list (this was for the second drop-down) Based on the selection above, I display sub-categories. This list had 2 columns; Title, SumCat. SumCat matches Title from 1st list.

So based on above it looked like this:

Audio Conference Request, Service Request
Video Conference Request, Service Request
PC Bluescreen, Hardware Failure
Blackberry Broken, Hardware Failure

3. Created a third Sharepoint list (this is what populates the Notes field). Based on second-drop down, look up questions to help a tech know what the problem is.

This had two columns, Title, Body. Title matches Title column in 2nd list.

For example:

Audio Conference Request, then the Body column had this text.
Conference Name:
Host Name:
Host Phone:
Date:
Duration:
# of lines:

When the notes field is populated; users would enter the relevant information: See screenshot above.

4. Created a 4th Sharepoint list that holds the actual requests.

The columns are Category (lookup on 1st list), Summary (lookup on 2nd list), Notes (Multiple lines of text), OtherID (person or group).

Here’s the script; this was placed under PlaceHolderMain on the NewForm.aspx

<script language=”javascript” type=”text/javascript” src=”jquery.js”></script>
<script language=”javascript” type=”text/javascript” src=”jquery.SPServices-0.5.4.js”></script>
<script type=”text/javascript” language=”javascript”>

$(document).ready(function() {
// alert(‘Working!’);

$().SPServices.SPCascadeDropdowns({
relationshipList: “RC_Summary”,
relationshipListParentColumn: “SumCat”,
relationshipListChildColumn: “Title”,
parentColumn: “Category”,
childColumn: “Summary”,
debug: true

});

$().SPServices.SPDisplayRelatedInfo({
columnName: “Summary”,
relatedList: “RC_Notes”,
relatedListColumn: “Title”,
relatedColumns: [“Body”],
displayFormat: “notes”,
childColumn: “Notes”,
debug: true
});

});
</script>
<script language=”javascript” type=”text/javascript” >
function PreSaveAction() {
// alert(“PreSave”);
$(“input[name^=’fileupload’]”).each(function() {
if ($(this).val() != “”) {
// alert($(this).val());
var fpath = $(this).val();
var m = fpath.match(/(.*)[/\]([^/\]+.w+)$/);

// alert(m[2]);

$().SPServices({
operation: “UpdateListItems”,
async: false,
listName: “WorkInfo”,
updates: “<Batch OnError=’Continue’ PreCalc=’TRUE’>” +
“<Method ID=’1′ Cmd=’New’>” +
“<Field Name=’Title’>” + m[2] + “</Field>” +
“</Method>” +
“</Batch>”,
completefunc: function(xData, Status) {
// alert(“Status=” + Status + ” XML=” + xData.responseXML.xml);
}
});
// return true;

}
});
return true;
}</script>

I also created a new ‘CASE’ for SPDisplayRelatedInfo for the SPServices library. Note: This is hardcoded to look for a multiline textbox named “Notes”

// append to Notes field
case “notes”:
var outString = “Please provide supporting information:”; // This does display on page load
// Get relevant Notes from relatedlist

for (i=0; i < opt.relatedColumns.length; i++) {
$(xData.responseXML).find(“[nodeName=z:row]”).each(function() {

// outString += “* ” + relatedColumnsXML[i].attr(“DisplayName”) + ” *”;
outString += “* ” + showColumn(relatedColumnsXML[i], $(this).attr(“ows_” + opt.relatedColumns[i]), opt) + ” *”;

});
}

// alert(outString); Debugging

setTextFromFieldName(“Notes”, “” + outString + “”);

break;

// end of new ‘CASE’

At the end of the jquery.SPServices.js, I have added the following functions:

// Find the Notes field
function setTextFromFieldName(fieldName, value) {
if (value == undefined) return;
// alert(value);
var theTextBox = getTagFromIdentifierAndTitle(“textarea”,””,fieldName);theTextBox.value=value
}

function getTagFromIdentifierAndTitle(tagName, identifier, title) {

var len = identifier.length;

var tags = document.getElementsByTagName(tagName);

for (var i=0; i < tags.length; i++) {

var tempString = tags[i].id;

if (tags[i].title == title && (identifier == “” || tempString.indexOf(identifier) == tempString.length – len)) {

return tags[i];

}

}

return null;

}

// End of new functions