I got a call from a colleague (@JoeBurrows) the other week asking if I knew of a way to work out what Management Pack the list values were stored in so the customer could make sure they had all their lists backed up.
On the surface this seemed to be a very simple exercise as the lists node in the library workspace of the console shows the list and the Management Pack it is associated with, right?
The management pack listed in the lists node shows the management pack that contains the definition of the list but not necessarily the lists data as the management pack may be sealed.
When you first edit or add a value to a list in a sealed management pack it will ask you to select a management pack that you would like to store the list values in. This will be the management pack that will store all values for this list from now on.
So how do you tell what the administrator who edited this list first selected when they were confronted by this screen?
To answer that I think it best that we get a better understanding of how the data is stored in the database.
The SCSM “ServiceManager” database is designed in such a way for lists to allow one to many relationships for list values. “But that’s mad!” I hear you scream. Surely a value in a list is a one to one relationship. A change status of “Completed” is a single value so why would there be need for this one value to have any other relationship?
The answer is Language.
In English the value you may want is “Completed” but the value may need to be “Completado” in Spanish or even “lõpule viidud” in Estonian. (Just to pick two languages at random) So there has to be a way for the system to find a particular list value in many different languages. So to do this the ServiceManager database stores the list definition in one table and the list values in another.
So each entry in a list can have a text value for each language. This is great if your installation is going to be used by several nationalities within your organization. For most of us this is not the case and we just operate in a single language within our deployment but i hope it makes sense why this is done.
Now that we know this, we can search for the management pack value in the right location, namely the Language table or “LocalizedText” table using a query like:
SELECT languagecode, ManagementPackId, LTValue, LastModified
WHERE LTValue like ‘%Test Support Group Value%’
(Where %Test Support Group Value% is the list value you are looking for)
Once you have the Management Pack ID you can then lookup the name of that management pack using SMLets with a command like:
Get-SCSMManagamentPack –id 08847985-C76E-05D6-1514-0710D637E3CA
I’ll see if I can update this post in the future with a better script that will make this a lot simpler.