Endpoint Protection

I'm trying to find both where it's stored and how to extract the list of Hardware Devices (name and device IDs) from the database.

I may have narrowed it down to the BASIC_METADATA table, though I'm not sure.

I have found refereces to the names of the devices where the column 'TYPE' is "OpDevice", but I can't make heads or tails of the value in the 'Contents' field.

It appears to be encoded somehow, but the schema document only says for that field "The XML content of the schema object".

Anyone know how to get the actual values?  I need to export that to a spreadsheet.

See if the DB schema helps:

Symantec Endpoint Protection 12.1.6 database schema

It doesn't help at all.

In fact, it makes zero mention of where policies and their componets are stored.

Additionally, I already said I looked at it to find the decription of the Contents field which is listed as "The XML Content of the Schema Object".

That document doesn't help me in the least.

Then my suggestion to you is to open a support case. They have plenty of skilled folks who are of much greater use than myself.

Unfortunately that seems not to be the case.

So far their answer has been that they're not trained on using an SQL database, and that I should talk to my local database admin, which suffice it to say is 100% unhelpful.

I think you're right concerning BASIC_METADATA. If I understand you correctly you cannot read the CONTENT column containing XML properly. The reason is that the XML strings are shown in hex code.

In SQL Server the column has a rather old datatype (IMAGE) you cannot handle very well. As I see it, you have to convert the CONTENT column to VARBINARY and then to VARCHAR (but perhaps there is a more elegant solution):

SELECT CONVERT( VARCHAR(256), CONVERT (VARBINARY(MAX), CONTENT)) 
FROM BASIC_METADATA
WHERE TYPE = 'OpDevice'

Then you have the XML representations of the Hardware devices which of course you have to parse. I'm sure there is a more straightforward solution in SQL Server to get the values you want directly , but I'm not a SQL Server pundit.

If you are using the embedded database, open the dbisqlc.exe tool and run the following command:

SELECT CONVERT( VARCHAR , CONTENT) FROM BASIC_METADATA
WHERE TYPE = 'OpDevice' ;
OUTPUT TO C:\output.txt;

Here it's possible to convert directly to VARCHAR. The result will be written in the file 'C:\output.txt'. dbisqlc does not need a conversion at all to show the XML data in its window but if you want to export through OUTPUT you have to convert to VARCHAR.

In the XML data, device IDs are called "DeviceInstanceId" and class IDs "DeviceClassGuid".

HTH!

This is exactly what I needed.  Thank you so much.

XML is perfectly fine to work with, I could do that manually or throw together a quick parser.