‎11-15-2019 04:29 PM
‎11-18-2019 10:28 AM
‎11-15-2019 08:57 PM
‎11-15-2019 08:25 PM
SELECT Distinct
tblAssets.AssetName,
tsysOS.OSname,
CASE WHEN soft01.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [Software1],
CASE WHEN soft02.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [Software2],
CASE WHEN soft03.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS Chrome,
CASE WHEN soft04.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [C++ 2005],
CASE WHEN soft05.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [C++ 2008],
CASE WHEN soft06.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [C++ 2010],
CASE WHEN soft07.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [C++ 2012],
CASE WHEN soft08.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [C++ 2013],
CASE WHEN soft09.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [C++ 2017],
CASE WHEN soft10.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [Software 8],
CASE WHEN soft11.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [Software 9],
CASE WHEN soft12.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS [Office 2010 Pro],
CASE WHEN soft13.AssetID IS NULL THEN 'NO' ELSE 'YES' END AS Software10,
tblAssets.Firstseen,
tblAssets.Lastseen,
CASE
WHEN soft01.AssetID IS NULL
OR soft02.AssetID IS NULL
OR soft03.AssetID IS NULL
OR soft04.AssetID IS NULL
OR soft05.AssetID IS NULL
OR soft06.AssetID IS NULL
OR soft07.AssetID IS NULL
OR soft08.AssetID IS NULL
OR soft09.AssetID IS NULL
OR soft10.AssetID IS NULL
OR soft11.AssetID IS NULL
OR soft12.AssetID IS NULL
OR soft13.AssetID IS NULL
THEN '#ffcccc'
END As backgroundcolor
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tsysOS ON tsysOS.OScode = tblAssets.OScode
LEFT JOIN (SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftwareUni.softwareName LIKE '%Software1%'
) AS soft01 ON soft01.AssetID = tblAssets.AssetID
LEFT JOIN (SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftwareUni.softwareName LIKE '%Software2%'
) AS soft02 ON soft02.AssetID = tblAssets.AssetID
LEFT JOIN (SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftwareUni.softwareName LIKE '%Google Chrome%'
) AS soft03 ON soft03.AssetID = tblAssets.AssetID
LEFT JOIN (SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftwareUni.softwareName LIKE '%Microsoft Visual C++ 2005%'
) AS soft04 ON soft04.AssetID = tblAssets.AssetID
LEFT JOIN (SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftwareUni.softwareName LIKE '%Microsoft Visual C++ 2008%'
) AS soft05 ON soft05.AssetID = tblAssets.AssetID
LEFT JOIN (SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftwareUni.softwareName LIKE '%Microsoft Visual C++ 2010%'
) AS soft06 ON soft06.AssetID = tblAssets.AssetID
LEFT JOIN (SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftwareUni.softwareName LIKE '%Microsoft Visual C++ 2012%'
) AS soft07 ON soft07.AssetID = tblAssets.AssetID
LEFT JOIN (SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftwareUni.softwareName LIKE '%Microsoft Visual C++ 2013%'
) AS soft08 ON soft08.AssetID = tblAssets.AssetID
LEFT JOIN (SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftwareUni.softwareName LIKE '%Microsoft Visual C++ 2017%'
) AS soft09 ON soft09.AssetID = tblAssets.AssetID
LEFT JOIN (SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftwareUni.softwareName LIKE '%Software 8%'
) AS soft10 ON soft10.AssetID = tblAssets.AssetID
LEFT JOIN (SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftwareUni.softwareName LIKE '%Software 9%'
) AS soft11 ON soft11.AssetID = tblAssets.AssetID
LEFT JOIN (SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftwareUni.softwareName LIKE '%Microsoft Office Professional Plus 2010%'
) AS soft12 ON soft12.AssetID = tblAssets.AssetID
LEFT JOIN (SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftwareUni.softwareName LIKE '%Software10%'
) AS soft13 ON soft13.AssetID = tblAssets.AssetID
WHERE
tsysOS.OSname = 'Win 10'
AND tblAssetCustom.State = 1
ORDER BY
tblAssets.Firstseen DESC
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now