Micro Focus Service Manager (HPSM) files automated analysis – part I

While auditing an ITSM process automated with Micro Focus Service Manager (HPSM), such as Incident or Change Management, an auditor may face the necessity to look through the attached files. This is especially cumbersome when a test population includes hundreds of elements unless we use automation. But first, we need to dump all the files.

Here is the (incomplete) manual on How to extract an attachment from SYSATTACHMENTS table. Why is it incomplete and what is missed? Well… Let me tell you the whole story and guide you through the unapparent pitfalls of this task using the Change Management process as an example.

For the sake of simplicity, let’s assume that we are tasked with the analysis of changes, each of which must have test evidence attached as a separate document. And, for sure, we want to perform a massive test, not limiting ourselves to a short sample of changes. Moreover, we have access to the HPSM’s database and are used to extract data with Microsoft SQL Server Management Studio.

First, we need to extract the population of files from HPSM. The beginning is easy, you select a test scope relevant change IDs from the changes table and join the SYSATTACHMENTS table containing the attached files. Surprisingly, this is where the first problem pops up. The files are stored in the table as encoded BLOB objects, meaning you can’t just dump and open them. Moreover, the SQL Server Management Studio can’t fully extract big files due to the limit on the length of extracted data (which was 43,680 symbols in my case).

Bulk Copy Program (BCP) can solve this problem. The biggest advantage of it is that while extracting data from a database it does not limit the length of data being extracted.

However, two points need attention. At first, the format of the data fields being extracted needs to be explicitly provided. Below is an example of this file.

14.0
6
1 SQLCHAR 0 255 "," 1 TOPIC           ""
2 SQLCHAR 0 255 "," 2 FILENAME        ""
3 SQLCHAR 0 25  "," 3 SEGMENT         ""
4 SQLCHAR 0 0   "," 4 DATA            ""
5 SQLCHAR 0 25  "," 5 COMPRESSED_SIZE ""
6 SQLCHAR 0 25  "," 6 SIZE            ""

Secondly, the BCP command is executed in the command line, which has its length limits. This means we cannot just insert hundreds of change IDs into the command body. Let’s wrap it up with .BAT script extracting data change by change and assembling them into one file.

Besides, considering that some files may be segmented, we extract data ordered by CHG ID, FILENAME and SEGMENT to ease further processing of raws. This will help to recover segmented files that have to be preliminarily assembled.

All in all, the resulting script is below.

@echo off
setlocal enabledelayedexpansion
DEL C:\Temp\FIles_from_HPSM.txt
DEL C:\Temp\temp.txt
FOR \F “tokens=* delims=” %%i IN (C:\Temp\CHG_list.txt) DO (
	bcp “select TOPIC, FILENAME, SEGMENT, DATA, COMPRESSED_SIZE, SIZE from hpsm.dbo.SYSATTACHMEM1 where TOPIC=%%i order by FILENAME, SEGMENT” queryout C:\Temp\temp.txt -T -S hpsm_server\hpsm_db_instance -f C:\Temp\format.fmt
	type C:\Temp\temp.txt >> C:\Temp\FIles_from_HPSM.txt
)
DEL C:\Temp\temp.txt

This is the first part of the article. The second part can be found here (link).