CVE Fields in SQL API query with fn_ExtractFields

Reporting
1

When trying to do a SQL query with the fn_ExtractField I am having trouble getting the CVE information from the dbo.bes_object_defs view. Unfortunately IBM does NOT support helping with any queries (relevance or SQL), and they suggested either getting professional services or going to the forum.

Has anyone else used the fn_ExtractFields to gather CVE information? Here is the query that sums up what I have tried so far. Source Severity and Source Release Date both work. None of the CVEs work.

SELECT
                Sitename
                ,ID
                ,Name
                ,dbo.fn_ExtractField('Source Severity', 0,O.Fields) as [Severity]
                ,dbo.fn_ExtractField('Source Release Date', 0,O.Fields) as [Released]
                ,dbo.fn_ExtractField('CVE Names', 0,O.Fields) as [CVE1]
                ,dbo.fn_ExtractField('CVENames', 0,O.Fields) as [CVE2]
                ,dbo.fn_ExtractField('CVEID', 0,O.Fields) as [CVE3]
                ,dbo.fn_ExtractField('CVE ID', 0,O.Fields) as [CVE4]
                ,dbo.fn_ExtractField('CVE', 0,O.Fields) as [CVE5]
                ,dbo.fn_ExtractField('cve', 0,O.Fields) as [CVE6]
                ,dbo.fn_ExtractField('cve id', 0,O.Fields) as [CVE7]
                ,dbo.fn_ExtractField('cvenames', 0,O.Fields) as [CVE8]

FROM
                [dbo].[BES_OBJECT_DEFS] O
WHERE
Sitename LIKE '%Vulnerabilities to Win%'
AND
                ContentType = 0
                AND
                IsFixlet = 1
                AND
                NOT ParentID = 1

Anyone knows how to see a list of the field names that are available in a row/xml through the SQL, that would be amazing.

Thank you.

Chris

2

To get the full contents of the Fields column, you would use:

CONVERT(xml,CONVERT(varbinary(max),Fields))

Most of this data is already extracted in the new 9.5.5 schema where you can access it without the ExtractFields function via tables EXTERNAL_FIXLETS and EXTERNAL_FIXLET_TRANSLATIONS.

3

This is out of my element but I have a suggestion for these –

When you look at the XML some of the fields start with an index of 0 and some start with an index of 1:

<Fields>
	<Name>Issuer</Name>
	<Number>0</Number>
	<Contents></Contents>
</Fields>

Versus

<Fields>
	<Name>MIME_x-fixlet-modification-time</Name>
	<Number>1</Number>
	<Contents>Mon, 06 Jan 2014 19:12:31 +0000</Contents>
</Fields>

The second parameter of fn_ExtractField is the “Number” of the field to extract. In my notes I have that, at least for fixlets in the “Patches for Windows” site, that the MIME_x-fixlet-cve field is index 1 and not index 0, the other CVE fields may be similar. As such, to extract that field you would need to use something similar to: dbo.fn_ExtractField('MIME_x-fixlet-cve',1,fields) note the 1 as the second parameter.

The easiest way to investigate what the properties are is to open SQL, find the fixlet you want in the dbo.BES_OBJECT_DEFS view and copy the value from Fields column into a Hex to ASCII converter: Hex to ASCII Text String Converter

image
image943×643 47.1 KB