Warranty Status Field ANSWER

Follow

someone requested a query to put Warranty Master into Labtech (Automate) datasets.  If you are ON-Premise for this, I have modified the original Machine Statistics Dataset with the following, which pulls the information from the Warranty Master plugin.

SELECT
c.computerid AS 'id'
,c.computerid
,c.name AS 'Machine Name'
,clients.name AS 'Client Name'
,vc.location_name AS 'Location Name'
,IF((LOCATE('server',LOWER(c.os)) = 0), 'Workstation', 'Server') AS 'Machine Type'
,CAST(c.Assetdate AS DATETIME)  AS 'Asset Date'
,CAST(FORMAT(ROUND(c.TotalMemory/512)/2,1) AS DECIMAL(10,1)) AS 'Total Memory'
,p.Name AS 'CPU'
,d.Letter AS 'Main Drive'
,d.Free AS 'Main Drive Size Free'
,d.Size AS 'Main Drive Size Total'
,c.BiosVer AS 'Serial Number'
,ROUND(DATEDIFF(CURRENT_TIMESTAMP,CAST(wm.purchasedate AS DATETIME))/365, 2) AS 'Age in Years'
,CAST(c.lastcontact AS DATETIME) AS 'Last Checked In'
,c.LastUsername AS 'Last User'
,c.BiosName AS 'Model'
,c.os AS 'OS Version'
,c.BiosMFG AS 'Manufacturer'
,IF(c.lastcontact > (NOW() - INTERVAL 15 MINUTE), 1, 0) AS 'Online'
,CASE
    WHEN COALESCE(pm.patches_missing, 0) > 4 THEN '>5 Missing'
    WHEN COALESCE(pm.patches_missing, 0) > 2 THEN '3-4 Missing'
    WHEN COALESCE(pm.patches_missing, 0) > 0 THEN '1-2 Missing'
    ELSE 'Full'
END AS 'Patch Status'
,CAST(c.lastcontact AS DATETIME) AS 'Last Contact'
,CAST(vc.Uptime AS DECIMAL(18,2)) AS 'Uptime_Minutes'
,CAST(vc.Uptime / 60.00 AS DECIMAL(18,2)) AS 'Uptime_Hours'
,CAST(COALESCE(pm.patches_missing, 0) AS DECIMAL) AS '# of Patches Missing'
,CASE
    WHEN (c.virusap = 1 AND DATEDIFF(NOW(),c.virusdefs) < 14) THEN 'Up to Date'
    WHEN (DATEDIFF(NOW(),c.virusdefs) < 14) THEN 'Installed'
    WHEN (DATEDIFF(NOW(),c.virusdefs) >= 14) THEN 'Out of Date'
    ELSE 'Not Enabled'
END AS 'AV Status'
,IF(c.virusap,'True','False') AS 'Protection Enabled'
,CAST(c.virusdefs AS DATETIME) AS 'AV Last Update Time'
,vs.name AS 'AV Version'
,CAST(wm.expirydate AS DATETIME) AS 'warranty_end'
#,(SELECT left(MajorVersion,2) FROM labtech.config ) AS LT_version
FROM drives d
INNER JOIN computers c ON d.computerid = c.computerid
INNER JOIN v_computers vc on vc.computerid = c.computerid
INNER JOIN clients ON c.clientid = clients.clientid
LEFT JOIN virusscanners vs ON c.virusscanner = vs.vscanid
LEFT JOIN v_processors p ON p.computerid = c.computerid
LEFT JOIN v_extradatacomputers ex ON ex.computerid = c.computerid
LEFT JOIN (
    SELECT
        ComputerID,
        COUNT(*) AS patches_missing
    FROM
        v_hotfixes
    WHERE
        CASE
          WHEN (SELECT left(MajorVersion,2) FROM labtech.config) <= 10 THEN Approved = 1
          WHEN (SELECT left(MajorVersion,2) FROM labtech.config ) >= 11 THEN Approved = 2
        END
        AND Installed = 0
    GROUP BY ComputerID
    ) pm ON c.computerid = pm.computerid 
LEFT JOIN plugin_warrantymaster_aux wm on c.Computerid = wm.computerID
WHERE d.size > 0
GROUP BY d.computerid