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, ...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 ...Problem Module Data import
Importing Problem data into Analytics Plus: You could log into the Analytics Plus 'ME ServiceDesk Plus Analytics' database and start creating these two tables using the below queries. Check this link for steps to import data from Local DB. Problem ...