Data Loss Prevention

View Only

SQL Query Showing Server Settings and Values – Enforce Console

Recommend

Feb 18, 2019 12:22 PM

Christopher Bradburn

The Context

Continuing the journey of mapping out DLP and its relationship to the Oracle database it sits on, we today look at how the server settings can be viewed and updated (if needed) in the database. As usual, the full query is at the bottom, and we break it down step by step throughout the article.

While working on a larger project that involves optimising DLP, I discovered there are several settings that need to be changed in the web console. As a big fan of automation, naturally I wanted to find out the source of these settings. Once I found out how these settings were being updated in the background, I could then manipulate them with a script. The screen in question is illustrated below.

Naturally, through the process of checking through install files and property files etc. I decided to check the database. This is where the ATTRIBUTE table was discovered.

It is important to note that these settings are instanced per discover server. I.e the settings for one discover server are treated separately by the database. This query deals with isolating one Discover Server, but it shouldn’t be too taxing to adapt and look up another instance.

The Tables

As mentioned above, the meat of this query comes from the ATTRIBUTE table. However, as usual, there are stipulations that affect how to find the information we need. We also need to bring in another table, SETTING. As mentioned above, the database keeps each Discover Server’s settings separate. The column that achieves this is SETTINGID. An example of the completed query results are below.

The Select

Slightly confusingly, the two tables we draw from both have a NAME column. The SETTING table holds the name of the Discover monitor whereas the ATTRIBUTE table holds the name of the settings that can be configured. This can be circumvented by aliasing the column, just remember to make sure they’re distinct.

SELECT sett.NAME monname
,attr.NAME
,attr.VALUE
,attr.SETTINGID

FROM SETTING sett

The Join and Where

As mentioned previously, we need to join the SETTING table to the ATTRIBUTE table to bring us all the information we need. We join the tables on SETTINGID as this common feature is what the database uses to determine which settings we’re looking at.

In this instance, the SETTINGID for the Discover Server we’re examining is ‘18’. Therefore, we’re going to stipulate this is the SETTINGID we want to see.

Another note is that all instances of discover servers are recorded, regardless of whether they have been deleted or not. This means that without filtering, the resulting dataset can be quite cumbersome.

LEFT JOIN ATTRIBUTE attr ON attr.SETTINGID = sett.SETTINGID

WHERE sett.SETTINGID = '18'

ORDER BY attr.SETTINGID
;

The Conclusion

With the full query now built, we can see which settings are configured in the Enforce Console, without having to log into the web portal and navigate the menus.

The applications for this are numerous, particularly in regards to reporting and ensuring config remains consistent across an estate.

If you, or the client also has permission to edit the database, this query could also potentially identify fields which can be updated to improve performance for DLP scanning speeds etc. Although we advise a great deal of caution around editing the DLP database.

WARNING

EDITING THE DLP DATABASE IS UNSUPPORTED. WE DO NOT ADVISE THIS.

Once again, I hope this helps.

Thanks

Chris

SELECT sett.NAME monname
,attr.NAME
,attr.VALUE
,attr.SETTINGID

FROM SETTING sett

LEFT JOIN ATTRIBUTE attr ON attr.SETTINGID = sett.SETTINGID

WHERE sett.SETTINGID = '18'

ORDER BY attr.SETTINGID
;

Statistics

0 Favorited

5 Views

0 Files

0 Shares

0 Downloads

Tags and Keywords

Comments

Paul Greenberg

Jul 21, 2019 12:21 PM

Great article! Here are some other queries one might need:

Queries for all setting names:

SELECT DISTINCT sett.NAME setting_name FROM SETTING sett;

The output:

Language
agentManagement
endpointLocations
enforceProxySetting
eurPortalAuthenticationToken
eurPortalConnectionSetting
fullyQualifiedManagerHost
generalSettings
kerberosAuth
managementConsole
matrixSettings
monitor:0
...
monitor:NNNN
passwordEnforcement
processControl
reports
smtp
telemetry
udpCredentialsSetting

Queries for all settings:

SELECT sett.NAME server, sett.SETTINGID, attr.NAME, attr.VALUE, attr.SETTINGID
        FROM SETTING sett
        LEFT JOIN ATTRIBUTE attr ON attr.SETTINGID = sett.SETTINGID
        ORDER BY attr.SETTINGID
;

Queries for setting from monitors only:

SELECT sett.NAME setting_name, attr.NAME conf_key , attr.VALUE conf_value
        FROM SETTING sett
        LEFT JOIN ATTRIBUTE attr ON attr.SETTINGID = sett.SETTINGID
        WHERE sett.NAME LIKE 'monitor%'
        ORDER BY attr.SETTINGID
;

Queries for DLP Enforce name:

SELECT sett.NAME setting_name, attr.NAME conf_key , attr.VALUE conf_value
        FROM SETTING sett
        LEFT JOIN ATTRIBUTE attr ON attr.SETTINGID = sett.SETTINGID
        WHERE sett.NAME = 'fullyQualifiedManagerHost'
        ORDER BY attr.SETTINGID
;