Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

ServiceDesk 7.5 SP1 Purge Utility

Created: 13 Mar 2014 • Updated: 24 Jan 2015 | 12 comments
Language Translations
bcason's picture
+3 3 Votes
Login to vote

The intent of this project is to Search, Select and Purge Report Processes along with their associated references.

The Project is built on ServiceDesk 7.5 SP1 and is dependent on the associated Database Schema.

The Project includes a method for discerning the ProcessManager ConnectionString. If this fails, you can define the ConnectionString in the Project Properties.

The following is a description of the Project features:
 

Search.png

The 'Search ReportProcess' feature uses plain text pattern matching against the ReportProcessID, ProcessTitle and ProcessDescription data.
Found ReportProcesses will display in the 'Purge Candidates' section of the Form.
If the 'Description' scope is included with the Search, the text used for the search will be prepended to the ProcessTitle and enclosed with '{...}'.
This provides feedback that the search text was found in the ProcessDescription.
 

Range.png

The 'Select Range by Type' feature uses a 'Start:' and 'End:' item from a pull-down list.
This will perform a range type of search with the following conditions:

1) The ReportProcess Type is the same. For example: IM-
2) A 'Start:' and an 'End:' are selected.
 

DateTime.png

The 'Cut Off DateTime' feature selects any ReportProcess with a ProcessStarted date that is less than the defined DateTime.

Purge.png

The 'Keep Articles' switch allows for the retention of Knowledge Base articles.
Knowledge Base articles that are created using the 'Submit Knowledge Base Entry' form are ReportProcesses and will be purged if they are selected.
However, if the Knowledge Base process is Closed and the 'Keep Articles' option is checked, the KB Article will remain untouched in the Knowledge Base tab.

Begin the Purge...

Once confirmed, the Purge begins and will eventually end with a Count summary of the purged items.
One can 'Go Back', which starts over with a fresh copy of the remaining ReportProcesses.

———————–
PERFORMANCE
———————–
In an under performing test environment, one can expect approximately 1,000 items purged every 10 minutes.

———
FIXES       ( Posted: Sat, Jan 24, 2015 )
———

  •  Selecting a single item in the Search List moves all of the items to the Purge List.
  •  Duplicate items occur in the Search List when the same item is removed from the Purge List.
  • The project bombs when used in an environment without Change Management.
  • The project bombs when used in an environment without Incident Management.
  • The project exits unexpectedly when the database is SQL Server 2005.

Comments 12 CommentsJump to latest comment

QuietLeni's picture

Thanks for this! So useful (but not to be given to children)!

What is the point of an Asset Management Solution that needs excessive management? Let me help you.

0
Login to vote
André Florencio's picture

Hi bcason,

First of all thank you so much!

Is there any way to reset the count of the process? (IM-000001)

I used the Workflow and i couldn't reset de number of the process.

Thank you!

Regards,

André Florencio

0
Login to vote
TGiles's picture

The process id # is controlled by the table ReportProcessGenerator. If you simply remove all the entries associated with IMs it will reset the counter. You can use the following script to reset the number.

DELETE FROM ReportProcesssGenerator

WHERE Type LIKE 'IM-%'

+1
Login to vote
bcason's picture

The project calculates the LastUsedIdentity in the 'Reconcile RPG Table' Embedded Model.
I would be interested in knowing if the project did not calculate correctly.

NOTE: Always check the ReportProcessGenerator and ReportProcess tables before resetting your Ticket count to zero, there may be tickets left in the system.

+1
Login to vote
André Florencio's picture

Hi,

I'm sorry. Outstanding tickets there after excluding the counter was reset

Thank You!

André Florencio

0
Login to vote
Scott Pequin's picture

This was show to me as possible solution to cleaning out old test Change Request.  I opened the package in Process Manager, made the change to access my database.  When I run it in debug mode it will let me sign on then it comes back that process is complete and just stops.  What am I doing wrong?

0
Login to vote
africo's picture

You'll need to review the Workflow logs to find the issue.  What errors occur when the process stops?

0
Login to vote
bcason's picture

FYI: The project is supposed to be able to fetch your SQL ConnectionString automatically. The Project Property is a fall-back if the automated method fails.

As previously hinted, the project log should tell us what the problem is.

0
Login to vote
Scott Pequin's picture

THis is what I get back.  I have not modified the package in any other way

Log Fatal: What just happened?

: Time Spent: 0:6

: Component: Get All Report Process IDs :

: Message: Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Incorrect syntax near the keyword 'ELSE'. :

: StackTrace: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Incorrect syntax near the keyword 'ELSE'.

at GetAllReportProcessIDs.SqlQuery.GetAllReportProcessIDs.Run(IData data)

