Video Screencast Help
Search Video Help Close Back
to help

How can i clean the opened tickets?

Created: 18 Feb 2013 | Updated: 20 Feb 2013 | 12 comments
brunoyep's picture
brunoyep Certified
0 0 Votes
Login to vote
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

Discussion Filed Under:
, , , , , , , , , , , , , ,

Comments 12 Comments • Jump to latest comment

Ashish-Sharma's picture
Ashish-Sharma Partner
How can i clean the opened tickets? - Comment:18 Feb 2013 : Link

Hi,

Check this thread may be help

https://www-secure.symantec.com/connect/forums/deleting-tickets-symantec-service-desk-71-sp2

Thanks In Advance

Ashish Sharma

SEPM Knowledgebase Documents  

 

+1
Login to vote
  • Actions
brunoyep's picture
brunoyep Certified
How can i clean the opened tickets? - Comment:18 Feb 2013 : Link

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

0
Login to vote
  • Actions
brunoyep's picture
brunoyep Certified
How can i clean the opened tickets? - Comment:18 Feb 2013 : Link

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

0
Login to vote
  • Actions
reecardo's picture
reecardo Symantec Employee
How can i clean the opened tickets? - Comment:18 Feb 2013 : Link

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;

+1
Login to vote
  • Actions
brunoyep's picture
brunoyep Certified
How can i clean the opened tickets? - Comment:18 Feb 2013 : Link

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

0
Login to vote
  • Actions
TGiles's picture
TGiles Symantec Employee
How can i clean the opened tickets? - Comment:19 Feb 2013 : Link

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
+1
Login to vote
  • Actions
Lark's picture
Lark Partner
How can i clean the opened tickets? - Comment:19 Feb 2013 : Link

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

+1
Login to vote
  • Actions
brunoyep's picture
brunoyep Certified
How can i clean the opened tickets? - Comment:20 Feb 2013 : Link

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

0
Login to vote
  • Actions
reecardo's picture
reecardo Symantec Employee
How can i clean the opened tickets? - Comment:20 Feb 2013 : Link

Flip the Database from master to ProcessManager

+1
Login to vote
  • Actions
brunoyep's picture
brunoyep Certified
How can i clean the opened tickets? - Comment:20 Feb 2013 : Link

Tks reecardo, tks TGiles.

The Database was cleaned!

Bruno Bacelar

0
Login to vote
  • Actions
brunoyep's picture
brunoyep Certified
How can i clean the opened tickets? - Comment:20 Feb 2013 : Link

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
0
Login to vote
  • Actions
BRING's picture
BRING Symantec Employee Certified
How can i clean the opened tickets? - Comment:27 Feb 2013 : Link

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

 

+1
Login to vote
  • Actions