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 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 : SELECT aps.patch_id AS "Patch Id",
p.patchname AS "Patch Name",
pd.description AS "Patch Description",
c2.collection_name AS "Deployed using",
DATEADD(SECOND, pd.releasedtime / 1000, '1970-01-01') AS "Patch Released Date",
pmseverity.name AS "Severity",
csd.status_name AS "Patch Status",
CASE
WHEN p.Bulletinid IS NOT NULL THEN p.Bulletinid
ELSE 'Not Applicable'
END AS "Bulletin Id",
CASE
WHEN cps.PATCH_ID IS NULL THEN 'Not Approved'
ELSE 'Approved'
END AS "Patch Approval Status",
CASE
WHEN p.uninstall_status IS NULL THEN 'Not Applicable'
WHEN p.uninstall_status = 1 THEN 'Supported'
ELSE 'Unsupported'
END AS "Patch Uninstallation",
CASE
WHEN CAST(p.NOREBOOT AS VARCHAR) = CAST(0 AS VARCHAR) THEN 'May Require'
WHEN CAST(p.NOREBOOT AS VARCHAR) = CAST(1 AS VARCHAR) THEN 'Not Require'
WHEN CAST(p.NOREBOOT AS VARCHAR) = CAST(3 AS VARCHAR) THEN 'Auto Reboot'
ELSE '--'
END AS "Reboot",
CASE
WHEN v.name IS NOT NULL THEN v.name
ELSE 'Not Applicable'
END AS "Vendor",
CASE
WHEN MSPatch.sqnumber IS NOT NULL THEN MSPatch.sqnumber
ELSE 'Not Applicable'
END AS "KB Number",
CASE
WHEN c2.collection_id IS NULL THEN 'Configuration has been deleted by the user'
ELSE c2.collection_name
END AS "Deployed Using",
CASE
WHEN cd.config_id IN (454, 854, 154) THEN 'TestGroup task'
WHEN cd.config_id IN (802, 403, 162) THEN 'APD Task'
WHEN cd.config_id IN (800, 401, 151) THEN 'Manual Deployment task'
ELSE '--'
END AS "Deployment Type",
ud.updatename AS "Patch Type",
platform.platform_name AS "Platform",
CASE
WHEN downloadstatus.status_name IS NULL THEN '--'
ELSE downloadstatus.status_name
END AS "Download Status",
CASE
WHEN cps.PATCH_ID IS NULL THEN 'Not Applicable'
ELSE CONCAT(
approveduser.first_name,
approveduser.middle_name,
approveduser.last_name
)
END AS "Approved By",
CASE
WHEN ips.deploy_status_id = 209 THEN 'Installed'
WHEN ips.deploy_status_id = 206 THEN 'Failed'
WHEN ips.deploy_status_id = 207 THEN 'Reboot Pending'
ELSE '--'
END AS "Deployment Status",
ips.remarks AS "Remarks",
DATEADD(SECOND, ips.installed_time / 1000, '1970-01-01') AS "Deployed Date",
r.name AS "System Name",
r.domain_netbios_name AS "Domain Name",
bod.branch_office_name AS "Remote Office",
ComputerType.display_label AS "Computer Type",
c.os_name AS "OS Name",
c.service_pack AS "Service Pack",
DATEADD(SECOND, ac.last_contact_time / 1000, '1970-01-01') AS "Last Contact Time",
CASE
WHEN prhs.health_status = 3 THEN 'Highly Vulnerable'
WHEN prhs.health_status = 2 THEN 'Vulnerable'
WHEN prhs.health_status = 1 THEN 'Healthy'
ELSE 'Health Not Available'
END AS "Health",
DATEADD(SECOND, rds.last_patched_time / 1000, '1970-01-01') AS "Last Patched Time",
ac.logged_on_users AS "Logged On Users",
DATEADD(SECOND, pcss.last_successful_scan / 1000, '1970-01-01') AS "Last Successful Scan",
CASE
WHEN (ppl.size / (1024 * 1024 * 1024)) > 1
THEN CONCAT(CAST(ppl.size / (1024 * 1024 * 1024) AS VARCHAR), ' GB')
WHEN (ppl.size / (1024 * 1024)) > 1
THEN CONCAT(CAST(ppl.size / (1024 * 1024) AS VARCHAR), ' MB')
WHEN (ppl.size / 1024) > 1
THEN CONCAT(CAST(ppl.size / 1024 AS VARCHAR), ' KB')
WHEN ppl.size > 1
THEN CONCAT(CAST(ppl.size AS VARCHAR), ' Bytes')
ELSE '0 KB'
END AS "Size"
FROM affectedpatchstatus aps
INNER JOIN managedcomputer mc
ON mc.resource_id = aps.resource_id
AND mc.managed_status = 61
INNER JOIN resource r ON r.resource_id = aps.resource_id
INNER JOIN configstatusdefn csd ON aps.status_id = csd.status_id
INNER JOIN computer c ON c.resource_id = aps.resource_id
INNER JOIN patch p ON p.patchid = aps.patch_id
INNER JOIN patchdetails pd ON pd.patchid = aps.patch_id
INNER JOIN pmseverity ON pmseverity.severityid = p.severityid
INNER JOIN agentcontact ac ON ac.resource_id = mc.resource_id
LEFT JOIN installpatchstatus ips
ON ips.patch_id = aps.patch_id
AND ips.resource_id = r.resource_id
LEFT JOIN collection c2 ON c2.collection_id = ips.collection_id
LEFT JOIN configstatusdefn deployment_status
ON deployment_status.status_id = ips.deploy_status_id
LEFT JOIN CUSTOMERPATCHSTATUS cps ON cps.patch_id = p.patchid
INNER JOIN Vendor v ON v.vendorid = p.vendorid
INNER JOIN MSPatch ON MSPatch.patchid = p.patchid
INNER JOIN pmpatchtype pmtype ON pmtype.patchid = p.patchid
INNER JOIN updatedefinition ud ON ud.updateid = pmtype.type
INNER JOIN platform ON platform.platform_id = pmtype.platform_id
LEFT JOIN PatchStoreLocation store ON store.patchid = p.patchid
LEFT JOIN ConfigStatusDefn downloadstatus ON store.status_id = downloadstatus.status_id
LEFT JOIN aaauser approveduser ON approveduser.user_id = cps.user_id
LEFT JOIN pmreshealthstatus prhs ON prhs.resource_id = r.resource_id
LEFT JOIN resourcedeploymentstatus rds ON rds.resource_id = r.resource_id
LEFT JOIN patchclientscanstatus pcss ON pcss.resource_id = r.resource_Id
INNER JOIN patchapplicabledetails pad
ON pad.patchid = aps.patch_id
AND pad.affectedstatus = 1
INNER JOIN branchmemberresourcerel bmrr ON bmrr.resource_id = r.resource_id
INNER JOIN InvComputer ON r.resource_id = InvComputer.COMPUTER_ID
INNER JOIN ComputerType ON InvComputer.COMPUTER_TYPE = ComputerType.COMPUTER_TYPE_ID
INNER JOIN branchofficedetails bod ON bod.branch_office_id = bmrr.branch_office_id
LEFT JOIN cfgdatatocollection cfg ON cfg.collection_id = c2.collection_id
LEFT JOIN configdata cd ON cfg.config_data_id = cd.config_data_id
INNER JOIN pmpatchlocation ppl
ON ppl.patchid = aps.patch_id
AND (ppl.languageid = 0 OR ppl.languageid = 1)
WHERE
p.ISSUPERCEDED = 0
AND pmtype.STATUS IN (0, 6);
New to ADSelfService Plus?
Related Articles
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: ...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'. ...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 ...TRUNCATEADD not deleting existing rows in DB import
Issue: when 0 records are imported with 'delete and add records' chosen, the old entries in Analytics table is not getting removed. (DB import) Builds identified: 4300, 4310, 4350 Description: The problem with the table not removing the old entries ...