Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

How can i clean the opened tickets?

Created: 18 Feb 2013 • Updated: 20 Feb 2013 | 12 comments
brunoyep's picture
This issue has been solved. See solution.

Hi everybody

I have 98 tickets in my Servicedesk and I need to clean all tickets to starts the use of the system in the company

How can i do this?

PS: I don't want to Close the tickets, I just need to clean the tickets and return to the ticket IM-000001

Regards,

Bruno Bacelar

Comments 12 CommentsJump to latest comment

brunoyep's picture

I found some informations to delete EM tickets, but I need to delete the IM

The code for EM is:

DECLARE @Process_ID nvarchar(50),
@Session_ID nvarchar(36)
 
SET @Process_ID = 'XX-######'  --Specific Process ID to be deleted.
SET @Session_ID = (SELECT SessionID FROM ReportProcess WHERE ReportProcessID = @Process_ID)
DELETE FROM ReportProcess WHERE SessionID = @Session_ID
DELETE FROM ReportProcessStatusHistory WHERE SessionID = @Session_ID
DELETE FROM Task WHERE SessionID = @Session_ID
DELETE FROM [Messages] WHERE MessageID = @Session_ID

Bruno Bacelar

brunoyep's picture

I got this error:

Msg 208, Level 16, State 1, Line 5
Invalid object name 'ReportProcess'.
 
I'm not good with SQL..
I need help yet!

Bruno Bacelar

reecardo's picture

When you open a new Query Window, you have to change the database used from master to ProcessManager. You can do this in the dropdown, or just type the directive before any commands:

USE ProcessManager;

brunoyep's picture

It's Working!

I need to exclude one by one, but that's OK!

Tks Everybody

The final Code is it:

USE ProcessManager
DECLARE @Process_ID nvarchar(50),
@Session_ID nvarchar(36)
 
SET @Process_ID = 'IM-000096'  --Specific Process ID to be deleted.
SET @Session_ID = (SELECT SessionID FROM ReportProcess WHERE ReportProcessID = @Process_ID)
 
 
DELETE FROM ReportProcess WHERE SessionID = @Session_ID
DELETE FROM ReportProcessStatusHistory WHERE SessionID = @Session_ID
DELETE FROM Task WHERE SessionID = @Session_ID
DELETE FROM [Messages] WHERE MessageID = @Session_ID

Bruno Bacelar

TGiles's picture

Unfortunately Bruno this doesn't specifically return your IM generation back to IM-000001. Looks like you have missed some additional tables that should be cleaned up specifically ServiceDeskIncidentManagement.

I've attached a SQL script to the post that's designed to purge all SD data from a database. It was originally written for 7.0, but it also works on 7.1 versions as well.

AttachmentSize
SD70.Purge_.updated.txt 1.95 KB
Lark's picture

I don't supose that script would work on 7.5 would it?

brunoyep's picture

Hi TGiles!

I got this error when I execute this code:

Msg 208, Level 16, State 1, Line 7
Invalid object name 'ReportProcess'.
 
You know if this is common?
 
Regards,
 
Bruno Bacelar

Bruno Bacelar

reecardo's picture

Flip the Database from master to ProcessManager

brunoyep's picture

Tks reecardo, tks TGiles.

The Database was cleaned!

Bruno Bacelar

brunoyep's picture

The final code is:

USE ProcessManager

SET NOCOUNT ON
 
DECLARE @SessionID NVARCHAR(36)
DECLARE @TaskID NVARCHAR(36)
 
DECLARE curSDPurge CURSOR FOR
SELECT Sessionid
FROM ReportProcess
WHERE ProjectName LIKE '%SD.%'
 
DECLARE curTaskPurge CURSOR FOR
SELECT TaskID
FROM Task
WHERE UrlofProcess LIKE '%SD.%'
OR [Name] IN ('Reopen Incident', 'Postponement')
 
OPEN curSDPurge
FETCH NEXT FROM curSDPurge INTO @SessionID
 
WHILE (@@FETCH_STATUS=0)
BEGIN
 
DELETE FROM ReportProcess
WHERE SessionID = @SessionID
 
DELETE FROM ReportProcessComment
WHERE SessionID = @SessionID
 
DELETE FROM ReportProcessContact
WHERE SessionID = @SessionID
 
DELETE FROM ReportProcessHistory
WHERE SessionID = @SessionID
 
DELETE FROM ReportProcessPermission
WHERE SessionID = @SessionID
 
DELETE FROM ReportProcessReference
WHERE SessionID = @SessionID
 
DELETE FROM ReportProcessStatusHistory
WHERE SessionID = @SessionID
 
DELETE FROM ReportProcessTiming
WHERE SessionID = @SessionID
 
FETCH NEXT FROM curSDPurge INTO @SessionID
END
 
OPEN curTaskPurge
FETCH NEXT FROM curTaskPurge INTO @TaskID
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
DELETE FROM TaskAssignment
WHERE TaskID = @TaskID
 
DELETE FROM TaskHistory
WHERE TaskHistoryID = @TaskID
 
DELETE FROM TaskResponse
WHERE TaskID = @TaskID
 
DELETE FROM Task
WHERE TaskID = @TaskID
 
FETCH NEXT FROM curTaskPurge INTO @TaskID
END
 
DELETE FROM ReportProcessRelationship
WHERE ParentProcessID LIKE 'IM-%'
OR ChildProcessID LIKE 'IM-%'
 
DELETE FROM ServiceDeskIncidentManagement
DELETE FROM ServiceDeskIncidentTemplate
DELETE FROM ServiceDeskOverAllSLASchedules
DELETE FROM ServiceDeskSLA
DELETE FROM ServiceDeskSLALevelManagement
DELETE FROM ServiceDeskSurveyData
 
DELETE FROM ReportProcessGenerator
WHERE [Type] LIKE '%M-%'
OR [Type] = 'SURVEY-'
 
DELETE FROM [Messages]
WHERE QueueName LIKE '%incident%'
 
CLOSE curSDPurge
DEALLOCATE curSDPurge
 
CLOSE curTaskPurge
DEALLOCATE curTaskPurge
 
 
With this code, is possible to clean all tickets and return to the ticket IM-000001
 
Regards,
 
Bruno Bacelar

Bruno Bacelar

SOLUTION
BRING's picture

All,

The solution should have a BEGIN TRANSACTION at the beginning of the script, and an END TRANSACTION statement at the end.  This will more accurately protect the data during the process.  If it crashes for some reason mid stream, you can ROLLBACK TRANSACTION and start over.

Without wanting to rain on the hard work and research done by the participants on this thread, I want to make everyone aware that running these types of queries does have some serious potential for destroying data in your environment.  Please backup your data and run these queries very judiciously. I would suggest that this solution only be run in a test environment, not in production.  

Regards,

Brent Ring