Labtech - Offline Servers Dataset

Follow

Hi Lachie,

Have a look at this below. It's a clone of the default dataset with two added flags for active maintenance mode and future maintenance mode.

SELECT
c.computerid as 'id',
c.name as 'Machine Name',
clients.name as 'Client Name',
l.name as 'Location Name',
IF((locate('server',lower(c.os)) = 0), 'Workstation', 'Server') AS 'Machine Type',
DATE_ADD(cast(c.Assetdate as datetime), INTERVAL TIMESTAMPDIFF(HOUR,CURRENT_TIMESTAMP, UTC_TIMESTAMP) HOUR) 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(CURDATE(),DATE_FORMAT(c.Assetdate, '%Y-%m-%d'))/365, 2) AS 'Age in Years',
DATE_ADD(cast(c.lastcontact as datetime), INTERVAL TIMESTAMPDIFF(HOUR,CURRENT_TIMESTAMP, UTC_TIMESTAMP) HOUR) 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',
DATE_ADD(cast(c.lastcontact as datetime), INTERVAL TIMESTAMPDIFF(HOUR,CURRENT_TIMESTAMP, UTC_TIMESTAMP) HOUR) AS 'Last Contact',

CAST(c.uptime / 60.0 AS DECIMAL(10,2)) AS 'uptime_minutes',
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',
c.warrantyend AS 'warranty_end',
if ( act_mm.computerid is not null,True,False) as active_maintenance_mode,
if ( fut_mm.computerid is not null,True,False) as future_maintenance_mode
FROM
drives d
INNER JOIN computers c ON d.computerid = c.computerid
INNER JOIN clients ON c.clientid = clients.clientid
LEFT JOIN virusscanners vs ON c.virusscanner = vs.vscanid
Left Join locations l on l.locationid = c.locationid
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
Approved = 1
AND Installed = 0
GROUP BY
ComputerID
) pm ON c.computerid = pm.computerid
LEFT JOIN (
select computerid
from maintenancemode mm
where (date_add(mm.timestart, interval mm.durration MINUTE) >= current_timestamp) and (current_timestamp >= mm.timestart)
) as act_mm on act_mm.computerid = c.computerid
LEFT JOIN (
select computerid
from maintenancemode mm
where mm.timestart > current_timestamp
) as fut_mm on fut_mm.computerid = c.computerid
WHERE
d.size > 0
GROUP BY
d.computerid

Hey Bud,

i get this error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS 'Asset Date', CAST(FORMAT(ROUND(c.TotalMemory/512)/2,1) AS DECIMAL(10,1)) ' at line 7

Has anyone had any luck getting this working?

This works for us
SELECT
c.computerid AS 'id',
c.name AS 'Machine Name',
clients.name AS 'Client Name',
l.name AS 'Location Name',
IF((LOCATE('server',LOWER(c.os)) = 0), 'Workstation', 'Server') AS 'Machine Type',
DATE_ADD(CAST(c.Assetdate AS DATETIME), INTERVAL TIMESTAMPDIFF(HOUR,CURRENT_TIMESTAMP, UTC_TIMESTAMP) HOUR) 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(CURDATE(),DATE_FORMAT(c.Assetdate, '%Y-%m-%d'))/365, 2) AS 'Age in Years',
DATE_ADD(CAST(c.lastcontact AS DATETIME), INTERVAL TIMESTAMPDIFF(HOUR,CURRENT_TIMESTAMP, UTC_TIMESTAMP) HOUR) 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',
DATE_ADD(CAST(c.lastcontact AS DATETIME), INTERVAL TIMESTAMPDIFF(HOUR,CURRENT_TIMESTAMP, UTC_TIMESTAMP) HOUR) AS 'Last Contact',

CAST(c.uptime / 60.0 AS DECIMAL(10,2)) AS 'uptime_minutes',
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',
c.warrantyend AS 'warranty_end',
mm.mode AS 'maintenance mode'
FROM
drives d
INNER JOIN computers c ON d.computerid = c.computerid
INNER JOIN clients ON c.clientid = clients.clientid
LEFT JOIN virusscanners vs ON c.virusscanner = vs.vscanid
LEFT JOIN maintenancemode mm ON c.computerid = mm.computerid
LEFT JOIN locations l ON l.locationid = c.locationid
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
Approved = 1
AND Installed = 0
GROUP BY
ComputerID
) pm ON c.computerid = pm.computerid
WHERE
d.size > 0
GROUP BY
d.computerid

Hi there, we built up this stand alone query after the introduction of LabTech 'heartbeat' so we only show machines with BOTH normal check in and heartbeat not updating. It also ignores machines in maintenance mode:

SELECT cl.Name AS 'Client Name', c.Name AS 'Computer Name', c.ComputerID,
DATE_ADD(CAST(c.lastcontact AS DATETIME), INTERVAL TIMESTAMPDIFF(HOUR,CURRENT_TIMESTAMP, UTC_TIMESTAMP) HOUR) AS 'lastcontact',
DATE_ADD(CAST(hc.lastheartbeattime AS DATETIME), INTERVAL TIMESTAMPDIFF(HOUR,CURRENT_TIMESTAMP, UTC_TIMESTAMP) HOUR) AS 'lastheartbeat',
c.LocalAddress AS 'Local IP', c.RouterAddress AS 'Router IP', c.computerId AS 'id'
FROM computers c
JOIN clients cl ON c.clientid = cl.clientid
JOIN heartbeatcomputers hc ON c.computerid = hc.computerid
WHERE c.os LIKE '%Server%' AND c.lastcontact < DATE_ADD(NOW(),INTERVAL -300 SECOND) AND c.computerid NOT IN (SELECT computerid FROM maintenancemode) AND hc.lastheartbeattime < DATE_ADD(NOW(),INTERVAL -300 SECOND)