‎11-12-2019 08:37 AM
‎11-15-2019 09:58 PM
SELECT
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.softwareName
FROM
tblSoftwareUni
WHERE
tblSoftwareUni.softwareName LIKE '%Office%'
ORDER BY
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.softwareName
WHERE
-- no idea why, but there's at least one entry with no publisher recorded
(tblSoftwareUni.SoftwarePublisher = '' AND tblSoftwareUni.softwareName LIKE 'Microsoft Office Professional%')
-- I see a "Corel Home Office" and a "WordPerfect Office" version_number
OR (tblSoftwareUni.SoftwarePublisher = 'Corel' AND tblSoftwareUni.softwareName LIKE '%Office%')
OR (tblSoftwareUni.softwareName LIKE 'LibreOffice%')
OR (tblSoftwareUni.softwareName LIKE 'OpenOffice.org%')
-- deal with the mess of not-necessarily-Office titles that contain "Microsoft Office" in the name
OR (tblSoftwareUni.SoftwarePublisher LIKE 'Microsoft Corp%'
AND ( tblSoftwareUni.softwareName = 'Microsoft Office'
OR tblSoftwareUni.softwareName LIKE 'Microsoft Office%Pro%'
OR tblSoftwareUni.softwareName = 'Microsoft Office 2010'
OR tblSoftwareUni.softwareName LIKE 'Microsoft Office Starter 20%'
OR tblSoftwareUni.softwareName LIKE 'Microsoft Office Ultimate 20%'
)
-- hopefully redundant, but in case something sneaks through the positive matches above
AND tblSoftwareUni.softwareName NOT LIKE '%Viewer%'
AND tblSoftwareUni.softwareName NOT LIKE '%web components%'
AND tblSoftwareUni.softwareName NOT LIKE '%interop assemblies%'
AND tblSoftwareUni.softwareName NOT LIKE '%activation assistant%'
AND tblSoftwareUni.softwareName NOT LIKE '%compatibility%'
AND tblSoftwareUni.softwareName NOT LIKE '%database engine%'
AND tblSoftwareUni.softwareName NOT LIKE '%runtime%'
AND tblSoftwareUni.softwareName NOT LIKE '%add-in%'
AND tblSoftwareUni.softwareName NOT LIKE '%tools%'
AND tblSoftwareUni.softwareName NOT LIKE '%click-to-run%'
AND tblSoftwareUni.softwareName NOT LIKE '%validation%'
AND tblSoftwareUni.softwareName NOT LIKE '%language%'
AND tblSoftwareUni.softwareName NOT LIKE '%interface pack%'
AND tblSoftwareUni.softwareName NOT LIKE '%live meeting%'
AND tblSoftwareUni.softwareName NOT LIKE '%OneNote%'
AND tblSoftwareUni.softwareName NOT LIKE '%Outlook%'
AND tblSoftwareUni.softwareName NOT LIKE '%Project%'
AND tblSoftwareUni.softwareName NOT LIKE '%Visio %'
)
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.softwareName
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
tblAssetCustom.State = 1
AND (-- no idea why, but there's at least one entry with no publisher recorded
(tblSoftwareUni.SoftwarePublisher = '' AND tblSoftwareUni.softwareName LIKE 'Microsoft Office Professional%')
-- I see a "Corel Home Office" and a "WordPerfect Office" version_number
OR (tblSoftwareUni.SoftwarePublisher = 'Corel' AND tblSoftwareUni.softwareName LIKE '%Office%')
OR (tblSoftwareUni.softwareName LIKE 'LibreOffice%')
OR (tblSoftwareUni.softwareName LIKE 'OpenOffice.org%')
-- deal with the mess of not-necessarily-Office titles that contain "Microsoft Office" in the name
OR (tblSoftwareUni.SoftwarePublisher LIKE 'Microsoft Corp%'
AND ( tblSoftwareUni.softwareName = 'Microsoft Office'
OR tblSoftwareUni.softwareName LIKE 'Microsoft Office%Pro%'
OR tblSoftwareUni.softwareName = 'Microsoft Office 2010'
OR tblSoftwareUni.softwareName LIKE 'Microsoft Office Starter 20%'
OR tblSoftwareUni.softwareName LIKE 'Microsoft Office Ultimate 20%'
)
-- hopefully redundant, but in case something sneaks through the positive matches above
AND tblSoftwareUni.softwareName NOT LIKE '%Viewer%'
AND tblSoftwareUni.softwareName NOT LIKE '%web components%'
AND tblSoftwareUni.softwareName NOT LIKE '%interop assemblies%'
AND tblSoftwareUni.softwareName NOT LIKE '%activation assistant%'
AND tblSoftwareUni.softwareName NOT LIKE '%compatibility%'
AND tblSoftwareUni.softwareName NOT LIKE '%database engine%'
AND tblSoftwareUni.softwareName NOT LIKE '%runtime%'
AND tblSoftwareUni.softwareName NOT LIKE '%add-in%'
AND tblSoftwareUni.softwareName NOT LIKE '%tools%'
AND tblSoftwareUni.softwareName NOT LIKE '%click-to-run%'
AND tblSoftwareUni.softwareName NOT LIKE '%validation%'
AND tblSoftwareUni.softwareName NOT LIKE '%language%'
AND tblSoftwareUni.softwareName NOT LIKE '%interface pack%'
AND tblSoftwareUni.softwareName NOT LIKE '%live meeting%'
AND tblSoftwareUni.softwareName NOT LIKE '%OneNote%'
AND tblSoftwareUni.softwareName NOT LIKE '%Outlook%'
AND tblSoftwareUni.softwareName NOT LIKE '%Project%'
AND tblSoftwareUni.softwareName NOT LIKE '%Visio %'
)
)
ORDER BY
tblAssets.AssetName,
tblSoftwareUni.softwareName
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now