While the HP Service Manager client is a great tool, sometimes you still need to run a direct SQL query on the data. You may want the data to appear on some external report or you just need some “quick and dirty” numbers to send on to management. If you manage HPSM environments long enough, you will invariably find yourself connecting to the database to run queries from time to time. And that’s when you’ll discover that HPSM has been storing the data you need in a difficult-to-query array.
Although the example in this article applies to HP Service Manager, the method can be used whenever delimited data needs to be split and joined to another table in Microsoft SQL Server.
In HP Service Manager, lists of data associated to a particular record are often stored in a proprietary data type called an “array” rather than a relational, foreign-key table. If you’re using Microsoft SQL Server as your backend database, you will find array data stored in TEXT or NTEXT type fields as a list (Oracle databases use CLOB types). It would be much more beneficial if this data could be represented as relational data so that it could be reported on or joined to another table for more information.
Our Example
Bill, our CFO, would like to send an e-mail to everyone listed as a Request Management approver in HP Service Manager reminding them of a budget freeze. He asks us for a list of approvers and their e-mail addresses so that he can target the e-mail rather than send a company-wide communication.
Special Note: Our example uses an out-of-box installation of HP Service Manager 9.20 and sample data.
The Data
Request Management groups (both members and approvers) are located in the ocmgroups file in HP Service Manager. A quick look at a Request Management group record in HPSM shows us members and approvers as lists. Approvers are displayed as operator names. Additionally, we need e-mail addresses. Both contact names that Bill would recognize and e-mail addresses can be found in the operator file.
HPSM Request Mgmt Group
A glance at the dbdict record for the ocmgroups and operator files shows us that the fields we want are stored in the OCMGROUPSM1 and OPERATORM1 tables within SQL Server.
We should be able to simply query the OCMGROUPSM1, join to OPERATORM1 and get the names and e-mail addresses, right? Unfortunately, it’s not quite that easy.
The Problem
The OCMGROUPSM1 table contains a TEXT type field called APPROVERS that can’t be directly joined to the OPERATORM1 table. The entire list of approvers is stored in a single field with a non-printable character delimiter. The character (both SQL Server and Oracle) is a new-line character (ASCII decimal value of 10). A simple join query will not work for our situation.
OCMGROUPSM1
We need to be able to represent the array data in a way that can be joined to another table.
SQL Server Array Splitting Function – fnSplit
For the first part of our solution, we will build a function that splits array data into something more understandable. We will actually go one step further and build a function that can split data on any delimiter, not just the new-line character.
In tackling this for the first time, I started with a simple SQL Server function that I found via an Internet search. The source code is as shown below:
CREATE FUNCTION [dbo].[fnSplit](
@sInputList VARCHAR(MAX) -- List of delimited items
, @sDelimiter VARCHAR(MAX) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(MAX))
BEGIN
DECLARE @sItem VARCHAR(MAX)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,
CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,
CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),
LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
To see how this function works, create it and execute the following query:
SELECT * FROM dbo.fnSplit('Bill,Janet,Sarah,Tiffany,Vanessa,William,Xander', ',')
The data is split by comma-separated values and returned as a list.
In our example, the character delimiter is a non-printable new-line character. We can use SQL Server’s CHAR() function to manage this. CHAR() accepts a decimal value and returns the corresponding ASCII character. The new-line character has a decimal value of 10 and can be represented in a SQL Server query as CHAR(10). You will see how this is used in the next section.
Using fnSplit in a SQL Server Query using CROSS APPLY
Our goal is to get the data into a relational format that we can join to another table. We can use SQL Server’s CROSS APPLY syntax to accomplish this. CROSS APPLY allows us to join a table to a table-value function (TVF) result set within SQL Server.
SELECT M1.NAME, S.ITEM
FROM OCMGROUPSM1 M1
CROSS APPLY fnSplit(M1.APPROVERS, CHAR(10)) S
Our result should now be something that we can much more easily join to another table as shown in the screen shot.
SELECT CROSS APPLY
We need only join this result to the OPERATORM1 table to get our extra information – the CONTACT_NAME and EMAIL fields.
SELECT M1.NAME, S.ITEM, O.CONTACT_NAME, O.EMAIL
FROM OCMGROUPSM1 M1
CROSS APPLY fnSplit(M1.APPROVERS, CHAR(10)) S
JOIN OPERATORM1 O ON O.NAME = S.ITEM
Cleaning up the Result
Just for polish, we can clean this data up to provide our CFO, Bill, with the result of his specific request. We currently show duplicate names and e-mail addresses when the same person might approve on behalf of multiple Request Management groups.
We’ll just limit the result set to the columns we need, use a SELECT DISTINCT and an ORDER BY clause to get Bill the answer in a format that he wants.
SELECT DISTINCT O.CONTACT_NAME, O.EMAIL
FROM OCMGROUPSM1 M1
CROSS APPLY fnSplit(M1.APPROVERS, CHAR(10)) S
JOIN OPERATORM1 O ON O.NAME = S.ITEM
ORDER BY CONTACT_NAME
Conclusion
Array data can be difficult to work with especially when attempting to access it through database queries. Breaking components out of the model file, generating pending approver lists from the Approval file, and many other challenges can be solved by adopting the technique laid out in this article. The best part is that the fnSplit function is added to your SQL Server database and used whenever you need it.
While the example that was used in this article is specific to the HP Service Manager application, I have encountered numerous other databases where data is stored as delimited lists rather than as relational data. The method of using the fnSplit function and CROSS APPLY is universal.
In a future post, we will modify the fnSplit function to include NULL values (an empty value between delimiters) as well as an index column identifying the position of the item in the list.