BMC Remedy Assigned Group Incident Transfer(s) Query

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

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

Rename SQL Server 2008 R2 Reporting Server Databases

 Computer, Software  Comments Off on Rename SQL Server 2008 R2 Reporting Server Databases
Jun 132012
 

Had a task to move the default SQL Reporting Services ReportServer and ReportServerTempDB databases to a new SQL cluster which required a rename to avoid any potential conflicts.

These instance of the databases are being used in Sharepoint-Integrated Mode. You could do Step 7 below before you do Steps 4 – 6.

1. Stop old SSRS

2. Detach ReportServer, ReportServerTempDB

3. Rename the MDF/LDF files; in this case ReportServer_MySite, ReportServer_MySiteTempDB

4. Copy/Attach these databases into new SQL server.

5. In my case, SSRS is Sharepoint-Integrated. In Central Admin; modify your Reporting Services integration as applicable.

6. Make sure new SSRS Configuration Manager is pointing to new database names.

7. Drop/Create script all Stored Procedures and the ExtendedCatalog Function and the ExtendedDatasource and Extended DataSet views.

Now for happy fun-time.

If it’s not enabled already; enable xp_cmdshell

EXECUTE SP_CONFIGURE ‘show advanced options’, 1
RECONFIGURE WITH OVERRIDE
GO

EXECUTE SP_CONFIGURE ‘xp_cmdshell’, ‘1’
RECONFIGURE WITH OVERRIDE
GO

EXECUTE SP_CONFIGURE ‘show advanced options’, 0
RECONFIGURE WITH OVERRIDE
GO

Execute the following Stored Procedure “FindandReplace”; this will find all references to your old ReportServerTempDB in the existing Stored Procedures in ReportServer.

USE [ReportServer_MySite]
GO

/****** Object: StoredProcedure [dbo].[FindAndReplace] Script Date: 06/12/2012 14:00:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[FindAndReplace]
(
@filepath varchar(500) =’c:temp’, — output filepath
@myfind varchar(500) = ‘ReportServerTempDB’, — keyword to be found
@myreplace varchar(500) = ‘ReportServer_MySiteTempDB’ — replacement word
)
as
set nocount on
declare @cmd varchar(8000), @myobject_id int, @myproc varchar(100), @checksum0 int, @checksum1 int

/*
Procedure to identify any stored procedures, in a given database, with a given keyword
BCPs an “alter” script to modify the proc with the replacement word
Also BCPs generates a “backup” script
Compatible with SQL2005 only
Louis Nguyen 2008
*/

— BCP requires permanent tables
create table temptable0 (definition nvarchar(max))
create table temptable1 (definition nvarchar(max))

— init filepath
if right(@filepath,1) <> ”
select @filepath = @filepath + ”

— cursor over procedures
declare mycur cursor fast_forward for
select b.object_id, a.name
from sys.objects a
join sys.sql_modules b
on a.type=’p’ and a.object_id = b.object_id
where a.name <> ‘FindAndReplace’
open mycur
fetch next from mycur into @myobject_id, @myproc

— begin cursor
while @@fetch_status = 0 begin

— write to permanent table(s)
delete temptable0
insert temptable0(definition)
select definition from sys.sql_modules
where object_id= @myobject_id

delete temptable1
insert temptable1(definition)
select replace(definition,@myfind,@myreplace) from sys.sql_modules
where object_id= @myobject_id

— compare checksums
select @checksum0=checksum(definition) from temptable0
select @checksum1=checksum(definition) from temptable1

if @checksum0<>@checksum1 begin

print ‘!! MATCH FOUND: ‘ + @myproc

— bcp out unadulterated contents
select @cmd=’bcp “select definition from ‘+db_name()+’..temptable0” queryout “‘
+@filepath
+replace(@@servername,”,’_’) +’_’ +db_name()
+’____’+@myproc + ‘.sql’
+’.backup”‘
+’ -S’+@@servername
+’ -T -c’
exec master.dbo.xp_cmdshell @cmd, no_output

— add use database & drop procedure to beginning of file
delete temptable1
insert temptable1
select ‘use [‘+db_name()+’]’
union all
select ‘go’
union all
select ‘drop procedure [‘+@myproc +’]’
union all
select ‘go’
union all
select replace(definition,@myfind,@myreplace) from temptable0

— bcp out modified contents
select @cmd=’bcp “select definition from ‘+db_name()+’..temptable1” queryout “‘
+@filepath
+replace(@@servername,”,’_’) +’_’ +db_name()
+’____’+@myproc + ‘.sql’
+'”‘
+’ -S’+@@servername
+’ -T -c’
exec master.dbo.xp_cmdshell @cmd, no_output

end
else begin
print ‘No match found: ‘ + @myproc
end

— end cursor
fetch next from mycur into @myobject_id, @myproc
end
close mycur
deallocate mycur

— cleanup
drop table temptable0
drop table temptable1

GO

Once the SP is created; Execute it.

You will be prompted for a temporary path, eg. C:temp and your string to look for: “ReportServerTempDB” and the value you want to change it to: “ReportServer_MySiteTempDB”

This will backup existing SPs and create new SPs scripts with the new value.

From C:temp call all the new scripts into SQL Management Studio and execute.

You will manually need to modify the Function and View mentioned above.