I am working on the SOP that will govern how we use Cherwell Service Management at my company. One of the topics that has been requested is Periodic Review. You know, a scheduled set of activities that confirm the system is still running properly and there aren't any unnoticed problems getting out of hand.
One idea I came up with was to run the health check and submit to Cherwell Support to see if they see anything peculiar.
Does anyone else have any recurring activities they do on their system to help keep the system running and make sure that the higher-ups are confident in the system? Any ideas would be appreciated.
Thank you very much.
Enjoy your day!
We just started running DB Maintenance and rebuilding our indexes once a month. We had some problems with indexes, and the quick search stopped working for some Business Objects.
Create SQL views that will return records where incoming emails failed to process, and where automation process records failed to run.
There's an example of both queries, at the end of this post.
After you create these views - which you should do using a 'Database Server Object' definition in your blueprints - you should use an External Connection definition and create a new External Business Object for each of these views, so that you have them in Cherwell.
For each business object, when you create the grid, sort on the created date/time, in descending order, so you get newest records first.
Then, on a dashboard that you use, add Search Results List widgets that will return the top 10, 20 or so rows from each of these business objects / views.
This way, when you sign into cherwell in the morning, right on your dashboard, in your face, is a list of any errors encountered by your email / event monitor and your automation process engine.
Use the ticket ID on the automation process engine view to find the incident that failed and review it's errors.
On the emails, you can expand the EmailDetails field, but it has a lot of XML metadata in there, and copies of the email body in text, RTF and HTML, before finally giving you the error message. You'll find it at the bottom of the email.
On your form for this object, place a label for the body of the email somewhere. Give it a text expression of the EmailDetails field, with modifiers for 'As XML ()', 'Find Element (Email def), String from Element (Body text)' <- Do not include the parenthesis in the values you type out. It should look like that on the left half of the modifier list, where it shows the list of modifiers.
Add another label for the error message, with the email details field and modifiers: As XML (), Find Element (EmailDef), Find Element (ActionsExecutedList), XPath (value) (//ActionExecuted/InfoMsg) <- Again don't include the parenthesis in the values for these modifiers. You will need those slashes, for the xpath value.
Now you'll have a business object that shows you the body and error message from a failed email message.
There's a mApp on the mApp exchange that will do the same, doesn't require database views and looks better, but only works when a journal is created by the mail monitor and there isn't a way to filter on just the records that failed.
The SQL views you'll need:
Failed Incident Automation Processes:
CREATE VIEW V_FailedIncidentProcesses
Select I.IncidentID, I.Status as IncidentStatus, TDBP.DefName As AutomationProcessName, TDBP.DefDescription As APDescription, I.RecID As IncidentRecID, TP.RecID As UniqueRecID FROM TrebuchetProcesses AS TP
LEFT JOIN Incident I On I.RecID = TP.BusObRecId
LEFT JOIN TrebuchetDefs TDBP On TDBP.DefType = 'BusinessProcessDef' AND TDBP.DefID = TP.ProcessID
WHERE TP.ProcessStatus = 'CompletedFailed'
AND TP.BusObTypeId = '6dd53665c0c24cab86870a21cf6434ae'
AND IncidentID Is Not Null
AND TP.LastModDateTime > DATEADD(dd, 0, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, - 1, CURRENT_TIMESTAMP)) - 1, 0))
That's the end of the view.
Next, the email monitor view:
CREATE VIEW V_FailedIncomingEmails
Select * FROM TrebuchetMail
WHERE Status <> 'Success'
AND Direction = 'Incoming'
I've had many issues in the past running DB maintenance on a scheduled basis through the scheduler. Not sure exactly why. When running it manually, though, it seems to work.
Dependent on your version, additionally, you'll want to consider clearing some tables in the database after a certain period of time. Blueprint history will eventually take up a lot of space, especially after V9.1 where it started storing a full copy of the blueprint files themselves within the DB.
Additionally, your databases TrebuchetMail table, which holds a copy of every email going into and out of the system - and is separate from the Journal records you see on tickets - will grow at a very fast pace if you receive a lot of emails. A lot of companies consider truncating or clearing out some records in this table over time, to save space in their database.
The reason for this is that while this table ends up consuming a massive amount of data, it's only used to show you a compiled copy of an email, when looking at a (plaintext by default) journal mail history record as a child of a business object like an Incident, and pressing the 'E-mail' button that appears in the form arrangement toolbar. After about a year you might want to consider checking on this to see whether it's getting out of hand, as this table is included in your backups, and will thus make your backup files grow quite large as well.
Additionally, make sure that you enable the features on the licensing page that release licenses after the client stops responding for X number of minutes. There is a tendency, in seemingly all versions, to leave licenses in use even after some users have closed the browser or rich clients. If you don't turn on this option, your license usage might end up being higher than the number of people actually signed in at any given time. Use the license usage tracking functions in Cherwell to see how your license usage looks before and after enabling this feature.
Doug, thank you for the very detailed response(s).
Our environment is hosted by Cherwell; will they provide the credentials to create the external connection?
Cherwell will create the external connection for you if you create a support request for them to do so, in many cases.
However, SaaS / hosted systems can't create Database Server Object definitions to create SQL views or other objects through the app.
This is controlled by your system's license - if your license key is marked SaaS, that manager in the blueprint editor is hidden.
You'll need to create an additional support request to have the SQL views created by a cherwell representative.
I've heard of customers in the past having mixed results in getting views created; their security policies are pretty strict as far as customers modifying their databases; they may want to know why you need the views created.