While Altiris Asset Management Suite does a fantastic job of managing capitalized assets, there isn't any functionality to manage expensed consumables such as keyboards, mice, toner, etc.

The suggestions here cover the fundamentals as it relates to configuring resource types for consumable management and offers typical attributes and relations that a desktop support technician, IT manager, helpdesk agent, or operations personnel may require in their daily activities.

This solution provides capabilities for:

• Cataloging consumables by store room location
• Check-in/check-out procedures
• Cost basis calculation for chargeback
• Automated notification of low stock

Altiris products covered:

• Asset Control (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.

Setup

Data Classes

First, it is necessary to set up some new data classes for a Resource Type that we will create later called "Consumable".

1. Create a new Editable Data Class under Configuration Resource Settings > Data Classes>User Defined:

Name: Inventory Log

Multiple Rows: (checked)

Data Entry Tab Name: General

Attributes:

*note: any property left blank in the table below was simply left as null in the configuration.

Name	Description	Type	Size	Key	List Values	Order
Name		String	50			1
Reason		String	256			2
Date		Date				3
Quantity Added		Decimal				4
Quantity Taken		Decimal				5
PO		Resource Foreign Key (Purchase Order)				6
Price		Currency				7

Figure 1. Data class configuration for the "Inventory Log" multi-row table

Click to view.

2. Create a new Editable Data Class under Configuration > Resource Settings > Data Classes > User Defined:

Name: Details

Multiple Rows: (unchecked)

Data Entry Tab Name: General

Attributes:

*note: any property left blank in the table below was simply left as null in the configuration. The list values are merely examples and you can add or substitute any of your own.

Name	Description	Type	Size	Key	List Values	Order
Type		Static List	50		Keyboard, Mouse, Video Card, Hard Drive, NIC, Laptop Battery, Patch Cable, Toner, Drum	1
Minimum		Decimal	N/A
Quantity
Maximum Quantity		Decimal	N/A

Figure 2. Data class configuration for the Consumable Type.

Click to view.

Resource Types

A "Consumable" resource type does not exist out of the box, so it is necessary to create one. Create the new resource type "Consumable" at Configuration > Resource Settings > Resource Types > Asset Types > IT.

Add the new data classes from above to this resource type by editing the data classes list. Select "Apply" when done.

Figure 3. Adding data classes to the Consumable resource type.

Click to view.

Finally, you'll need to add a resource association in order to capture the location for a consumable.

1. Create a new folder called "Consumables" at Configuration > Resource Settings > Resource Associations > Asset Association Types > User Defined.
    
2. In the Consumables folder, create a new Editable Association Type called "Location". The properties should be as follows:

   Name: Stock Location

   From Type: Consumable

   To Type: Location

   Allow Association to Itself: (unchecked)

   Minimum Cardinality: 1

   Maximum Cardinality: Unlimited

   Edit Style: Hierarchy Picker Showing Children (note: you may need to apply changes and refresh the browser in order for this selection to show up)

Figure 4. Defining the resource association for Location.1

Click to view.

Consumables View In Asset Control

Creating "picker" reports for consumables

If only using the standard picker report for consumables, the user would be left a little confused. Therefore, we must create new reports that are a little more relevant. These reports will be used to show all of the consumables and their remaining inventory quantities, by location. This presents a bit of a challenge for the report because it requires subtracting the removed quantities from the added quantities to reflect the remaining balance, and then performing math on the quantities and costs to come up with an averaged cost basis. It is also possible for the user to leave the values in the field null, which means a SQL function is needed in order to convert the value before arithmetic functions can be performed. Finally, we have to be able to restrict the report to only the relevant location.

Create a new view for convenience

Prior to creating the reports, it will be necessary to generate a new view in SQL Server in order to make their required queries easier to construct. In Query Analyzer, copy, paste, and run the following SQL statement:

CREATE VIEW vConsumables
AS
SELECT
      AL2.Type,
      AL4.Name AS Description,
      AL4.Guid,
      AL5.Name AS Location,
      AL2.[Minimum Quantity],
      AL2.[Maximum Quantity],
      AL6.[Quantity Added],
      AL6.Price,
      AL6.[Quantity Taken],
      AL6._ResourceGuid
      FROM dbo.vLocation AL5
      INNER JOIN dbo.ResourceAssociation AL1 ON AL5._ResourceGuid = AL1.ChildResourceGuid
      INNER JOIN dbo.vResourceItem AL4
      INNER JOIN dbo.Inv_Details AL2 ON AL4.Guid = AL2._ResourceGuid ON AL1.ParentResourceGuid = AL4.Guid
      INNER JOIN dbo.Inv_Inventory_Log AL6 ON AL2._ResourceGuid = AL6._ResourceGuid

Report Configuration

1. To create the report, you'll first need to create a new folder. Create this folder as Reports > Assets and Inventory > Asset Management > Custom Reports. This is a simple tabular report.
    
2. Then, from the Custom Reports folder, create a new report. Title the report "All Consumables". Choose the "Enter SQL Directly" option and paste in the following SQL:

SELECT
      t1._ResourceGuid,
      t1.Type,
      t1.Description,
      t1.Location,
      CAST(SUM(ISNULL(t1.[Quantity Added], 0)) - SUM(ISNULL(t1.[Quantity Taken], 0)) AS SMALLINT) AS 'In Stock',
      ROUND (SUM(CAST ((ISNULL(t1.[Quantity Added], 0) * ISNULL(t1.[Price], 0)) AS MONEY)) / SUM(ISNULL(t1.[Quantity Added], 0)), 2) AS 'Cost Basis for Chargeback'
      FROM vConsumables AS t1
      GROUP BY t1._ResourceGuid, t1.Type, t1.Description, t1.Location
      ORDER BY t1.Location, t1.Type

Figure 5. The Report Builder Wizard is used to create the "All Consumables" picker report.

Click to view.

3. Once finished, edit the report again and make sure the GUID is hidden for the report.

Figure 6. Hide the GUID field using the "Configure Views" dialog.

Click to view.

4. Create another report in the same folder called "Consumables by Location". This report will contain a parameter that allows you to select a location to determine available quantities and check in/out items. Choose the "Enter SQL Directly" option again and paste in the following:

SELECT
      t1._ResourceGuid,
      t1.Type,
      t1.Description
      CAST(SUM(ISNULL(t1.[Quantity Added], 0)) - SUM(ISNULL(t1.[Quantity Taken], 0)) AS SMALLINT) AS 'In Stock',
      ROUND (SUM(CAST ((ISNULL(t1.[Quantity Added], 0) * ISNULL(t1.[Price], 0)) AS MONEY)) / SUM(ISNULL(t1.[Quantity Added], 0)), 2) AS 'Cost Basis for Chargeback'
      FROM vConsumables AS t1
      WHERE t1.Location LIKE '%Location%' AND t1.Type LIKE %Type%
      GROUP BY t1._ResourceGuid, t1.type, t1.Description
      ORDER BY t1.Type

5. Once finished, edit the report again and make sure the GUID is hidden for the report, just like in the previous step. You will also need to add a parameter in order to restrict the report to a location. Notice in the SQL statement that the WHERE clause includes the syntax t1.Location LIKE '%Location%'. %Location% is the variable that is used for the parameter. We need to supply distinct values to this variable in order to come up with a usable dropdown. To produce a selection dropdown for the parameter, we will once again incorporate SQL.

   In the report edit screen, select the "Parameters" button, then select "New Local Parameter". In the "Create Parameter" dialog, enter the following:

Name: Location

Parameter Type: Dropdown

Prompt user for value when report is run: (checked)

User prompt: Location

Value type: Query results

Value:

SELECT DISTINCT
      AL5.Name AS 'Location'
      FROM dbo.ResourceAssociation AL1,
      dbo.Inv_Details AL2,
      dbo.vResourceItem AL4,
      dbo.vLocation AL5,
      dbo.Inv_Inventory_Log AL6
      WHERE (AL4.Guid=AL2._ResourceGuid
            AND AL1.ChildResourceGuid=AL5._ResourceGuid
            AND AL4.Guid=AL1.ParentResourceGuid
            AND AL2._ResourceGuid=AL6._ResourceGuid)

Select OK and then apply your changes.

Figure 7. Creating the "Location" parameter.

Click to view.

6. Right click on the reports in the tree and move them to Reports > Assets and Inventory > Asset Management > Asset Type Picker Reports.
    
7. You will then need to select the new picker reports from the Resources tab so that they will become the default views. Be sure to unselect the old default report so that it will no longer be listed.

Without data, the system may return an ASP Server Error if you exit the view and then come back in. It can be a little confusing to remediate this issue.

Figure 8. Assign the new picker reports as the default views for the Consumable resource type.

Click to view.

Figure 9. The "All Consumables" view complete with remaining quantities. Simply drill in to check items in or out.

Click to view.

Figure 10. The "Consumables by Location" view complete with remaining quantities. Simply drill in to check items in or out.

Click to view.

Automated Replenishment

Now that stock levels are being managed for the various consumables, it is possible to enact some automation that will aid in replenishing the stock rooms before the available quantities diminish entirely.

To do this, we can employ the use of Notification Policies. Notification Policies are extremely flexible polices that are designed to take some form of action based upon criteria being met. For example, when managing software licenses, a Notification Policy could trigger an alert to the contract manager 60 days prior to a support renewal.

We will create a Notification Policy that will generate an email to an IT purchaser at such time that the balance remaining for a given item dips below five. You may wish to create new Notification Policies for different types of items. A quantity of five may be good for nominally priced NIC cards, but for more expensive multibay drives for specific vendor laptops, you may only want a quantity of two or something thereof. Also, earlier on when we created the "Details" data class, we added a field called 'Maximum Quantity'. This field could be used in a Notification Policy to inform a manager when surplus amounts over the maximum quantity have been ordered, but that is not covered here.

1. On the Tasks tab, create a new Notification Policy under Tasks>Assets and Inventory>Accounting>Notification Policies. Title the policy "Stock Replenishment".
    
2. Select "Query" as the source for the policy and then select the "Create Query" link.
    
3. In the Query Builder, select "Edit SQL Directly" and paste in the following statement:

SELECT
      AL2.Type,
      AL4.Name AS 'Description',
      AL4.Guid,
      AL5.Name AS 'Location',
       CAST ((Sum (ISNULL(AL6.[Quantity Added], 0)) - Sum (ISNULL(AL6.[Quantity Taken],0))) AS SMALLINT) AS 'In Stock'
        FROM dbo.ResourceAssociation AL1,
            dbo.Inv_Details AL2,
            dbo.vResourceItem AL4,
            dbo.vLocation AL5,
            dbo.Inv_Inventory_Log AL6
        WHERE (AL4.Guid=AL2._ResourceGuid
                AND AL1.ChildResourceGuid=AL5._ResourceGuid
                AND AL4.Guid=AL1.ParentResourceGuid
                AND AL2._ResourceGuid=AL6._ResourceGuid)
        GROUP BY AL2.Type, AL4.Name, AL4.Guid, AL5.Name
          HAVING (Sum (ISNULL(AL6."Quantity Added", 0)) - Sum (ISNULL(AL6."Quantity Taken",0))) < 5
           ORDER BY AL5.Name, AL2.Type

4. Select "Daily" for the policy's schedule.
    
5. Now we'll tailor the notification. 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 Type, Description, Location, and quantity remaining In Stock, so we can add any or all of these to our email notification for replenishment.

Under Automated Actions > Add action type, select E-mail Automated Action and click Add.

Fill in the dialog with the following properties:

Name: Stock Replenishment Alert

Enabled: (checked)

Execute: (Once Per Row)

To Address: <the desired address>

Subject: The %DS:Description% in %DS:Location% needs replenishment

Message: The %DS:Description% in %DS:Location% needs replenishment. There are only %DS:In Stock% left.

6. Apply changes and test the Notification Policy.

*note: Try experimenting with the other action types available. If you have Altiris Helpdesk, experiment with creating an Incident.

Figure 11. Configuring the email action for the Notification Policy.

Click to view.

Figure 12. The "Stock Replenishment" Notification Policy.

Click to view.

Figure 13. The resulting email.

Click to view.

Using the New Resource Type

1. From the Resource tab, select Resource Management>Asset Types>IT>Consumable.
    
2. Alternate between the two picker reports to see how you can display all items or restrict the display to a specific location.
    
3. Open a new item to edit its record.
    
4. Make sure to categorize the item by using the type and add any quantities necessary, along with date and user data. Modify quantities as needed on an ongoing basis. The report will always reflect the balance.

Figure 14. Edit the quantities and other data on the item in order to keep an accurate inventory.

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.