‎09-19-2014 07:11 PM
Solved! Go to Solution.
‎09-19-2014 08:13 PM
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 AS icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname,
CASE
WHEN tblComputersystem.SystemType LIKE 'X86%' THEN '32-bit'
WHEN tblComputersystem.SystemType LIKE 'x64%' THEN '64-bit'
ELSE ''
END AS Bitness,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode
INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
WHERE
tblAssetCustom.State = 1
AND tblAssetCustom.Manufacturer LIKE 'Dell%'
‎09-20-2014 12:43 AM
SELECT Distinct
softwarename
FROM
tblSoftwareUni
WHERE
SoftwarePublisher LIKE 'Microsoft%'
AND softwareName LIKE 'Microsoft Office%'
AND softwareName NOT LIKE '%Viewer%'
AND softwareName NOT LIKE '%Visio %'
AND softwareName NOT LIKE '%Project%'
AND softwareName NOT LIKE '%web comp%'
AND softwareName NOT LIKE '%connector%'
AND softwareName NOT LIKE '%click-to-run%'
AND softwareName NOT LIKE '%validat%'
AND softwareName NOT LIKE '%live meet%'
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 AS icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname,
CASE
WHEN tblComputersystem.SystemType Like 'X86%' THEN '32-bit'
WHEN tblComputersystem.SystemType Like 'x64%' THEN '64-bit'
ELSE ''
END AS Bitness,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
msoffice.SoftwarePublisher,
msoffice.softwareName,
msoffice.softwareVersion
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode
INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
LEFT JOIN ( SELECT
tblSoftware.AssetID,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.SoftwareName,
tblSoftware.SoftwareVersion
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
WHERE
tblSoftwareUni.SoftwarePublisher LIKE 'Microsoft Corp%'
AND tblSoftwareUni.softwareName LIKE 'Microsoft Office%'
AND tblSoftwareUni.softwareName NOT LIKE '%Viewer%'
AND tblSoftwareUni.softwareName NOT LIKE '%Visio %'
AND tblSoftwareUni.softwareName NOT LIKE '%Project%'
AND tblSoftwareUni.softwareName NOT LIKE '%web comp%'
AND tblSoftwareUni.softwareName NOT LIKE '%connector%'
AND tblSoftwareUni.softwareName NOT LIKE '%click-to-run%'
AND tblSoftwareUni.softwareName NOT LIKE '%validat%'
AND tblSoftwareUni.softwareName NOT LIKE '%live meet%'
) AS msoffice ON msoffice.AssetID = tblAssets.AssetID
WHERE
tblAssetCustom.State = 1
AND tblAssetCustom.Manufacturer LIKE 'Dell%'
‎09-19-2014 11:38 PM
‎09-19-2014 08:13 PM
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 AS icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname,
CASE
WHEN tblComputersystem.SystemType LIKE 'X86%' THEN '32-bit'
WHEN tblComputersystem.SystemType LIKE 'x64%' THEN '64-bit'
ELSE ''
END AS Bitness,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode
INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
WHERE
tblAssetCustom.State = 1
AND tblAssetCustom.Manufacturer LIKE 'Dell%'
‎09-19-2014 09:58 PM
RC62N wrote:
Using the default base query as a starting point:SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 AS icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname,
CASE
WHEN tblComputersystem.SystemType LIKE 'X86%' THEN '32-bit'
WHEN tblComputersystem.SystemType LIKE 'x64%' THEN '64-bit'
ELSE ''
END AS Bitness,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode
INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
WHERE
tblAssetCustom.State = 1
AND tblAssetCustom.Manufacturer LIKE 'Dell%'
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now