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

Delete Multiple Tickets From Service Desk

Created: 29 Oct 2010 • Updated: 18 Jul 2011 | 9 comments
Language Translations
mike.gibson's picture
+2 2 Votes
Login to vote

The issue:

Around 6000 duplicate tickets were automatically created by the email listener. This was down to 2 issues -

1. The component "Text Contains Multiple Text" claims to be case insensitive, but is in fact not. This means that despite filters as follows:

  • Out Of Office
  • Autoreply

A user putting say Out of office, or autoreply were not caught and the ticket was logged.

This has been raised with Symantec engineering for resolution. The workaround is to use multiple "Text Contains" components.

2. The environment we were working in had their exchange server configured to send "out out of office" replies to every email. Combined with sending an email to acknowledge a new incident and the issue in 1, the 2 services got in a loop.

a) Incident logged by someone out of office.

b) Incident automatically logged

c) Acknowledgment email sent to the user

d) Out of office responds by email - goto b)

Multiple filters will never completely protect you from this, as users were able to put in their own text - and in their own language.

Depending on your environment, 2 potential process workarounds are as follows:

1. Circuit breaker

Before the email listener logs a new incident, check to see if they have already logged an identical incident within the last hour. If they have, then send an email to tell them this, from a do-notreply@whereever.com address, and let them know that if they really need to log the ticket then they must contact the helpdesk.

- However, you may actually have a requirement for this to work - e.g. A third party system raises tickets automatically by email so you must remember to take care of exceptions.

2. SPAM Filter

Before the ticket is logged - hold the process in a process dialog - and send an email to the user, again from a do-notreply@whereever.com address with a link that takes the user to a confirmation form (don not use assignments as you may have licensing issues).

This clearly works only if the user can actually access the link internally, via VPN, or it must assume their Blackberry has access to the intranet (Usually the case, but not always) - So I would recommend a caveat that says - you need to forward this to someone internally, or call the service desk.

Removing the tickets

I have included a process model that we used to remove the 6000 tickets.

The process currently supports Incident, Change, Problem processes, but can be clearly extended to include any process at all.

Disclaimers:

It should be noted that a process project has been used instead of a more ergonomic form project due to issues with the number of iterations and the error "threading model aborting" which often rears itself in these mass iterations of webservice calls.

It should also be noted that the SQL script is an update of the delete all items that was preciously developed. This method is not supported by Symantec but has been used on numerous customers without issue. In this case it was used to remove 6000 live tickets from a an environment with about 10000 actual tickets, both live and closed. This was run 3 weeks ago, and has been deemed 100% successful.

If anyone believes the SQL should be modified, then please do not hesitate to forward any updates to me for review, and I'll update the component.

To operate the process:

1. Create a list of the tickets you wish to delete by their process_IDs

This can be easily done via SQL and searching on the criteria that identify the tickets as duplicates - for example:

SELECT     ReportProcess.ReportProcessID,ReportProcess.ProcessStarted,ReportProcess.Description

FROM         ReportProcess INNERJOIN

                      ReportProcessContact ONReportProcess.SessionID =ReportProcessContact.SessionID INNERJOIN

                      [User] ONReportProcessContact.ReferenceID =[User].UserID

WHERE     (ReportProcessContact.ContactType =N'Affected User')AND([User].PrimaryEmail =N'mike.gibson@protirus.com')AND

                      (ReportProcess.ProcessStarted >CONVERT(DATETIME,'2010-01-01 10:15:00',102))AND(ReportProcess.Description LIKEN'%out of office%')

2. Paste these into a spreadsheet of the format provided in the file - ProcessRemoveTest.xls

NOTE: It is important to retain the version of the Excel file, as the integration component does not work on the latest version of Excel.

3. Unpackage project and save to \Program Files (x86)\Altiris\Workflow Designer\WorkflowProjects

4. Open the POC_RemoveProcessesv01 project.

5. Adjust the following properties if necessary: 

IncidentWorkflowManagement - URL of incident workflow management service

ChangeWorkflowManagement -  URL of change workflow management service

ProblemWorkflowManagement - URL of problem workflow management service

6. Run the process in debug mode

7. Right click on composer.aspx?StartWorkflow=true and select open browser.

8. In the web page hit the browse button and browse to the excel file containing the process ID's from step 2.

9. Click OK Button and close browser.

10. When the blue arrow reaches the "display results dialog box". (Note diagram above has arrow @Abortworkflow task)