at LogicBase.Core.ExecutionEngine.SinglePathProcessComponentExecutionDelegate.Execute(IData data, IOrchestrationComponent comp, String& outputPath, IExecutionEngine engine, TLExecutionContext context)

at LogicBase.Core.ExecutionEngine.AbstractExecutionEngine.RunComponent(TLExecutionContext context, IData data, IOrchestrationComponent comp)

Component Executed: Create Log Entry (LogicBase.Components.Default.Logging.CreateLogEntryComponent), exit path is ''

Data:

CurrentUserTimeZone = "Central Standard Time"

EnsembleSecurityToken = EnsembleSecurityToken servicedesk@tastefullysimple.com

ExceptionComponentClassName = "GetAllReportProcessIDs.SqlQuery.GetAllReportProcessIDs"

ExceptionComponentName = "Get All Report Process IDs"

ExceptionTriggerComponentID = "0f848856-a160-11e3-b00c-0050560517cd"

ExceptionTriggerExceptionClassName = "System.Data.SqlClient.SqlException"

ExceptionTriggerMessage = "Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Incorrect syntax near the keyword 'ELSE'."

ExceptionTriggerStackTrace = "System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Incorrect syntax near the keyword 'ELSE'.

at GetAllReportProcessIDs.SqlQuery.GetAllReportProcessIDs.Run(IData data)

at LogicBase.Core.ExecutionEngine.SinglePathProcessComponentExecutionDelegate.Execute(IData data, IOrchestrationComponent comp, String& outputPath, IExecutionEngine engine, TLExecutionContext context)

at LogicBase.Core.ExecutionEngine.AbstractExecutionEngine.RunComponent(TLExecutionContext context, IData data, IOrchestrationComponent comp)"

Later = 1/19/2015 4:53:36 PM

PlainConnectionString = "Data Source=10.2.0.171;Initial Catalog=ServiceDeskProcessManager;Integrated Security=True;Pooling=True;Connect Timeout=30"

ThisFormData =

TimeSpent = 00:00:06.1210000

Component Executed: End (LogicBase.Components.Default.Process.EndComponent), exit path is ''

Data:

CurrentUserTimeZone = "Central Standard Time"

EnsembleSecurityToken = EnsembleSecurityToken servicedesk@tastefullysimple.com

ExceptionComponentClassName = "GetAllReportProcessIDs.SqlQuery.GetAllReportProcessIDs"

ExceptionComponentName = "Get All Report Process IDs"

ExceptionTriggerComponentID = "0f848856-a160-11e3-b00c-0050560517cd"

ExceptionTriggerExceptionClassName = "System.Data.SqlClient.SqlException"

ExceptionTriggerMessage = "Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Incorrect syntax near the keyword 'ELSE'."

ExceptionTriggerStackTrace = "System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Incorrect syntax near the keyword 'ELSE'.

at GetAllReportProcessIDs.SqlQuery.GetAllReportProcessIDs.Run(IData data)

at LogicBase.Core.ExecutionEngine.SinglePathProcessComponentExecutionDelegate.Execute(IData data, IOrchestrationComponent comp, String& outputPath, IExecutionEngine engine, TLExecutionContext context)

at LogicBase.Core.ExecutionEngine.AbstractExecutionEngine.RunComponent(TLExecutionContext context, IData data, IOrchestrationComponent comp)"

Later = 1/19/2015 4:53:36 PM

PlainConnectionString = "Data Source=10.2.0.171;Initial Catalog=ServiceDeskProcessManager;Integrated Security=True;Pooling=True;Connect Timeout=30"

ThisFormData =

TimeSpent = 00:00:06.1210000

0
Login to vote
bcason's picture

Please run the following SQL. Does it produce the same error?

with
    reference ( sessionID, processID )
    as
        (
            select rpr.ChildProcessID, rp.ReportProcessID
            from ReportProcessRelationship rpr with (NOLOCK)
            join ReportProcess rp with (NOLOCK) on rp.SessionID = rpr.ParentProcessID
            join CmRelatedProcess crp  with (NOLOCK) on crp.ProcessRelationshipId = rpr.ReportProcessRelationshipID
        )
select rp.ReportProcessID + ISNULL('=>' + reference.processID, '') as [ReportProcessID],
rp.ProcessStarted,
rp.ProcessTitle,
rp.SessionID
from ReportProcess rp with (NOLOCK)
left outer join reference on reference.sessionID = rp.SessionID

If you get the same error, try placing a ';' character before the 'with'. For example: ;with
If that still fails, what version of SQL are you running?

0
Login to vote
bcason's picture

We have discovered that the SQL Integration is not 2005 compatible. Stay tuned for an updated project.

0
Login to vote
bcason's picture

The package has been updated with the following fixes:

  • The project now works with Workflow only environments
  • The project supports SQL Server 2005
0
Login to vote