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

In part 1 of this series ( 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:



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.



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:


using the SQL query:



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


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


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



None/Not used.


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


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.


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:


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


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.

9 thoughts on “CI Assemblies/Configurations – How To Use Them – Part 2

  1. Hey Alan, Excellent article. I’ve tried it out and it works great but I have a Newb Question:

    In the below statement, what does “C1” & “C2” refer to? I not familiar with the usage of “C1” & “C2” etc…


  2. Hi CJW,

    Really glad you enjoyed the article.

    The C1 and C2 are simply aliases to save typing so in essence you could replace [_SMBDA_].[_CUSTOMER_] wherever you see C1 and [_SMDBA_].[_CONFIG_] wherever you see C2.

    Hopefully that makes sense.



  3. Thanks Alan, that makes a lot of sense. I hadn’t touched SQL before I became our company’s SDE Administrator so I’m still just picking it up as I go. Any advice on where to go to learn more? I’m learning a lot just from trial and error but would like a better understanding as I think it might open up some cooler things with SDE if I know more SQL.



  4. Hi Alan

    This article is great, question can the same principle be added to link companies to assets?


  5. Hi Dave,

    Apologies but I am not sure I understand the question. Assets (Configuration Items) ARE already linked to Companies via the CI Assembly (or Configuration in older versions). As such you can easily run a report showing all assets for a given company.

    If I have not understood then let me know and we will go from there.



  6. BMC Remedy verisoin 9.6 retrieving record please wait
    Server Error in ‘/SDE’ Application.

    [Microsoft][ODBC SQL Server Driver][SQL Server]Setuser failed because of one of the following reasons: the database principal ‘_SMDBA_’ does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission.

  7. Hi Alan,
    Great information. I am trying to use the Show First User Only script and I thought I had it updated correctly, but it is still not working. Here is what I have:
    SELECT [_SMBDA_].[_INVENTOR_].[Sequence], [_SMBDA_].[_INVENTOR_].[Asset/Tag #], [_SMBDA_].[_INVENTOR_].[Serial #], [_SMBDA_].[_INVENTOR_].[Category ID], [_SMBDA_].[_INVENTOR_].[Part Description],
    (SELECT TOP 1 [_SMBDA_].[_CONFIGDET_].[Client ID] FROM [_SMDBA_].[Configurations Clients] [_SMBDA_].[_CONFIGDET_] WHERE [_SMBDA_].[_INVENTOR_].[Seq.Configuration] = [_SMBDA_].[_CONFIGDET_].[Seq.Configuration]) As ‘Client ID’,
    (SELECT TOP 1 [_SMBDA_].[_CONFIGDET_].[Configuration ID] FROM [_SMDBA_].[Configurations Clients] [_SMBDA_].[_CONFIGDET_] WHERE [_SMBDA_].[_INVENTOR_].[Seq.Configuration] = [_SMBDA_].[_CONFIGDET_].[Seq.Configuration]) As ‘Configuration ID’
    FROM [_SMDBA_].[Inventory Items] [_SMBDA_].[_INVENTOR_]
    WHERE [_SMBDA_].[_CONFIGDET_].[InActive:] = 0

    If you can help, I would appreciate it.
    Thank you.

  8. Hi Alan,

    Great Blog!! It really helped me answer most of my SDE questions.

    Anyway, i would like to seek your idea on how would SDE v 10 tracked the movement of asset from one client to another? Any idea on how to accomplish this? We’re using CI assembly as a grouping for assets, we didnt use clientid per assembly but rather different convention.

    Thanks a lot!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s