This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject

→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !

Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

End of Support Software Report

Esben_D
Lansweeper Employee
Lansweeper Employee

‎05-07-2019 03:35 PM

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

‎12-20-2023 08:14 PM

Is there a current report that covers 2023 or later?

0 Kudos
rasldasl
Engaged Sweeper III

‎05-14-2019 12:31 AM

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

‎05-13-2019 02:58 AM

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;
0 Kudos
Esben_D
Lansweeper Employee
Lansweeper Employee
In response to nnewton

‎05-13-2019 10:04 AM

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
0 Kudos
Esben_D
Lansweeper Employee
Lansweeper Employee

‎05-10-2019 03:25 PM

It's the DATEFROMPARTS specifically that is not supported. I'll take a look next week to see if I can find a workaround.
0 Kudos
Josi
Engaged Sweeper

‎05-10-2019 10:47 AM

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

‎05-10-2019 09:11 AM

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
0 Kudos
Esben_D
Lansweeper Employee
Lansweeper Employee
In response to eric_persiali

‎05-10-2019 10:01 AM

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.
0 Kudos
eric_persiali
Engaged Sweeper
In response to Esben_D

‎05-10-2019 11:25 AM

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?
0 Kudos

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now