CI Assemblies/Configurations – How To Use Them – Part 2

In part 1 of this series (http://www.joatit.com/wordpress/?p=539) I described the relationships and use of the modules associated with the CI Assemblies/Configurations module. I also described how I would implement a real-world example using these modules and eluded to the need to automate some functionality through business rules and be aware of certain issues when it comes to reporting.

In this post, I want to cover off those points and hopefully provide some additional resources to help you get the most out of the product

Business Process Automation

In the first post in this series I suggested it would be a sensible idea to have a CI Assembly/Configuration for each client to manage their mobile assets. Fine, but what a pain in the proverbial if for every client you create you have to manually create a CI Assembly/Configuration and then link it to that client! At this point I would normally suggest a business rule but unfortunately we have a problem – namely Service Desk Express does not allow you to create business rules based on the Assembly Clients module which would mean we could create the CI Assembly/Configuration but couldn’t link it to the client. So we need an alternative – enter the Integration Console:

CC – Create and Link CI Assemblies to Clients (click here to download).

What we need to do to achieve this is create a package that contains a step and a substep:

cc-create-and-link-ci-assemblies-to-clients-01

Initiator:

We will set the Initiator to be a schedule running every 15 minutes (as shown below) such that within 15 minutes of creating a new account your CI Assembly/Configuration will be created and linked.

cc-create-and-link-ci-assemblies-to-clients-02cc-create-and-link-ci-assemblies-to-clients-03

Source:

The key to this package is the source for the Create CI Assemblies Step. We need to run a SQL query to only return the clients that don’t have a matching CI Assembly/Configuration so we need to use the ODBC adapter type as shown below:

cc-create-and-link-ci-assemblies-to-clients-031cc-create-and-link-ci-assemblies-to-clients-04

using the SQL query:

SELECT C1.[CLIENT], C1.[COMPANY], C1.[DEPT], C1.[BILL TO], C1.[BLDNG], C1.[OFFICE] FROM [_SMBDA_].[_CUSTOMER_] C1 LEFT JOIN [_SMDBA_].[_CONFIG_] C2 ON C1.[CLIENT] = C2.[CONFIG] WHERE C2.[CONFIG] IS NULL AND C1.[_INACTIVE_:] = 0

Target:

We then map the target to the CI Assembly view using the SDE adapter type as shown below:

cc-create-and-link-ci-assemblies-to-clients-05cc-create-and-link-ci-assemblies-to-clients-06cc-create-and-link-ci-assemblies-to-clients-07

So that creates the CI Assemblies/Configurations for the Clients – now we need the sub step to link them to each other.

Initiator:

As can be seen below we only want the ub step to run if the parent succeeds:

cc-create-and-link-ci-assemblies-to-clients-08

Source:

None/Not used.

Target:

Finally we map the target to the Assembly Clients view using the SDE adapter type as shown below:

cc-create-and-link-ci-assemblies-to-clients-09cc-create-and-link-ci-assemblies-to-clients-10cc-create-and-link-ci-assemblies-to-clients-11

This will create and link a CI Assembly for every client that does not already have one based on matching the Client ID with CI Assembly ID  as mentioned in my previous post.

Orphaned Configurations and CSBRs

I wrote a post a while back on how to warn users if the CI Assembly/Configuration they are saving is NOT linked to a client. I would also suggest that you implement this business rule as well.

Reporting

Now if you have implemented something similar to what I suggested in the previous post there are a couple of issues relating to reporting that you need to be aware of. Specifically, if management ask for a list of all assets and their users you need to be very careful how you handle the many to many relationship between Clients and CI Assemblies otherwise you could end up with duplicate assets in the cases when an asset is used by multiple clients. So what are your options:

Exclude duplicates

Your first option is to exclude these circumstances altogether. Let me explain:

SELECT II.[Sequence], II.[Asset/Tag #], II.[Serial #], II.[Category ID], II.[Part Description], CC1.[Client ID], CC1.[Configuration ID]
FROM [_SMDBA_].[Inventory Items] II LEFT JOIN [_SMDBA_].[Configurations] C ON C.[Sequence] = II.[Seq.Configuration]
LEFT JOIN [_SMDBA_].[Configurations Clients] CC1 ON CC1.[Seq.Configuration] = C.[Sequence]
WHERE II.[InActive:] = 0 AND (SELECT COUNT(*) FROM  [_SMDBA_].[Configurations Clients] CC2 WHERE CC2.[Seq.Configuration] = CC1.[Seq.Configuration]) < 2

The advantage of this is that your report is accurate in terms of assets as long as you appreciate that your assets do not include shared assets. The disadvantages are pretty obvious in that gaining a holistic view of your assets using this method is almost impossible and as such I would not recommend it.

Show first user only

An alternative solution is to only show the asset once and assume that the first person sharing an asset is the person responsible for it. This is done using an embedded SELECT statement in your report:

SELECT II.[Sequence], II.[Asset/Tag #], II.[Serial #], II.[Category ID], II.[Part Description],
(SELECT TOP 1 CC.[Client ID] FROM [_SMDBA_].[Configurations Clients] CC WHERE II.[Seq.Configuration] = CC.[Seq.Configuration]) As 'Client ID',
(SELECT TOP 1 CC.[Configuration ID] FROM [_SMDBA_].[Configurations Clients] CC WHERE II.[Seq.Configuration] = CC.[Seq.Configuration]) As 'Configuration ID'
FROM [_SMDBA_].[Inventory Items] II
WHERE II.[InActive:] = 0

The big advantages here are that there is no customisation required and critically the correct number of assets are shown on report (including shared assets). The big disadvantage is that it makes a very large and often incorrect assumption – i.e. that the first person sharing an asset is the person responsible for it. This simple isn’t true in many circumstances and if you are using this information for billing then you’re potentially opening up a whole can of worms. Again, not my preferred option.

Add Person Responsible field and CSBR

My preferred option is to add a new field (Seq.Responsible) to the Inventory Item/Configuration Item module – a foreign key to the Client module (obviously with its relevant virtual fields) that is used to store the Person Responsible for the asset as shown below:

responsibleforeignkey

Add this field on to your Inventory Item/Configuration Item form and you can then create a simple CSBR that on change of Configuration/CI Assembly, prefill the Person Responsible with the first person linked to that Configuration/CI Assembly. The massive advantage here is that although the person is prefilled you have the ability to overrule the suggestion. What is more you can even select a departmental manager who does not use the shared asset in question. Now you have the ability to report directly off the Inventory Item/Configuration Item module giving you accurate asset information that CAN be used for billing without arguement.

CSBR: CI – Prefill Responsible Client On Change Of CI Assembly

When On Data Change occurs for field CI Assembly ID where {TR,CI Assembly ID} NotEqual <Blank>
Populate Current Form - JQS - Configuration Item (the new form with the Seq.Responsible field added) setting Seq.Responsible = {DB,Assembly Clients,Seq.Client,"Seq.CIAssembly" = {TR,Seq.CIAssembly} DB}
Assigned To JQS - Configuration Item

Summary

CI Assemblies provide a highly flexible solution to the management of assets in Service Desk Express but it is essential that robust business processes are put in place to combat the risk of poor data. As always comments, positive or negative, are always welcome.

Advertisements