cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Esben_D
Lansweeper Employee
Lansweeper Employee
Based on the following lists of EOS software, I've created a report which can be used to identify whether you've got software in your network which is close or is no longer supported. The EOS date is also shown after each software found. For more information, check out the blog post here.

Due to the size and coverage of this report, you will notice some performance impact on the report builder. This impact will only last while the report is being compiled or processed.

  • https://www.cisecurity.org/wp-content/uploads/2019/03/EOS-Table.pdf
  • https://support.microsoft.com/en-us/help/4470235/products-reaching-end-of-support-for-2020
SELECT tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
COALESCE(tsysOS.IMAGE,tsysAssetTypes.AssetTypeIcon10) AS icon,
tblAssets.IPAddress,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.softwareVersion AS Version,
CASE
WHEN tblSoftwareUni.softwareName LIKE 'Coldfusion'
AND tblSoftware.softwareVersion LIKE '11.%'
THEN cast('2019-04-30' as datetime)
WHEN tblSoftwareUni.softwareName LIKE 'Coldfusion'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-05-16' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Correspondence%Management%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Designer%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%signature%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Documentum%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Content%Manager%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%FileNet%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%SharePoint%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Forms%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Output%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Process%Management%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Reader%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Rights%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Workbench%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Apache%Struts%'
AND tblSoftware.softwareVersion LIKE '2.3.%'
THEN cast('2019-05-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion LIKE '4.13'
THEN cast('2019-01-20' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion LIKE '4.14'
THEN cast('2019-02-21' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion LIKE '5.0'
THEN cast('2019-05-02' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion LIKE '5.1'
THEN cast('2019-06-06' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Confluence%'
AND tblSoftware.softwareVersion LIKE '6.1'
THEN cast('2019-03-20' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Confluence%'
AND tblSoftware.softwareVersion LIKE '6.2'
THEN cast('2019-05-15' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Core%'
AND tblSoftware.softwareVersion LIKE '7.3'
THEN cast('2019-01-03' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Core%'
AND tblSoftware.softwareVersion LIKE '7.4'
THEN cast('2019-06-29' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion LIKE '3.3'
THEN cast('2019-01-03' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion LIKE '3.4'
THEN cast('2019-03-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion LIKE '3.5'
THEN cast('2019-04-27' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion LIKE '3.6'
THEN cast('2019-06-29' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Software%'
AND tblSoftware.softwareVersion LIKE '7.3'
THEN cast('2019-01-03' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Software%'
AND tblSoftware.softwareVersion LIKE '7.4'
THEN cast('2019-06-29' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Endpoint%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Checkpoint%'
AND (tblSoftware.softwareVersion LIKE '%80.61'
OR tblSoftware.softwareVersion LIKE '%80.63'
OR tblSoftware.softwareVersion LIKE '%80.65'
OR tblSoftware.softwareVersion LIKE '%80.66'
OR tblSoftware.softwareVersion LIKE '%80.67'
OR tblSoftware.softwareVersion LIKE '%80.68'
OR tblSoftware.softwareVersion LIKE '%80.69'
OR tblSoftware.softwareVersion LIKE '%5.6')
THEN cast('2018-11-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Endpoint%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Checkpoint%'
AND tblSoftware.softwareVersion LIKE '%5.6'
THEN cast('2019-06-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Server%'
AND tblSoftware.softwareVersion LIKE '2.8'
THEN cast('2019-00-20' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%'
AND tblSoftware.softwareVersion LIKE '32.12.%'
THEN cast('2019-01-11' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%'
AND tblSoftware.softwareVersion LIKE '32.%.%'
THEN cast('2019-05-24' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Websense%URL%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%'
AND tblSoftware.softwareVersion IN('7.0','7.1','7.5')
THEN cast('2019-04-02' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Websense%Express%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%'
AND tblSoftware.softwareVersion LIKE '1.0.0'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jboss%Web%'
AND tblSoftware.softwareVersion LIKE '1.%'
THEN cast('2020-04-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Mediawiki%'
AND tblSoftware.softwareVersion LIKE '1.27.%'
THEN cast('2019-06-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Exchange%2010%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion LIKE '7.0.7'
THEN cast('2019-02-25' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion LIKE '7.0.8'
THEN cast('2019-04-22' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion LIKE '7.0.9'
THEN cast('2019-06-19' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion LIKE '7.0.10'
THEN cast('2019-08-19' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '5.17.3'
THEN cast('2019-01-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '5.17.4'
THEN cast('2019-04-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '5.17.5'
THEN cast('2019-06-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Connect%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '3.5'
THEN cast('2019-05-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Enterprise%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '8.5.3'
THEN cast('2019-03-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Enterprise%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '8.5.4'
THEN cast('2019-06-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%IP360%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '8.1.1'
THEN cast('2019-05-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%log%center%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '7.2.4'
THEN cast('2019-03-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%log%center%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '7.2.5'
THEN cast('2019-06-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Intelligence%hub%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '2.7.3'
THEN cast('2019-03-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Winzip%'
AND tblSoftware.softwareVersion LIKE '20%'
THEN cast('2019-04-30' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Horizon%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%vmware%'
AND tblSoftware.softwareVersion LIKE '6.%'
THEN cast('2019-06-19' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Blend%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Design%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Encoder%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Studio%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Web%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Forefront%Unified%Access%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-04-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Forefront%Threat%management%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-04-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Visual%Studio%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-07-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Encoder%4%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-09-08' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Web%4%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-09-08' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Acess%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Dynamics%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Excel%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Studio%4%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Infopath%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Office%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Onenote%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Outlook%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%powerpoint%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%project%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%publisher%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%sharepoint%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%visio%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%visual%basic%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%word%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
END AS [EOS date],
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname AS OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftware.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
LEFT JOIN tsysOS ON tsysOS.OScode = tblAssets.OScode
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftwareUni.softwareName LIKE 'Coldfusion'
AND (tblSoftware.softwareVersion LIKE '11.%'
OR tblSoftware.softwareVersion LIKE '10.%')
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Correspondence%Management%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Designer%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%signature%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Documentum%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Content%Manager%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%FileNet%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%SharePoint%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Forms%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Output%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Process%Management%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Reader%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Rights%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Workbench%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%Apache%Struts%'
AND tblSoftware.softwareVersion LIKE '2.3.%'
OR tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion IN('4.13','4.14','5.0','5.1')
OR tblSoftwareUni.softwareName LIKE '%Confluence%'
AND tblSoftware.softwareVersion IN('6.1','6.2')
OR tblSoftwareUni.softwareName LIKE '%Jira%Core%'
AND tblSoftware.softwareVersion IN('7.3','7.4')
OR tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion IN('3.3','3.4','3.5','3.6')
OR tblSoftwareUni.softwareName LIKE '%Jira%Software%'
AND tblSoftware.softwareVersion IN('7.3','7.4')
OR tblSoftwareUni.softwareName LIKE '%Endpoint%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Checkpoint%'
AND (tblSoftware.softwareVersion LIKE '%80.61'
OR tblSoftware.softwareVersion LIKE '%80.63'
OR tblSoftware.softwareVersion LIKE '%80.65'
OR tblSoftware.softwareVersion LIKE '%80.66'
OR tblSoftware.softwareVersion LIKE '%80.67'
OR tblSoftware.softwareVersion LIKE '%80.68'
OR tblSoftware.softwareVersion LIKE '%80.69'
OR tblSoftware.softwareVersion LIKE '%5.6')
OR tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Server%'
AND tblSoftware.softwareVersion LIKE '2.8'
OR tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%'
AND (tblSoftware.softwareVersion LIKE '32.12.%'
OR tblSoftware.softwareVersion LIKE '32.%.%')
OR tblSoftwareUni.softwareName LIKE '%Websense%URL%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%'
AND tblSoftware.softwareVersion IN('7.0','7.1','7.5')
OR tblSoftwareUni.softwareName LIKE '%Websense%Express%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%'
AND tblSoftware.softwareVersion LIKE '1.0.0'
OR tblSoftwareUni.softwareName LIKE '%Jboss%Web%'
AND tblSoftware.softwareVersion LIKE '1.%'
OR tblSoftwareUni.softwareName LIKE '%Mediawiki%'
AND tblSoftware.softwareVersion LIKE '1.27.%'
OR tblSoftwareUni.softwareName LIKE '%Exchange%2010%'
OR tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion IN('7.0.7','7.0.8','7.0.9','7.0.10')
OR tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion IN('5.17.3','5.17.4','5.17.5')
OR tblSoftwareUni.softwareName LIKE '%Connect%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '3.5'
OR tblSoftwareUni.softwareName LIKE '%Enterprise%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion IN('8.5.3','8.5.4')
OR tblSoftwareUni.softwareName LIKE '%IP360%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '8.1.1'
OR tblSoftwareUni.softwareName LIKE '%log%center%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion IN('7.2.4','7.2.5')
OR tblSoftwareUni.softwareName LIKE '%Intelligence%hub%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '2.7.3'
OR tblSoftwareUni.softwareName LIKE '%Winzip%'
AND tblSoftware.softwareVersion LIKE '20%'
OR tblSoftwareUni.softwareName LIKE '%Horizon%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%vmware%'
AND tblSoftware.softwareVersion LIKE '6.%'
OR tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
AND (tblSoftwareUni.softwareName LIKE '%Expression%Blend%3%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Design%3%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Encoder%3%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Studio%3%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Web%3%'
OR tblSoftwareUni.softwareName LIKE '%Forefront%Unified%Access%2010%'
OR tblSoftwareUni.softwareName LIKE '%Forefront%Threat%management%2010%'
OR tblSoftwareUni.softwareName LIKE '%Visual%Studio%2010%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Encoder%4%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Web%4%'
OR tblSoftwareUni.softwareName LIKE '%Acess%2010%'
OR tblSoftwareUni.softwareName LIKE '%Dynamics%2010%'
OR tblSoftwareUni.softwareName LIKE '%Excel%2010%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Studio%4%'
OR tblSoftwareUni.softwareName LIKE '%Infopath%2010%'
OR tblSoftwareUni.softwareName LIKE '%Office%2010%'
OR tblSoftwareUni.softwareName LIKE '%Onenote%2010%'
OR tblSoftwareUni.softwareName LIKE '%Outlook%2010%'
OR tblSoftwareUni.softwareName LIKE '%powerpoint%2010%'
OR tblSoftwareUni.softwareName LIKE '%project%2010%'
OR tblSoftwareUni.softwareName LIKE '%publisher%2010%'
OR tblSoftwareUni.softwareName LIKE '%sharepoint%2010%'
OR tblSoftwareUni.softwareName LIKE '%visio%2010%'
OR tblSoftwareUni.softwareName LIKE '%visual%basic%2010%'
OR tblSoftwareUni.softwareName LIKE '%word%2010%')
AND tblState.Statename = 'Active'
ORDER BY tblAssets.Domain,
tblAssets.AssetName
16 REPLIES 16
PapaTuck
Engaged Sweeper III

Is there a current report that covers 2023 or later?

rasldasl
Engaged Sweeper III
I'm picking up a lot of Microsoft Visual Studio 2010 Tools for Office Runtime. Are runtimes like this (and Access runtime) really affected?
nnewton
Engaged Sweeper III
CONVERT should work in Compact shouldn't it?


SELECT tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
COALESCE(tsysOS.IMAGE,tsysAssetTypes.AssetTypeIcon10) AS icon,
tblAssets.IPAddress,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.softwareVersion AS Version,
CASE
WHEN tblSoftwareUni.softwareName LIKE 'Coldfusion'
AND tblSoftware.softwareVersion LIKE '11.%'
THEN CONVERT(DATE,'2019-04-30')
WHEN tblSoftwareUni.softwareName LIKE 'Coldfusion'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN CONVERT(DATE,'2019-05-16')
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Correspondence%Management%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN CONVERT(DATE,'2019-03-31')
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Designer%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN CONVERT(DATE,'2019-03-31')
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%signature%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN CONVERT(DATE,'2019-03-31')
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Documentum%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN CONVERT(DATE,'2019-03-31')
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Content%Manager%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN CONVERT(DATE,'2019-03-31')
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%FileNet%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN CONVERT(DATE,'2019-03-31')
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%SharePoint%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN CONVERT(DATE,'2019-03-31')
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Forms%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN CONVERT(DATE,'2019-03-31')
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Output%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN CONVERT(DATE,'2019-03-31')
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Process%Management%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN CONVERT(DATE,'2019-03-31')
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Reader%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN CONVERT(DATE,'2019-03-31')
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Rights%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN CONVERT(DATE,'2019-03-31')
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Workbench%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN CONVERT(DATE,'2019-03-31')
WHEN tblSoftwareUni.softwareName LIKE '%Apache%Struts%'
AND tblSoftware.softwareVersion LIKE '2.3.%'
THEN CONVERT(DATE,'2019-05-14')
WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion LIKE '4.13'
THEN CONVERT(DATE,'2019-01-20')
WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion LIKE '4.14'
THEN CONVERT(DATE,'2019-02-21')
WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion LIKE '5.0'
THEN CONVERT(DATE,'2019-05-02')
WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion LIKE '5.1'
THEN CONVERT(DATE,'2019-06-06')
WHEN tblSoftwareUni.softwareName LIKE '%Confluence%'
AND tblSoftware.softwareVersion LIKE '6.1'
THEN CONVERT(DATE,'2019-03-20')
WHEN tblSoftwareUni.softwareName LIKE '%Confluence%'
AND tblSoftware.softwareVersion LIKE '6.2'
THEN CONVERT(DATE,'2019-05-15')
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Core%'
AND tblSoftware.softwareVersion LIKE '7.3'
THEN CONVERT(DATE,'2019-01-03')
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Core%'
AND tblSoftware.softwareVersion LIKE '7.4'
THEN CONVERT(DATE,'2019-06-29')
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion LIKE '3.3'
THEN CONVERT(DATE,'2019-01-03')
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion LIKE '3.4'
THEN CONVERT(DATE,'2019-03-14')
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion LIKE '3.5'
THEN CONVERT(DATE,'2019-04-27')
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion LIKE '3.6'
THEN CONVERT(DATE,'2019-06-29')
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Software%'
AND tblSoftware.softwareVersion LIKE '7.3'
THEN CONVERT(DATE,'2019-01-03')
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Software%'
AND tblSoftware.softwareVersion LIKE '7.4'
THEN CONVERT(DATE,'2019-06-29')
WHEN tblSoftwareUni.softwareName LIKE '%Endpoint%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Checkpoint%'
AND (tblSoftware.softwareVersion LIKE '%80.61'
OR tblSoftware.softwareVersion LIKE '%80.63'
OR tblSoftware.softwareVersion LIKE '%80.65'
OR tblSoftware.softwareVersion LIKE '%80.66'
OR tblSoftware.softwareVersion LIKE '%80.67'
OR tblSoftware.softwareVersion LIKE '%80.68'
OR tblSoftware.softwareVersion LIKE '%80.69'
OR tblSoftware.softwareVersion LIKE '%5.6')
THEN CONVERT(DATE,'2018-11-01')
WHEN tblSoftwareUni.softwareName LIKE '%Endpoint%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Checkpoint%'
AND tblSoftware.softwareVersion LIKE '%5.6'
THEN CONVERT(DATE,'2019-06-01')
WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Server%'
AND tblSoftware.softwareVersion LIKE '2.8'
THEN CONVERT(DATE,'2019-00-20')
WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%'
AND tblSoftware.softwareVersion LIKE '32.12.%'
THEN CONVERT(DATE,'2019-01-11')
WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%'
AND tblSoftware.softwareVersion LIKE '32.%.%'
THEN CONVERT(DATE,'2019-05-24')
WHEN tblSoftwareUni.softwareName LIKE '%Websense%URL%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%'
AND tblSoftware.softwareVersion IN('7.0','7.1','7.5')
THEN CONVERT(DATE,'2019-04-02')
WHEN tblSoftwareUni.softwareName LIKE '%Websense%Express%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%'
AND tblSoftware.softwareVersion LIKE '1.0.0'
THEN CONVERT(DATE,'2019-03-31')
WHEN tblSoftwareUni.softwareName LIKE '%Jboss%Web%'
AND tblSoftware.softwareVersion LIKE '1.%'
THEN CONVERT(DATE,'2020-04-01')
WHEN tblSoftwareUni.softwareName LIKE '%Mediawiki%'
AND tblSoftware.softwareVersion LIKE '1.27.%'
THEN CONVERT(DATE,'2019-06-01')
WHEN tblSoftwareUni.softwareName LIKE '%Exchange%2010%'
THEN CONVERT(DATE,'2020-01-14')
WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion LIKE '7.0.7'
THEN CONVERT(DATE,'2019-02-25')
WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion LIKE '7.0.8'
THEN CONVERT(DATE,'2019-04-22')
WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion LIKE '7.0.9'
THEN CONVERT(DATE,'2019-06-19')
WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion LIKE '7.0.10'
THEN CONVERT(DATE,'2019-08-19')
WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '5.17.3'
THEN CONVERT(DATE,'2019-01-01')
WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '5.17.4'
THEN CONVERT(DATE,'2019-04-01')
WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '5.17.5'
THEN CONVERT(DATE,'2019-06-01')
WHEN tblSoftwareUni.softwareName LIKE '%Connect%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '3.5'
THEN CONVERT(DATE,'2019-05-01')
WHEN tblSoftwareUni.softwareName LIKE '%Enterprise%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '8.5.3'
THEN CONVERT(DATE,'2019-03-01')
WHEN tblSoftwareUni.softwareName LIKE '%Enterprise%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '8.5.4'
THEN CONVERT(DATE,'2019-06-01')
WHEN tblSoftwareUni.softwareName LIKE '%IP360%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '8.1.1'
THEN CONVERT(DATE,'2019-05-01')
WHEN tblSoftwareUni.softwareName LIKE '%log%center%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '7.2.4'
THEN CONVERT(DATE,'2019-03-01')
WHEN tblSoftwareUni.softwareName LIKE '%log%center%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '7.2.5'
THEN CONVERT(DATE,'2019-06-01')
WHEN tblSoftwareUni.softwareName LIKE '%Intelligence%hub%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '2.7.3'
THEN CONVERT(DATE,'2019-03-01')
WHEN tblSoftwareUni.softwareName LIKE '%Winzip%'
AND tblSoftware.softwareVersion LIKE '20%'
THEN CONVERT(DATE,'2019-04-30')
WHEN tblSoftwareUni.softwareName LIKE '%Horizon%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%vmware%'
AND tblSoftware.softwareVersion LIKE '6.%'
THEN CONVERT(DATE,'2019-06-19')
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Blend%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-01-14')
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Design%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-01-14')
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Encoder%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-01-14')
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Studio%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-01-14')
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Web%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-01-14')
WHEN tblSoftwareUni.softwareName LIKE '%Forefront%Unified%Access%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-04-14')
WHEN tblSoftwareUni.softwareName LIKE '%Forefront%Threat%management%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-04-14')
WHEN tblSoftwareUni.softwareName LIKE '%Visual%Studio%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-07-14')
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Encoder%4%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-09-08')
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Web%4%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-09-08')
WHEN tblSoftwareUni.softwareName LIKE '%Acess%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-10-13')
WHEN tblSoftwareUni.softwareName LIKE '%Dynamics%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-10-13')
WHEN tblSoftwareUni.softwareName LIKE '%Excel%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-10-13')
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Studio%4%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-10-13')
WHEN tblSoftwareUni.softwareName LIKE '%Infopath%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-10-13')
WHEN tblSoftwareUni.softwareName LIKE '%Office%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-10-13')
WHEN tblSoftwareUni.softwareName LIKE '%Onenote%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-10-13')
WHEN tblSoftwareUni.softwareName LIKE '%Outlook%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-10-13')
WHEN tblSoftwareUni.softwareName LIKE '%powerpoint%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-10-13')
WHEN tblSoftwareUni.softwareName LIKE '%project%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-10-13')
WHEN tblSoftwareUni.softwareName LIKE '%publisher%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-10-13')
WHEN tblSoftwareUni.softwareName LIKE '%sharepoint%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-10-13')
WHEN tblSoftwareUni.softwareName LIKE '%visio%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-10-13')
WHEN tblSoftwareUni.softwareName LIKE '%visual%basic%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-10-13')
WHEN tblSoftwareUni.softwareName LIKE '%word%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN CONVERT(DATE,'2020-10-13')
END AS [EOS date],
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname AS OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftware.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
LEFT JOIN tsysOS ON tsysOS.OScode = tblAssets.OScode
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftwareUni.softwareName LIKE 'Coldfusion'
AND (tblSoftware.softwareVersion LIKE '11.%'
OR tblSoftware.softwareVersion LIKE '10.%')
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Correspondence%Management%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Designer%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%signature%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Documentum%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Content%Manager%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%FileNet%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%SharePoint%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Forms%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Output%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Process%Management%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Reader%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Rights%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Workbench%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%Apache%Struts%'
AND tblSoftware.softwareVersion LIKE '2.3.%'
OR tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion IN('4.13','4.14','5.0','5.1')
OR tblSoftwareUni.softwareName LIKE '%Confluence%'
AND tblSoftware.softwareVersion IN('6.1','6.2')
OR tblSoftwareUni.softwareName LIKE '%Jira%Core%'
AND tblSoftware.softwareVersion IN('7.3','7.4')
OR tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion IN('3.3','3.4','3.5','3.6')
OR tblSoftwareUni.softwareName LIKE '%Jira%Software%'
AND tblSoftware.softwareVersion IN('7.3','7.4')
OR tblSoftwareUni.softwareName LIKE '%Endpoint%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Checkpoint%'
AND (tblSoftware.softwareVersion LIKE '%80.61'
OR tblSoftware.softwareVersion LIKE '%80.63'
OR tblSoftware.softwareVersion LIKE '%80.65'
OR tblSoftware.softwareVersion LIKE '%80.66'
OR tblSoftware.softwareVersion LIKE '%80.67'
OR tblSoftware.softwareVersion LIKE '%80.68'
OR tblSoftware.softwareVersion LIKE '%80.69'
OR tblSoftware.softwareVersion LIKE '%5.6')
OR tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Server%'
AND tblSoftware.softwareVersion LIKE '2.8'
OR tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%'
AND (tblSoftware.softwareVersion LIKE '32.12.%'
OR tblSoftware.softwareVersion LIKE '32.%.%')
OR tblSoftwareUni.softwareName LIKE '%Websense%URL%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%'
AND tblSoftware.softwareVersion IN('7.0','7.1','7.5')
OR tblSoftwareUni.softwareName LIKE '%Websense%Express%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%'
AND tblSoftware.softwareVersion LIKE '1.0.0'
OR tblSoftwareUni.softwareName LIKE '%Jboss%Web%'
AND tblSoftware.softwareVersion LIKE '1.%'
OR tblSoftwareUni.softwareName LIKE '%Mediawiki%'
AND tblSoftware.softwareVersion LIKE '1.27.%'
OR tblSoftwareUni.softwareName LIKE '%Exchange%2010%'
OR tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion IN('7.0.7','7.0.8','7.0.9','7.0.10')
OR tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion IN('5.17.3','5.17.4','5.17.5')
OR tblSoftwareUni.softwareName LIKE '%Connect%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '3.5'
OR tblSoftwareUni.softwareName LIKE '%Enterprise%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion IN('8.5.3','8.5.4')
OR tblSoftwareUni.softwareName LIKE '%IP360%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '8.1.1'
OR tblSoftwareUni.softwareName LIKE '%log%center%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion IN('7.2.4','7.2.5')
OR tblSoftwareUni.softwareName LIKE '%Intelligence%hub%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '2.7.3'
OR tblSoftwareUni.softwareName LIKE '%Winzip%'
AND tblSoftware.softwareVersion LIKE '20%'
OR tblSoftwareUni.softwareName LIKE '%Horizon%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%vmware%'
AND tblSoftware.softwareVersion LIKE '6.%'
OR tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
AND (tblSoftwareUni.softwareName LIKE '%Expression%Blend%3%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Design%3%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Encoder%3%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Studio%3%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Web%3%'
OR tblSoftwareUni.softwareName LIKE '%Forefront%Unified%Access%2010%'
OR tblSoftwareUni.softwareName LIKE '%Forefront%Threat%management%2010%'
OR tblSoftwareUni.softwareName LIKE '%Visual%Studio%2010%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Encoder%4%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Web%4%'
OR tblSoftwareUni.softwareName LIKE '%Acess%2010%'
OR tblSoftwareUni.softwareName LIKE '%Dynamics%2010%'
OR tblSoftwareUni.softwareName LIKE '%Excel%2010%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Studio%4%'
OR tblSoftwareUni.softwareName LIKE '%Infopath%2010%'
OR tblSoftwareUni.softwareName LIKE '%Office%2010%'
OR tblSoftwareUni.softwareName LIKE '%Onenote%2010%'
OR tblSoftwareUni.softwareName LIKE '%Outlook%2010%'
OR tblSoftwareUni.softwareName LIKE '%powerpoint%2010%'
OR tblSoftwareUni.softwareName LIKE '%project%2010%'
OR tblSoftwareUni.softwareName LIKE '%publisher%2010%'
OR tblSoftwareUni.softwareName LIKE '%sharepoint%2010%'
OR tblSoftwareUni.softwareName LIKE '%visio%2010%'
OR tblSoftwareUni.softwareName LIKE '%visual%basic%2010%'
OR tblSoftwareUni.softwareName LIKE '%word%2010%')
AND tblState.Statename = 'Active'
ORDER BY tblAssets.Domain,
tblAssets.AssetName;
Esben_D
Lansweeper Employee
Lansweeper Employee
nnewton wrote:
CONVERT should work in Compact shouldn't it?


Convert is supported, but I believe SQL Compact does not support DATE and only does datetime. I fixed it by using a cast and datetime.
SELECT tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
COALESCE(tsysOS.IMAGE,tsysAssetTypes.AssetTypeIcon10) AS icon,
tblAssets.IPAddress,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.softwareVersion AS Version,
CASE
WHEN tblSoftwareUni.softwareName LIKE 'Coldfusion'
AND tblSoftware.softwareVersion LIKE '11.%'
THEN cast('2019-04-30' as datetime)
WHEN tblSoftwareUni.softwareName LIKE 'Coldfusion'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-05-16' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Correspondence%Management%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Designer%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%signature%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Documentum%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Content%Manager%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%FileNet%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%SharePoint%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Forms%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Output%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Process%Management%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Reader%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Rights%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Workbench%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Apache%Struts%'
AND tblSoftware.softwareVersion LIKE '2.3.%'
THEN cast('2019-05-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion LIKE '4.13'
THEN cast('2019-01-20' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion LIKE '4.14'
THEN cast('2019-02-21' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion LIKE '5.0'
THEN cast('2019-05-02' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion LIKE '5.1'
THEN cast('2019-06-06' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Confluence%'
AND tblSoftware.softwareVersion LIKE '6.1'
THEN cast('2019-03-20' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Confluence%'
AND tblSoftware.softwareVersion LIKE '6.2'
THEN cast('2019-05-15' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Core%'
AND tblSoftware.softwareVersion LIKE '7.3'
THEN cast('2019-01-03' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Core%'
AND tblSoftware.softwareVersion LIKE '7.4'
THEN cast('2019-06-29' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion LIKE '3.3'
THEN cast('2019-01-03' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion LIKE '3.4'
THEN cast('2019-03-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion LIKE '3.5'
THEN cast('2019-04-27' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion LIKE '3.6'
THEN cast('2019-06-29' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Software%'
AND tblSoftware.softwareVersion LIKE '7.3'
THEN cast('2019-01-03' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Software%'
AND tblSoftware.softwareVersion LIKE '7.4'
THEN cast('2019-06-29' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Endpoint%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Checkpoint%'
AND (tblSoftware.softwareVersion LIKE '%80.61'
OR tblSoftware.softwareVersion LIKE '%80.63'
OR tblSoftware.softwareVersion LIKE '%80.65'
OR tblSoftware.softwareVersion LIKE '%80.66'
OR tblSoftware.softwareVersion LIKE '%80.67'
OR tblSoftware.softwareVersion LIKE '%80.68'
OR tblSoftware.softwareVersion LIKE '%80.69'
OR tblSoftware.softwareVersion LIKE '%5.6')
THEN cast('2018-11-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Endpoint%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Checkpoint%'
AND tblSoftware.softwareVersion LIKE '%5.6'
THEN cast('2019-06-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Server%'
AND tblSoftware.softwareVersion LIKE '2.8'
THEN cast('2019-00-20' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%'
AND tblSoftware.softwareVersion LIKE '32.12.%'
THEN cast('2019-01-11' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%'
AND tblSoftware.softwareVersion LIKE '32.%.%'
THEN cast('2019-05-24' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Websense%URL%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%'
AND tblSoftware.softwareVersion IN('7.0','7.1','7.5')
THEN cast('2019-04-02' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Websense%Express%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%'
AND tblSoftware.softwareVersion LIKE '1.0.0'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jboss%Web%'
AND tblSoftware.softwareVersion LIKE '1.%'
THEN cast('2020-04-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Mediawiki%'
AND tblSoftware.softwareVersion LIKE '1.27.%'
THEN cast('2019-06-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Exchange%2010%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion LIKE '7.0.7'
THEN cast('2019-02-25' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion LIKE '7.0.8'
THEN cast('2019-04-22' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion LIKE '7.0.9'
THEN cast('2019-06-19' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion LIKE '7.0.10'
THEN cast('2019-08-19' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '5.17.3'
THEN cast('2019-01-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '5.17.4'
THEN cast('2019-04-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '5.17.5'
THEN cast('2019-06-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Connect%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '3.5'
THEN cast('2019-05-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Enterprise%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '8.5.3'
THEN cast('2019-03-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Enterprise%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '8.5.4'
THEN cast('2019-06-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%IP360%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '8.1.1'
THEN cast('2019-05-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%log%center%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '7.2.4'
THEN cast('2019-03-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%log%center%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '7.2.5'
THEN cast('2019-06-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Intelligence%hub%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '2.7.3'
THEN cast('2019-03-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Winzip%'
AND tblSoftware.softwareVersion LIKE '20%'
THEN cast('2019-04-30' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Horizon%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%vmware%'
AND tblSoftware.softwareVersion LIKE '6.%'
THEN cast('2019-06-19' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Blend%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Design%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Encoder%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Studio%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Web%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Forefront%Unified%Access%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-04-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Forefront%Threat%management%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-04-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Visual%Studio%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-07-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Encoder%4%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-09-08' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Web%4%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-09-08' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Acess%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Dynamics%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Excel%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Studio%4%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Infopath%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Office%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Onenote%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Outlook%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%powerpoint%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%project%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%publisher%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%sharepoint%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%visio%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%visual%basic%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%word%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
END AS [EOS date],
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname AS OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftware.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
LEFT JOIN tsysOS ON tsysOS.OScode = tblAssets.OScode
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftwareUni.softwareName LIKE 'Coldfusion'
AND (tblSoftware.softwareVersion LIKE '11.%'
OR tblSoftware.softwareVersion LIKE '10.%')
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Correspondence%Management%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Designer%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%signature%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Documentum%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Content%Manager%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%FileNet%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%SharePoint%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Forms%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Output%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Process%Management%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Reader%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Rights%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Workbench%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%Apache%Struts%'
AND tblSoftware.softwareVersion LIKE '2.3.%'
OR tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion IN('4.13','4.14','5.0','5.1')
OR tblSoftwareUni.softwareName LIKE '%Confluence%'
AND tblSoftware.softwareVersion IN('6.1','6.2')
OR tblSoftwareUni.softwareName LIKE '%Jira%Core%'
AND tblSoftware.softwareVersion IN('7.3','7.4')
OR tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion IN('3.3','3.4','3.5','3.6')
OR tblSoftwareUni.softwareName LIKE '%Jira%Software%'
AND tblSoftware.softwareVersion IN('7.3','7.4')
OR tblSoftwareUni.softwareName LIKE '%Endpoint%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Checkpoint%'
AND (tblSoftware.softwareVersion LIKE '%80.61'
OR tblSoftware.softwareVersion LIKE '%80.63'
OR tblSoftware.softwareVersion LIKE '%80.65'
OR tblSoftware.softwareVersion LIKE '%80.66'
OR tblSoftware.softwareVersion LIKE '%80.67'
OR tblSoftware.softwareVersion LIKE '%80.68'
OR tblSoftware.softwareVersion LIKE '%80.69'
OR tblSoftware.softwareVersion LIKE '%5.6')
OR tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Server%'
AND tblSoftware.softwareVersion LIKE '2.8'
OR tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%'
AND (tblSoftware.softwareVersion LIKE '32.12.%'
OR tblSoftware.softwareVersion LIKE '32.%.%')
OR tblSoftwareUni.softwareName LIKE '%Websense%URL%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%'
AND tblSoftware.softwareVersion IN('7.0','7.1','7.5')
OR tblSoftwareUni.softwareName LIKE '%Websense%Express%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%'
AND tblSoftware.softwareVersion LIKE '1.0.0'
OR tblSoftwareUni.softwareName LIKE '%Jboss%Web%'
AND tblSoftware.softwareVersion LIKE '1.%'
OR tblSoftwareUni.softwareName LIKE '%Mediawiki%'
AND tblSoftware.softwareVersion LIKE '1.27.%'
OR tblSoftwareUni.softwareName LIKE '%Exchange%2010%'
OR tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion IN('7.0.7','7.0.8','7.0.9','7.0.10')
OR tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion IN('5.17.3','5.17.4','5.17.5')
OR tblSoftwareUni.softwareName LIKE '%Connect%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '3.5'
OR tblSoftwareUni.softwareName LIKE '%Enterprise%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion IN('8.5.3','8.5.4')
OR tblSoftwareUni.softwareName LIKE '%IP360%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '8.1.1'
OR tblSoftwareUni.softwareName LIKE '%log%center%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion IN('7.2.4','7.2.5')
OR tblSoftwareUni.softwareName LIKE '%Intelligence%hub%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '2.7.3'
OR tblSoftwareUni.softwareName LIKE '%Winzip%'
AND tblSoftware.softwareVersion LIKE '20%'
OR tblSoftwareUni.softwareName LIKE '%Horizon%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%vmware%'
AND tblSoftware.softwareVersion LIKE '6.%'
OR tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
AND (tblSoftwareUni.softwareName LIKE '%Expression%Blend%3%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Design%3%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Encoder%3%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Studio%3%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Web%3%'
OR tblSoftwareUni.softwareName LIKE '%Forefront%Unified%Access%2010%'
OR tblSoftwareUni.softwareName LIKE '%Forefront%Threat%management%2010%'
OR tblSoftwareUni.softwareName LIKE '%Visual%Studio%2010%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Encoder%4%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Web%4%'
OR tblSoftwareUni.softwareName LIKE '%Acess%2010%'
OR tblSoftwareUni.softwareName LIKE '%Dynamics%2010%'
OR tblSoftwareUni.softwareName LIKE '%Excel%2010%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Studio%4%'
OR tblSoftwareUni.softwareName LIKE '%Infopath%2010%'
OR tblSoftwareUni.softwareName LIKE '%Office%2010%'
OR tblSoftwareUni.softwareName LIKE '%Onenote%2010%'
OR tblSoftwareUni.softwareName LIKE '%Outlook%2010%'
OR tblSoftwareUni.softwareName LIKE '%powerpoint%2010%'
OR tblSoftwareUni.softwareName LIKE '%project%2010%'
OR tblSoftwareUni.softwareName LIKE '%publisher%2010%'
OR tblSoftwareUni.softwareName LIKE '%sharepoint%2010%'
OR tblSoftwareUni.softwareName LIKE '%visio%2010%'
OR tblSoftwareUni.softwareName LIKE '%visual%basic%2010%'
OR tblSoftwareUni.softwareName LIKE '%word%2010%')
AND tblState.Statename = 'Active'
ORDER BY tblAssets.Domain,
tblAssets.AssetName
Esben_D
Lansweeper Employee
Lansweeper Employee
It's the DATEFROMPARTS specifically that is not supported. I'll take a look next week to see if I can find a workaround.
Josi
Engaged Sweeper
What about Microsoft SQL server expiring in a few months? I cannot see it in the list.
Here is the reference:
https://blogs.msdn.microsoft.com/sqlreleaseservices/end-of-mainstream-support-for-sql-server-2008-and-sql-server-2008-r2/
eric_persiali
Engaged Sweeper
Hello,

Sorry, last version of the report (10th of May) doesn't work...
Got error message in red when you save it as a new report in Lansweeper.

Error doesn't help to find where the issue is, could someone check it and find the problem?

Thanks.
Regards
Eric
Great to see all the improvements guys! I've fixed the typo in the original post. For now, until I find a better report which works in both SQL Server and SQL Compact, I'll keep the original report up.

Centralis IT - E wrote:
Hello,

Sorry, last version of the report (10th of May) doesn't work...
Got error message in red when you save it as a new report in Lansweeper.

Error doesn't help to find where the issue is, could someone check it and find the problem?

Thanks.
Regards
Eric


The last report posted by nnewton will only work in SQL Server installations. The SQL code used is not supported in SQL Compact.
Esben.D wrote:
Great to see all the improvements guys! I've fixed the typo in the original post. For now, until I find a better report which works in both SQL Server and SQL Compact, I'll keep the original report up.

Centralis IT - E wrote:
Hello,

Sorry, last version of the report (10th of May) doesn't work...
Got error message in red when you save it as a new report in Lansweeper.

Error doesn't help to find where the issue is, could someone check it and find the problem?

Thanks.
Regards
Eric


The last report posted by nnewton will only work in SQL Server installations. The SQL code used is not supported in SQL Compact.


Thanks, just for my knowledge, may I ask what part of the code isn't working in SQL compact?