This is a re-written report of my previous zoom report.
It now properly shows Out of Date and Up to date for versions.
The previous report I made had an issue when checking the versions due to the way SQL works.
Here is the updated report:
;WITH ZoomInventory AS (
/* Windows */
SELECT
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename AS AssetType,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 AS icon,
tblAssets.IPAddress,
tblSoftwareUni.softwareName AS Software,
tblSoftware.softwareVersion AS Version,
tblSoftwareUni.SoftwarePublisher AS Publisher,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname AS OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftware.Lastchanged AS LastChanged
FROM tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tsysIPLocations ON tsysIPLocations.LocationID = tblAssets.LocationID
INNER JOIN tblState ON tblState.State = tblAssetCustom.State
INNER JOIN tblSoftware ON tblAssets.AssetID = tblSoftware.AssetID
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
LEFT JOIN tsysOS ON tsysOS.OScode = tblAssets.OScode
WHERE tblState.Statename = 'Active'
AND (
tblSoftwareUni.softwareName LIKE 'Zoom(64bit)'
OR tblSoftwareUni.softwareName LIKE 'Zoom Workplace (64-bit)'
OR tblSoftwareUni.softwareName LIKE 'Zoom (64-bit)'
)
UNION ALL
/* macOS */
SELECT
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename AS AssetType,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 AS icon,
tblAssets.IPAddress,
tblSoftwareUni.softwareName AS Software,
tblMacApplications.Version AS Version,
tblSoftwareUni.SoftwarePublisher AS Publisher,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblMacOSInfo.SystemVersion AS OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblMacApplications.LastChanged AS LastChanged
FROM tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tsysIPLocations ON tsysIPLocations.LocationID = tblAssets.LocationID
INNER JOIN tblState ON tblState.State = tblAssetCustom.State
INNER JOIN tblMacApplications ON tblAssets.AssetID = tblMacApplications.AssetID
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblMacApplications.softid
INNER JOIN tblMacOSInfo ON tblMacOSInfo.AssetID = tblAssets.AssetID
WHERE tblState.Statename = 'Active'
AND (
tblSoftwareUni.softwareName LIKE 'Zoom(64bit)'
OR tblSoftwareUni.softwareName LIKE 'Zoom (64-bit)'
)
UNION ALL
/* Linux */
SELECT
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename AS AssetType,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 AS icon,
tblAssets.IPAddress,
tblSoftwareUni.softwareName AS Software,
tblLinuxSoftware.Version AS Version,
tblSoftwareUni.SoftwarePublisher AS Publisher,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblLinuxSystem.OSRelease AS OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblLinuxSoftware.LastChanged AS LastChanged
FROM tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tsysIPLocations ON tsysIPLocations.LocationID = tblAssets.LocationID
INNER JOIN tblState ON tblState.State = tblAssetCustom.State
INNER JOIN tblLinuxSoftware ON tblAssets.AssetID = tblLinuxSoftware.AssetID
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblLinuxSoftware.SoftwareUniID
INNER JOIN tblLinuxSystem ON tblAssets.AssetID = tblLinuxSystem.AssetID
WHERE tblState.Statename = 'Active'
AND (
tblSoftwareUni.softwareName LIKE '%Zoom(64bit)%'
OR tblSoftwareUni.softwareName LIKE 'Zoom (64-bit)'
)
),
Parsed AS (
SELECT
zi.*,
/* Baseline (current) */
BaseMajor = 6,
BaseMinor = 7,
BaseBuild = 24657,
/* Defensive: trim at first space (handles "6.7.24657 (x64)") */
CleanVersion = LEFT(COALESCE(zi.Version,''), CHARINDEX(' ', COALESCE(zi.Version,'') + ' ') - 1),
DotCount = LEN(LEFT(COALESCE(zi.Version,''), CHARINDEX(' ', COALESCE(zi.Version,'') + ' ') - 1))
- LEN(REPLACE(LEFT(COALESCE(zi.Version,''), CHARINDEX(' ', COALESCE(zi.Version,'') + ' ') - 1), '.', ''))
FROM ZoomInventory zi
),
Parts AS (
SELECT
p.*,
/* If 4-part version: major.minor.build.revision (we ignore revision) */
Major = TRY_CONVERT(int, CASE WHEN p.DotCount >= 3 THEN PARSENAME(p.CleanVersion, 4) ELSE PARSENAME(p.CleanVersion, 3) END),
Minor = TRY_CONVERT(int, CASE WHEN p.DotCount >= 3 THEN PARSENAME(p.CleanVersion, 3) ELSE PARSENAME(p.CleanVersion, 2) END),
Build = TRY_CONVERT(int, CASE WHEN p.DotCount >= 3 THEN PARSENAME(p.CleanVersion, 2) ELSE PARSENAME(p.CleanVersion, 1) END)
FROM Parsed p
)
SELECT TOP 1000000
AssetID,
AssetName,
Domain,
AssetType,
Username,
Userdomain,
icon,
IPAddress,
Software,
/* Show version on report */
Version,
Publisher,
CASE
WHEN Major IS NULL OR Minor IS NULL OR Build IS NULL THEN 'Unknown'
WHEN Major > BaseMajor THEN 'Up to date'
WHEN Major = BaseMajor AND Minor > BaseMinor THEN 'Up to date'
WHEN Major = BaseMajor AND Minor = BaseMinor AND Build >= BaseBuild THEN 'Up to date'
ELSE 'Out of date'
END AS [Patch Status],
IPLocation,
Manufacturer,
Model,
OS,
SP,
Lastseen,
Lasttried,
LastChanged,
CASE
WHEN Major IS NULL OR Minor IS NULL OR Build IS NULL THEN '#fff2a8'
WHEN Major > BaseMajor THEN '#d4f4be'
WHEN Major = BaseMajor AND Minor > BaseMinor THEN '#d4f4be'
WHEN Major = BaseMajor AND Minor = BaseMinor AND Build >= BaseBuild THEN '#d4f4be'
ELSE '#ffadad'
END AS backgroundcolor
FROM Parts
ORDER BY Domain, AssetName, Software;
When versions increase and you want to correct the out of date vs up to date, modify these lines:
/* Baseline (current) */
BaseMajor = 6,
BaseMinor = 7,
BaseBuild = 24657,You can view your current version installed in Zoom -> Top right corner menu drop down -> Help -> About Zoom Workplace
To show only Out of Date devices
Set the Criteria for "Patch Status" to = 'out of date'
@duster1 Thank you for posting this. Love it when I see other users on this forum posting to help out the community.
When I ran the report it was only returning 17 devices. I looked under the "Software" inventory for just the word "Zoom" and it returned 137 devices with these software names. (Screenshot Below). I'm sure the installers are different. Like regular app install vs the enterprise installer and x86 vs 64 bit. I'm currently trying to figure out why we have so many different installs. I just added these to the report to get it to show all...
Where (tblSoftwareUni.softwareName Like 'Zoom' Or
tblSoftwareUni.softwareName Like 'Zoom Workplace (64-bit)' Or
tblSoftwareUni.softwareName Like 'Zoom Workplace' Or
tblSoftwareUni.softwareName Like 'Zoom (64-bit)') And
tblState.Statename = 'Active'
