(imported topic written by SystemAdmin)
Hello,
I am trying to generate a query to extract the machine information in order to generate a cross product reports website. I was able to come up with a query extracting the Global Properties fairly easy as they are stored as text
SELECT [Computer Name] as
"Server Name", [Primary IP Address] as
"Management IP", [RAM] as
"RAM", [CPU] as
"CPU", [OS] as
"Operating System", [Uptime] as
"Uptime" FROM ( SELECT t1.computerId , t2.Name , t2.Value FROM COMPUTERS AS t1 JOIN BES_COLUMN_HEADINGS AS t2 ON t1.computerId = t2.computerId ) p PIVOT ( MAX([Value]) FOR Name IN ([Computer Name], [RAM], [CPU], [Primary IP Address], [OS], [Uptime]) ) AS pvt ORDER BY computerId;
Now, I would like to pull some Properties from Custom Analysis but they appeared to be structure differently. I read through the Database API reference guide and I came up with a query to pull the Analysis from the database(see below) and from what I gathered the data I am looking for should be stored within the “Fields” column but I am having hard time extracting the actual machine information.
SELECT ID,dbo.fn_ExtractField(
'Fixlet Type', 0, Z.Fields) as
"Fields",Name FROM BES_OBJECT_DEFS AS Z where dbo.fn_ExtractField(
'Fixlet Type', 0, Z.Fields) like
'Analysis' and UserName like
'...';
Any help would be appreciated. Thank you.