Many customers have requested the capability to perform scheduled maintenance functions for devices that require preventative maintenance intervals, such as printers. No such feature exists in Altiris Helpdesk today, but a little bit of configuration work outlined in this document can resolve this need.
The solution described in this article provides capabilities for:
- Defining preventative maintenance intervals
- Automating the creation and assignment of Helpdesk Work Items
- Relate Work Item closure information back to the device's asset record
- Global maintenance history reporting
Altiris products covered in this document:
- Helpdesk Solution (required)
- Asset Control (required)
- Connector Solution (required)
*note: at the end of this document is a zip file containing all of the necessary XML export files that you can use to import into your NS. However, for the sake of learning, I highly recommend going through the exercises yourself.
Process Flow
- User enters data about a printer onto its asset form in Asset Control. This includes a start date (date placed into service), and an interval for service, such as three months. The interval is the amount of time between each scheduled maintenance.
- At each interval a Work Item is created in Helpdesk with preset assignments.
- The work item is processed by the respective technician and then resolved.
- Information from the Work Item is imported back into the asset record.
- Empirical data is provided via web reports.
Setup
Data Classes
First, it is necessary to set up some new data classes for the Resource Type "Network Printer".
- Create a new Editable Data Class under Configuration>Resource Settings>Data Classes>User Defined:
Name: Scheduled Maintenance
Multiple Rows: (unchecked)
Data Entry Tab Name: Scheduled Maintenance
Attributes:
*note: any property left blank in the table below was simply left as null in the configuration.
| Name |
Description |
Type |
Size |
Key |
Hidden |
List Values |
Order |
| Start Date |
|
Date |
|
|
|
|
1 |
| Interval (in months) |
|
Integer |
|
|
|
|
2 |
| Interval Date |
|
Date |
|
|
(checked) |
|
- |
Figure 1. Data class configuration for the "Scheduled Maintenance" data class.
Click to view.
Figure 2. The hidden field [Interval Date] in the database.
Click to view.
It is important to log maintenance work that has been done for the device; and while the tickets will be kept in Helpdesk Solution, we can actually use Connector Solution to extract data from the help desk database and return it to the form in order to provide at-a-glance maintenance history.
To do this you need to add another class for the "Network Printer" Resource Type.
- Create a new Editable Data Class under Configuration > Resource Settings > Data Classes > User Defined:
Name: Scheduled Maintenance History
Multiple Rows: (checked)
Data Entry Tab Name: Scheduled Maintenance
Attributes:
*note: any property left blank in the table below was simply left as null in the configuration.
| Name |
Description |
Type |
Size |
Key |
Hidden |
List Values |
Order |
| Work Item No. |
|
String |
50 |
|
|
|
1 |
| Date Performed |
|
Date |
|
|
|
|
2 |
| Performed By |
|
String |
50 |
|
|
|
3 |
| Result |
|
String |
80 |
|
|
|
4 |
Figure 3. Data class configuration for the "Scheduled Maintenance History" data class.
Click to view.
- Be sure to add the new data classes to the Network Printer Resource Type at Configuration > Resource Settings > Resource Types > Asset Types > IT > Network Printer.
Figure 4. Adding data classes to the Network Printer Resource Type.
Click to view.
Triggering the Process
The ability exists now to add dates and intervals to the printers, however nothing has started the process yet. The process requires that a Work Item is triggered every time an interval comes up, but the Notification Server still doesn't know when that interval date will be because it's always dynamic based on the interval duration and the time of the last maintenance performed. Currently, the NS doesn't provide this ability, but it's very easy to get it kicked off with a little bit of behind-the-scenes database configuration. In SQL Server Enterprise Manager, open up your NS database and find the new table that was created from the Scheduled Maintenance data class. It should be called Inv_Scheduled_Maintenance.
- On the table, right click and select All Tasks>Manage Triggers.
Figure 5. Manage Triggers.
Click to view.
- In the Trigger Properties dialog, paste in the following SQL statement and click OK when done:
CREATE TRIGGER [checkmaintdate] ON [dbo].[Inv_Scheduled_Maintenance]
AFTER INSERT, UPDAT
AS
update dbo.Inv_Scheduled_Maintenance
set [Interval Date] = dateadd(m,[Interval (in months)],[Start Date])
where [Start Date] is not null
This creates a database trigger called "checkmaintdate" that will watch for new entries in the Start Date field and will then add the amount of time from the Interval field and record it in the hidden field called "Interval Date". The interval date will then be used by a Notification Policy later on to create Work Items.
Figure 6. The trigger used to check the maintenance start date and update the interval date.
Click to view.
Data Entry
To start the overall process, simply open the edit page of the desired resource, select the "Scheduled Maintenance" tab, and enter a Start Date and Interval.
Figure 7. Data entry for the printer.
Click to view.
Workflow Configuration
Helpdesk Categories
The example uses a custom category in Helpdesk in order to properly categorize the resulting work item, and also to use as a basis for a Connector rule that will import data back into the asset later on. The categories defined here are only a suggestion.
Configure a new category in Helpdesk by selecting Admin > Categories > Edit Categories. Add a new category at the root of the tree called "Scheduled Maintenance", and then add a child category for Scheduled Maintenance called "Printer". Save the new categories.
Figure 8. The new categories in Helpdesk.
Click to view.
Notification Policy
A Notification Policy will be used to query the database daily for any printers that need to be serviced and create a Work Item (Incident) in Helpdesk.
- On the Tasks tab, create a new Notification Policy under Tasks > Assets and Inventory > Asset Notification Policies. Title the policy "Scheduled Printer Maintenance".
- Select "Query" as the source for the policy and then select the "Create Query" link.
- In the Query Builder, select "Edit SQL Directly" and paste in the following statement:
SELECT np.guid,
CONVERT(VARCHAR(12), sm.[Interval Date], 1) as 'Scheduled Service Date',
CONVERT(VARCHAR(12), DATEADD(MONTH, -sm.[Interval (In Months)], sm.[Interval Date]), 1) as 'Last Service Date',
np.[Name]
FROM inv_scheduled_maintenance sm
JOIN vnetworkprinter np on sm._resourceguid = np.guid
WHERE sm.[interval date] = CONVERT(VARCHAR(12), GETDATE(), 1)
Figure 9. The Notification Policy.
Click to view.
Figure 10. Paste the SQL query into the Notification Policy query.
Click to view.
- Select "Daily" for the policy's schedule.
- Now we'll tailor the Work Item. Notification Policies use a temporary datastore to store variable output from the query. These variables are available when called in the form of %DS:<column name from query>%. Our query above captures the resource guid, the current interval date, the date of last service, and the resource name, so we can add any or all of these to our Work Item information.
Under Automated Actions > Add action type, select 'New Incident Automated Action' and click Add.
Fill in the dialog with the following properties:
Name: Scheduled Printer Maintenance
Enabled: (checked)
Execute: (Once Per Row)
Title: Printer %DS:Name% is due for preventative maintenance
Comment: %DS:Name% was last serviced on %DS:Last Service Date%. It is due for service on %DS:Scheduled Service Date%.
Resource Guid: %DS:guid%
Assigned: <your choice>
Owner: <your choice>
Priority: <your choice>
Status: Planned (*note: you may need to create a new query for your worker report in order to see incidents flagged as 'Planned'. Optionally, you can view all incidents in the Admin Report.)
Urgency: <your choice>
Impact: <your choice>
Category: Scheduled Maintenance > Printer
Type: Change
Optionally, you can choose any Notify Rules that may apply.
- Apply all changes and test the Notification Policy.
***Remember, in order to generate an incident, you must have at least one printer with an Interval Date of today. For example, if today is 8/1/2006, I would have supplied a Start Date of 5/1/2006 in my data entry. When the trigger fired off, it would have set the new Interval Date at 8/1/2006.
Figure 11. Configuring the New Incident Automated Action (upper page).
Click to view.
Figure 12. Configuring the New Incident Automated Action (lower page).
Click to view.
Figure 13. The newly generated Work Item (Incident) displaying all of the data from the Notification Policy.
Click to view.
Report Maintenance History Back to the Asset
The next portion of the workflow configuration will utilize Connector Solution with a simple rule that will attach completed Work Item information back on the asset in the form of maintenance history.
First, resolve or close the incident that was created from the Notification Policy. Be sure to provide a Close Code also.
Figure 14. Set the Status and Close Code for the incident.
Click to view.
Connector Rule
We will need to create a Connector Solution rule in order to extract the data from the incident and populate our history table for the asset. We'll use an ODBC data source for this purpose, because SQL will afford us the ability to normalize and transform data prior to mapping it in to the asset fields. In order to do this, you must first create an ODBC DSN.
- Create an ODBC DSN with the following properties:
Driver: SQL Server
Name: <your_dsn_name> (I just used "hd" in this example)
Server: <your_sql_server>
Choose your login type based on the method by which you wish to connect to SQL Server.
Change the default database to "Altiris_Incidents".
Take the defaults for the rest of the wizard and test the connection.
Figure 15: ODBC DSN configuration
Click to view.
Figure 16: ODBC DSN configuration
Click to view.
Figure 17: ODBC DSN configuration
Click to view.
Figure 18: ODBC DSN configuration
Click to view.
- Create a folder called "Workflow" under Configuration > Server Settings > Notification Server Infrastructure > Data Replication > Data Sources >.
In the Workflow folder, create a new ODBC Data Source with the following properties:
Name: Printer Maintenance Records
System DSN: <your_dsn_name>
Import Query:
SELECT np.guid,
np.name as 'Printer Name',
wo.workitem_number as 'Workitem Number',
wo.workitem_status_lookup_value as 'Status',
wo.workitem_category_tree_value as 'Category',
wo.assigned_to_worker_full_name as 'Worker',
wo.workitem_close_code_lookup_value as 'Closure Code',
wo.workitem_modified_on as 'Close Date',
CONVERT(VARCHAR(12), DATEADD(MONTH, sm.[Interval (In Months)], sm.[Interval Date]), 1) as 'Next Scheduled Service Date'
FROM workitem_current_view wo
JOIN demo2k3ns.altiris.dbo.vnetworkprinter np on wo.managed_object_resource_guid = np.guid
JOIN demo2k3ns.altiris.dbo.inv_scheduled_maintenance sm on np.guid = sm._resourceguid
WHERE wo.workitem_status_lookup_value = 'Closed' OR wo.workitem_status_lookup_value = 'Resolved'
*note: In order to dynamically join data from two different databases, this expression uses fully qualified table names in the form of <servername>.<database>.<user>.<table>. Therefore, you will need to replace the server name with your own (shown in bold red in the SQL statement).
Figure 19. Data source configuration for Printer Maintenance Records.
Click to view.
- Create another folder called "Workflow" under Configuration > Server Settings > Notification Server Infrastructure > Data Replication > Import/Export Rules.
In this folder, create a Resources Import/Export Rule with the following properties:
Name: Printer Maintenance Records
Data Source: Printer Maintenance Records
Resource Type: Network Printer
Replication Direction: Import
Allow Resource Creation: (checked)
Allow Resource Update: (checked)
Removed Assets: Left unchanged in the NS database
Resource Lookup Key: Resource Guid to guid
Column Mappings (as follows):
- Interval Date: Next Scheduled Service Date
- Work Item No.: Workitem Number
- Date Performed: Close Date
- Performed By: Worker
- Result: Closure Code
- *note: Remaining fields should be set to the default "-Select a Column -". Feel free to import other fields, but just make sure you double-check the column for column mapping. Connector Solution will make assumptions that you may not want. For example, any time there is a "Description" field in a data class, it will match it to "Description" in the source, which could result in erroneous data.
Enable Schedule: Ensure that it is checked and set to 'Half-Hour'. This means that every half-hour, asset records will be updated with maintenance history.
Save and execute.
Figure 20. Import rule for Printer Maintenance Records.
Click to view.
Figure 21. Column Mappings for Printer Maintenance Records import rule (upper page).
Click to view.
Figure 22. Column Mappings for Printer Maintenance Records import rule (lower page).
Click to view.
Figure 23. The resulting maintenance history for the asset.
Click to view.
Report Configuration
Once the workflow configuration is complete, it may be desirable to view a global report on maintenance performed on all assets. To do this is very simple actually, because the logic for the report is very similar to what we used for the Connector Solution data source.
- On the Reports tab, create a new folder called "Scheduled Maintenance" under Reports > Assets and Inventory.
- Using the Report Builder Wizard, create a new report called "Scheduled Maintenance History".
- Use the "Enter SQL Directly" method and paste in the following SQL statement:
USE altiris_incidents
SELECT np.guid,
np.name as 'Printer Name',
wo.workitem_number as 'Workitem Number',
wo.workitem_status_lookup_value as 'Status',
wo.workitem_category_tree_value as 'Category',
wo.assigned_to_worker_full_name as 'Worker',
wo.workitem_close_code_lookup_value as 'Closure Code',
wo.workitem_modified_on as 'Close Date',
CONVERT(VARCHAR(12), sm.[Interval Date], 1) as 'Next Scheduled Service Date'
FROM workitem_current_view wo
JOIN demo2k3ns.altiris.dbo.vnetworkprinter np on wo.managed_object_resource_guid = np.guid
JOIN demo2k3ns.altiris.dbo.inv_scheduled_maintenance sm on np.guid = sm._resourceguid
WHERE wo.workitem_status_lookup_value = 'Closed' OR wo.workitem_status_lookup_value = 'Resolved'
Notice now we've added the USE statement in order to ensure that the report is combining the Helpdesk and Asset databases properly in this context, and we've taken out the calculation for Interval Date, so that instead of setting it ahead according to the Interval, we're reflecting what has now been set in the database.
Figure 24. Report configuration.
Click to view.
Figure 25. The report result.
Click to view.
ZIP File with XML Export Files
To use, just copy and paste the zip file out to a folder. Extract the xml files and import them into the proper folders on the Notification Server.
| License: | AJSL
By clicking the download link below, you agree to the terms and conditions in the Altiris Juice Software License |
| Support: | User-contributed tools on the Juice are not supported by Altiris Technical Support. If you have questions about a tool, please communicate directly with the author by visiting their profile page and clicking the 'contact' tab. |