Query to Fetch Browser and Policy Data from Endpoint Central (Local DB Import)
Requirement:
The requirement is to fetch Browser and Policy details from Endpoint Central into Analytics Plus using a custom query via local database import, enabling detailed reporting and analysis on browser usage and policy management.
Solution:To achieve this, we need to connect to the Analytics Plus local database (PostgreSQL) and use the custom query option to fetch the required Endpoint Central data into a table format.
Steps to Import data from Local Database :Navigate to Analytics Plus → Endpoint Central workspace → Import Data → Local Database
Enter the following connection details:
Hostname:
Port:
Database Name:
Username:
Connection type : Data Import
Note:Ensure that you select the appropriate database based on your environment where the Endpoint Central data is stored, and that you have the necessary database access credentials.
Query :
Deployed Extension Restriction Policy :
SELECT Resource.NAME as "Hostname", BranchOfficeDetails.BRANCH_OFFICE_NAME as "Remote Office", COALESCE(ResourceMACIP.IP_ADDRESS, 'N/A') as "IP Address",
Browsers.BROWSER_NAME as "Browser Name", COALESCE(admQuery.PROFILE_NAME, 'No Profile Deployed Yet') as "Deployed Extension Restriction Policy"
FROM Resource
INNER JOIN ManagedComputer ON Resource.RESOURCE_ID = ManagedComputer.RESOURCE_ID
INNER JOIN ManagedResourceBSP ON ManagedComputer.RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
INNER JOIN BranchMemberResourceRel ON BranchMemberResourceRel.RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
INNER JOIN BranchOfficeDetails ON BranchOfficeDetails.BRANCH_OFFICE_ID = BranchMemberResourceRel.BRANCH_OFFICE_ID
INNER JOIN ResourceToBrowserRel ON ManagedResourceBSP.MANAGED_RESOURCE_ID = ResourceToBrowserRel.MANAGED_RESOURCE_ID
INNER JOIN InstalledBrowsers ON ResourceToBrowserRel.APPLICATION_ID = InstalledBrowsers.APPLICATION_ID
INNER JOIN BrowsersGroup ON InstalledBrowsers.BROWSER_GROUP_ID = BrowsersGroup.BROWSER_GROUP_ID
INNER JOIN Browsers ON BrowsersGroup.BROWSER_ID = Browsers.BROWSER_ID
LEFT JOIN (
SELECT Profile.PROFILE_NAME, RecentProfileForResource.RESOURCE_ID, AddonManagement.BROWSER_TYPE FROM Profile
INNER JOIN RecentProfileForResource ON Profile.PROFILE_ID = RecentProfileForResource.PROFILE_ID
INNER JOIN ResourceToProfileHistory
ON RecentProfileForResource.PROFILE_ID = ResourceToProfileHistory.PROFILE_ID
AND RecentProfileForResource.COLLECTION_ID = ResourceToProfileHistory.COLLECTION_ID
AND RecentProfileForResource.RESOURCE_ID = ResourceToProfileHistory.RESOURCE_ID
INNER JOIN CfgDataToCollection ON RecentProfileForResource.COLLECTION_ID = CfgDataToCollection.COLLECTION_ID
INNER JOIN ConfigData ON CfgDataToCollection.CONFIG_DATA_ID = ConfigData.CONFIG_DATA_ID
INNER JOIN ConfigDataItem ON ConfigData.CONFIG_DATA_ID = ConfigDataItem.CONFIG_DATA_ID
INNER JOIN AddonConfigDataItemRel ON ConfigDataItem.CONFIG_DATA_ITEM_ID = AddonConfigDataItemRel.CONFIG_DATA_ITEM_ID
INNER JOIN AddonManagement ON AddonConfigDataItemRel.ADD_ON_MANAGEMENT_ID = AddonManagement.ADD_ON_MANAGEMENT_ID
WHERE Profile.PROFILE_TYPE = 64025 AND RecentProfileForResource.MARKED_FOR_DELETE = 0
) admQuery ON Resource.RESOURCE_ID = admQuery.RESOURCE_ID AND admQuery.BROWSER_TYPE =
CASE
WHEN Browsers.BROWSER_ID IN (20, 21, 22, 23, 24) THEN 1024
ELSE Browsers.BROWSER_ID
END
LEFT JOIN ResourceMACIP ON Resource.RESOURCE_ID = ResourceMACIP.RESOURCE_ID
WHERE ManagedComputer.MANAGED_STATUS = 61 AND ManagedResourceBSP.INSTALLATION_STATUS = 22 AND ManagedResourceBSP.MANAGED_STATUS = 61
ORDER BY Resource.NAME, Browsers.BROWSER_NAME, admQuery.PROFILE_NAME
Deployed Browser Customization Policy :
SELECT Resource.NAME as "Hostname", BranchOfficeDetails.BRANCH_OFFICE_NAME as "Remote Office",
COALESCE(ResourceMACIP.IP_ADDRESS, 'N/A') as "IP Address",
Browsers.BROWSER_NAME as "Browser Name",
COALESCE(bcQuery.PROFILE_NAME, 'No Profile Deployed Yet') as "Deployed Browser Customization Policy"
FROM Resource
INNER JOIN ManagedComputer ON Resource.RESOURCE_ID = ManagedComputer.RESOURCE_ID
INNER JOIN ManagedResourceBSP ON ManagedComputer.RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
INNER JOIN BranchMemberResourceRel ON BranchMemberResourceRel.RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
INNER JOIN BranchOfficeDetails ON BranchOfficeDetails.BRANCH_OFFICE_ID = BranchMemberResourceRel.BRANCH_OFFICE_ID
INNER JOIN ResourceToBrowserRel ON ManagedResourceBSP.MANAGED_RESOURCE_ID = ResourceToBrowserRel.MANAGED_RESOURCE_ID
INNER JOIN InstalledBrowsers ON ResourceToBrowserRel.APPLICATION_ID = InstalledBrowsers.APPLICATION_ID
INNER JOIN BrowsersGroup ON InstalledBrowsers.BROWSER_GROUP_ID = BrowsersGroup.BROWSER_GROUP_ID
INNER JOIN Browsers ON BrowsersGroup.BROWSER_ID = Browsers.BROWSER_ID
LEFT JOIN (
SELECT Profile.PROFILE_NAME, RecentProfileForResource.RESOURCE_ID,
CASE
WHEN Profile.PROFILE_TYPE = 64001 THEN 1
WHEN Profile.PROFILE_TYPE = 64002 THEN 3
WHEN Profile.PROFILE_TYPE = 64003 THEN 5
WHEN Profile.PROFILE_TYPE = 64004 THEN 2
WHEN Profile.PROFILE_TYPE = 64008 THEN 31
WHEN Profile.PROFILE_TYPE = 64010 THEN 1024
END AS BROWSER_TYPE
FROM Profile
INNER JOIN RecentProfileForResource ON Profile.PROFILE_ID = RecentProfileForResource.PROFILE_ID
INNER JOIN ResourceToProfileHistory
ON RecentProfileForResource.PROFILE_ID = ResourceToProfileHistory.PROFILE_ID
AND RecentProfileForResource.COLLECTION_ID = ResourceToProfileHistory.COLLECTION_ID
AND RecentProfileForResource.RESOURCE_ID = ResourceToProfileHistory.RESOURCE_ID
WHERE Profile.PROFILE_TYPE IN (64001,64002,64003,64004,64008,64010) AND RecentProfileForResource.MARKED_FOR_DELETE = 0
) bcQuery ON Resource.RESOURCE_ID = bcQuery.RESOURCE_ID AND bcQuery.BROWSER_TYPE = CASE
WHEN Browsers.BROWSER_ID IN (20, 21, 22, 23, 24) THEN 1024
ELSE Browsers.BROWSER_ID
END
LEFT JOIN ResourceMACIP ON Resource.RESOURCE_ID = ResourceMACIP.RESOURCE_ID
WHERE ManagedComputer.MANAGED_STATUS = 61 AND ManagedResourceBSP.INSTALLATION_STATUS = 22 AND ManagedResourceBSP.MANAGED_STATUS = 61
ORDER BY Resource.NAME, Browsers.BROWSER_NAME, bcQuery.PROFILE_NAME
Browser Extension Report
SELECT Resource.NAME as "Host Name", BranchOfficeDetails.BRANCH_OFFICE_NAME as "Remote Office", COALESCE(ResourceMACIP.IP_ADDRESS, 'N/A') as "IP Address", Browsers.BROWSER_NAME as "Browser",
COALESCE(sq.NAME, 'N/A') as "Extension", COALESCE(AgentContact.ACTIVE_LOGGED_ON_USERS, 'N/A') as "Active Logged-on Users", COALESCE(AgentContact.ACTIVE_LOGGED_ON_USERS, 'N/A') as "Logged-on Users"
FROM Resource
INNER JOIN ManagedComputer ON Resource.RESOURCE_ID = ManagedComputer.RESOURCE_ID
INNER JOIN ManagedResourceBSP ON ManagedComputer.RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
INNER JOIN BranchMemberResourceRel ON BranchMemberResourceRel.RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
INNER JOIN BranchOfficeDetails ON BranchOfficeDetails.BRANCH_OFFICE_ID = BranchMemberResourceRel.BRANCH_OFFICE_ID
INNER JOIN ResourceToBrowserRel ON ResourceToBrowserRel.MANAGED_RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
INNER JOIN InstalledBrowsers ON InstalledBrowsers.APPLICATION_ID = ResourceToBrowserRel.APPLICATION_ID
INNER JOIN BrowsersGroup ON BrowsersGroup.BROWSER_GROUP_ID = InstalledBrowsers.BROWSER_GROUP_ID
INNER JOIN Browsers ON Browsers.BROWSER_ID = BrowsersGroup.BROWSER_ID
LEFT JOIN (
SELECT ExtensionDetails.EXTENSION_ID, ExtensionDetails.ID, ExtensionDetails.NAME, ExtensionGroups.BROWSER_TYPE, ManagedResourceBSP.MANAGED_RESOURCE_ID FROM
ManagedComputer
INNER JOIN ManagedResourceBSP ON ManagedComputer.RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
INNER JOIN ResourceToExtensions ON ResourceToExtensions.MANAGED_RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
INNER JOIN ExtensionDetails ON ExtensionDetails.EXTENSION_ID = ResourceToExtensions.EXTENSION_ID
INNER JOIN ExtensionGroups ON ExtensionDetails.EXTENSION_GROUP_ID = ExtensionGroups.EXTENSION_GROUP_ID
INNER JOIN Browsers ON ExtensionGroups.BROWSER_TYPE = Browsers.BROWSER_ID
INNER JOIN InstalledExtensionDetailsInResource ON InstalledExtensionDetailsInResource.RES_TO_EXTN_ID = ResourceToExtensions.RES_TO_EXTN_ID
WHERE ManagedResourceBSP.MANAGED_STATUS = 61 AND ManagedResourceBSP.INSTALLATION_STATUS = 22 AND ManagedComputer.MANAGED_STATUS = 61
) sq ON sq.BROWSER_TYPE = Browsers.BROWSER_ID AND sq.MANAGED_RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
LEFT JOIN AgentContact ON AgentContact.RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
LEFT JOIN ResourceMACIP ON Resource.RESOURCE_ID = ResourceMACIP.RESOURCE_ID
WHERE ManagedResourceBSP.MANAGED_STATUS = 61 AND ManagedResourceBSP.INSTALLATION_STATUS = 22 AND ManagedComputer.MANAGED_STATUS = 61
ORDER BY Resource.NAME, Browsers.BROWSER_ID, sq.NAME
Potentially Harmful Extension Report :
SELECT Resource.NAME as "Host Name", BranchOfficeDetails.BRANCH_OFFICE_NAME as "Remote Office", COALESCE(ResourceMACIP.IP_ADDRESS, 'N/A') as "IP Address", Browsers.BROWSER_NAME as "Browser",
COALESCE(sq.NAME, 'N/A') as "Harmful Extension", COALESCE(AgentContact.ACTIVE_LOGGED_ON_USERS, 'N/A') as "Active Logged-on Users", COALESCE(AgentContact.ACTIVE_LOGGED_ON_USERS, 'N/A') as "Logged-on Users"
FROM Resource
INNER JOIN ManagedComputer ON Resource.RESOURCE_ID = ManagedComputer.RESOURCE_ID
INNER JOIN ManagedResourceBSP ON ManagedComputer.RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
INNER JOIN BranchMemberResourceRel ON BranchMemberResourceRel.RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
INNER JOIN BranchOfficeDetails ON BranchOfficeDetails.BRANCH_OFFICE_ID = BranchMemberResourceRel.BRANCH_OFFICE_ID
INNER JOIN ResourceToBrowserRel ON ResourceToBrowserRel.MANAGED_RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
INNER JOIN InstalledBrowsers ON InstalledBrowsers.APPLICATION_ID = ResourceToBrowserRel.APPLICATION_ID
INNER JOIN BrowsersGroup ON BrowsersGroup.BROWSER_GROUP_ID = InstalledBrowsers.BROWSER_GROUP_ID
INNER JOIN Browsers ON Browsers.BROWSER_ID = BrowsersGroup.BROWSER_ID
LEFT JOIN (
SELECT ExtensionDetails.EXTENSION_ID, ExtensionDetails.ID, ExtensionDetails.NAME, ExtensionGroups.BROWSER_TYPE, ManagedResourceBSP.MANAGED_RESOURCE_ID FROM
ManagedComputer
INNER JOIN ManagedResourceBSP ON ManagedComputer.RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
INNER JOIN ResourceToExtensions ON ResourceToExtensions.MANAGED_RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
INNER JOIN ExtensionDetails ON ExtensionDetails.EXTENSION_ID = ResourceToExtensions.EXTENSION_ID
INNER JOIN ExtensionGroups ON ExtensionDetails.EXTENSION_GROUP_ID = ExtensionGroups.EXTENSION_GROUP_ID
INNER JOIN Browsers ON ExtensionGroups.BROWSER_TYPE = Browsers.BROWSER_ID
INNER JOIN InstalledExtensionDetailsInResource ON InstalledExtensionDetailsInResource.RES_TO_EXTN_ID = ResourceToExtensions.RES_TO_EXTN_ID
INNER JOIN InstalledExtensionPermissionsInResource ON InstalledExtensionDetailsInResource.RES_TO_EXTN_ID=InstalledExtensionPermissionsInResource.RES_TO_EXTN_ID
WHERE InstalledExtensionPermissionsInResource.EXTN_PERMISSIONS IN (28,55,36) AND InstalledExtensionDetailsInResource.EXTN_INSTALL_TYPE != 'admin'
AND InstalledExtensionDetailsInResource.EXTN_INSTALL_TYPE != 'Whitelist' AND ManagedResourceBSP.MANAGED_STATUS = 61
AND ManagedResourceBSP.INSTALLATION_STATUS = 22 AND ManagedComputer.MANAGED_STATUS = 61
) sq ON sq.BROWSER_TYPE = Browsers.BROWSER_ID AND sq.MANAGED_RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
LEFT JOIN AgentContact ON AgentContact.RESOURCE_ID = ManagedResourceBSP.MANAGED_RESOURCE_ID
LEFT JOIN ResourceMACIP ON Resource.RESOURCE_ID = ResourceMACIP.RESOURCE_ID
WHERE ManagedResourceBSP.MANAGED_STATUS = 61 AND ManagedResourceBSP.INSTALLATION_STATUS = 22 AND ManagedComputer.MANAGED_STATUS = 61
ORDER BY Resource.NAME, Browsers.BROWSER_ID, sq.NAME
New to ADSelfService Plus?
Related Articles
Query to Fetch Automatic Patch Deployment Data from Endpoint Central (Local DB Import)
Requirement: The requirement is to fetch Automatic Patch Deployment data from Endpoint Central into Analytics Plus using a custom query via local database import, enabling detailed reporting and analysis on patch automation. Solution: To achieve ...Procedure to upload local DB to Analytics Plus
Steps to use Upload Tool for uploading Data from any DB: 1. Download and extract the uploadtool.zip from this link 2. Configure the Database connection details: Open the file 'database_connection_params.conf' located under <Uploadtool>\conf\ folder, ...Query to Fetch Warranty Details from Endpoint Central
Requirement: The requirement is to fetch warranty expiry details of managed machines from Endpoint Central into Analytics Plus using a custom query through local database import. This enables users to track warranty coverage, identify machines ...Change module data : Import into Analytics Plus
Change module data : Import into Analytics Plus Note: When Analytics Plus officially supports Change module integration Out of the box, then these imported tables and reports might get overwritten. Please ensure to name the table as 'Change_localDB'. ...Query to Fetch Inactive Columns from a Workspace
Requirement: Customer wanted to determine the number of used or unused columns in a workspace. Solution: To achieve this, we need to connect to the Analytics Plus database by using local database import method (PostgrSQL) and then use the custom ...