Select workflow statistics tab.  Select View next to Unassigned Tasks. 

11. Click view for the task under task list unassigned.

12. On the Workflow Management Statistics page click on the Page URL under Responses.

This displays the results of the deletion. To to download these results for future reference then:

13. On the results page click on the download results to excel button. 

14. Click save on the dialog window shown.  Browse to the location to save the file and click save.

15. When returned to the main browser window click the Ok button.

16. Close the browser down and the debug process.

The delete ticket process has been modified to include a form to allow the entry of the connection string details (Sql instance, db name, username and password).  This allows the delete process to be used in both 7.0 and 7.1.

Comments 9 CommentsJump to latest comment

JanneP's picture

In my workflow designer there is an error in components POC_RemoveTickets and such.

The error is that workflow cannot find  [[ProfileProperties].service_desk_settings_ensemble_db_connection_string]

Is there something I've missed?

This workflow otherwise looks rally nice, thanks!

+1
Login to vote
mike.gibson's picture

For the ServiceDesks we have installed we used an application property for a connection string to the ensemble database.  This property can be created in the project properties of the process as follows:

  • Got to the project page.
  • Select the project tab.
  • Click on Add Property.
  • Name the property EnsembleConnnectionString.
  • Under value enter the following: Data Source=<Servername\Instance name>;Initial Catalog=Ensemble;User ID=<Username>;Password=<Password>;
  • In the primary model open the POC_RemoveComponent
  • In the Connection Configuration tab under Connection String select the above property from the project properties.

This will remove the error you are seeing.

0
Login to vote
mdelman's picture

I only need to delete a couple of records.  Is there an easier way to complete this task.  This solution is great if you need to delete 6000 records but it is a lot of work just to delete a couple.

I would hate to have to resolve the tickets just to get rid of them.  That would affect my KPI's.

Thanks

Marc

0
Login to vote
mike.gibson's picture

Due to the nature of ServiceDesk and how the ticket relates to the underlying database tables.  This is the simplest way of removing tickets from the servicedesk as the process takes care of all the underlying database tables.

0
Login to vote
smassie's picture

The new version has thrown up a small problemo...

The POC_getincidentsbyID integration component connect to the processmanager database but errors out as it looks like the SQL query in this component is hard coded to the Ensemble database. There seems to be a reference to Ensemble.dbo.Reportprocess in the SQL query.

0
Login to vote
SDriver's picture

using a 7 'ensemble' db upgraded to 7.1, so the database name hasn't changed, but getting error on POC_GetInicidentByID.

Application Name : POC_RemoveProcessesv010
Process ID : 1712
Date :8/09/2011 11:52:03 AM
Log Level :Error
Log Category :LogicBase.ExecutionEngine
Machine Name : BAULDSD1
Message :
System.MissingMethodException: No parameterless constructor defined for this object.
   at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandle& ctor, Boolean& bNeedSecurityCheck)
   at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean fillCache)
   at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean fillCache)
   at System.Activator.CreateInstance(Type type, Boolean nonPublic)
   at LogicBase.Components.FormBuilder.AdvancedComponents.GridView.GridViewColumnRendererFactory.GetColumnRenderer(AbstractGridViewColumnConfiguration columnConfiguration) in c:\build\projects\WF71MR2\components\LogicBase.Components.FormBuilder\Components\GridView\GridViewColumnRendererFactory.cs:line 18
   at LogicBase.Components.FormBuilder.AdvancedComponents.GridView.GridViewComponentRenderer.Load() in c:\build\projects\WF71MR2\components\LogicBase.Components.FormBuilder\Components\GridView\GridViewComponentRenderer.cs:line 123

0
Login to vote
bentur's picture

Hi Guys,

I am new to Altiris Wf designer.

I wanted to know how can we connect SQL server 2005 to my altiris WF designer ??

Also, my ALTIRIS designer is in one server and sql server 2005 is in other server.

Please help me...

Regards,

Bentur

0
Login to vote
sdmayhew's picture

error here also 

Process ID : 3968
Date :12/19/2011 5:57:12 PM
Log Level :Error
Log Category :Generated.POC_ReadProcessIDs.DynamicClassRead
Machine Name : server
Message : 
Exception at Run method with message : Unable to load DLL 'iprop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

Altiris user since 2001, Asset Management for 25 years

0
Login to vote
seralar's picture

HI,

Where is the spreadsheet ProcessRemoveTest.xls?

0
Login to vote