Notification

Icon
Error

End of Support Software Report

Posted: Tuesday, May 7, 2019 2:35:46 PM(UTC)
Esben.D

Esben.D

Member Administration Original PosterPosts: 1,834
7
Like
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.

Code:
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
kevinrpjones
#1kevinrpjones Member Posts: 8  
posted: 5/9/2019 3:22:58 PM(UTC)
Great Report!.. There is a typo.. just an extra zero:

Line 157 tblSoftwareUni.SoftwarePublisher Like '%Microsoft%' Then '7/14/20200'
RC62N
#2RC62N Member Posts: 374  
posted: 5/9/2019 4:09:02 PM(UTC)
Thanks Esben.D.

For the benefit of those who prefer ISO date formatting, I've modified that, including kevinrojones's catch of the 20200 typo.

Code:
Select Top 1000000
  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 '2019-04-30'
    WHEN tblSoftwareUni.softwareName LIKE 'Coldfusion' AND tblSoftware.softwareVersion LIKE '10.%' THEN '2019-05-16'
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Correspondence%Management%' And tblSoftware.softwareVersion LIKE '10.%' THEN '2019-03-31'
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Designer%ES3%' AND tblSoftware.softwareVersion LIKE '10.%' THEN '2019-03-31'
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%signature%ES3%' AND tblSoftware.softwareVersion LIKE '10.%' THEN '2019-03-31'
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Documentum%' And tblSoftware.softwareVersion LIKE '10.%' THEN '2019-03-31'
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Content%Manager%' AND tblSoftware.softwareVersion LIKE '10.%' THEN '2019-03-31'
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%FileNet%' And tblSoftware.softwareVersion LIKE '10.%' THEN '2019-03-31'
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%SharePoint%' And tblSoftware.softwareVersion LIKE '10.%' THEN '2019-03-31'
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Forms%ES3%' AND tblSoftware.softwareVersion LIKE '10.%' THEN '2019-03-31'
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Output%ES3%' AND tblSoftware.softwareVersion LIKE '10.%' THEN '2019-03-31'
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Process%Management%ES3%' And tblSoftware.softwareVersion LIKE '10.%' THEN '2019-03-31'
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Reader%ES3%' AND tblSoftware.softwareVersion LIKE '10.%' THEN '2019-03-31'
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Rights%ES3%' AND tblSoftware.softwareVersion LIKE '10.%' THEN '2019-03-31'
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Workbench%ES3%' AND tblSoftware.softwareVersion LIKE '10.%' THEN '2019-03-31'
    WHEN tblSoftwareUni.softwareName LIKE '%Apache%Struts%' AND tblSoftware.softwareVersion LIKE '2.3.%' THEN '2019-05-14'
    WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%' AND tblSoftware.softwareVersion LIKE '4.13' THEN '2019-01-20'
    WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%' AND tblSoftware.softwareVersion LIKE '4.14' THEN '2019-02-21'
    WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%' AND tblSoftware.softwareVersion LIKE '5.0' THEN '2019-05-02'
    WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%' AND tblSoftware.softwareVersion LIKE '5.1' THEN '2019-06-06'
    WHEN tblSoftwareUni.softwareName LIKE '%Confluence%' AND tblSoftware.softwareVersion LIKE '6.1' THEN '2019-03-20'
    WHEN tblSoftwareUni.softwareName LIKE '%Confluence%' AND tblSoftware.softwareVersion LIKE '6.2' THEN '2019-05-15'
    WHEN tblSoftwareUni.softwareName LIKE '%Jira%Core%' AND tblSoftware.softwareVersion LIKE '7.3' THEN '2019-01-03'
    WHEN tblSoftwareUni.softwareName LIKE '%Jira%Core%' AND tblSoftware.softwareVersion LIKE '7.4' THEN '2019-06-29'
    WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%' AND tblSoftware.softwareVersion LIKE '3.3' THEN '2019-01-03'
    WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%' AND tblSoftware.softwareVersion LIKE '3.4' THEN '2019-03-14'
    WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%' AND tblSoftware.softwareVersion LIKE '3.5' THEN '2019-04-27'
    WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%' AND tblSoftware.softwareVersion LIKE '3.6' THEN '2019-06-29'
    WHEN tblSoftwareUni.softwareName LIKE '%Jira%Software%' AND tblSoftware.softwareVersion LIKE '7.3' THEN '2019-01-03'
    WHEN tblSoftwareUni.softwareName LIKE '%Jira%Software%' AND tblSoftware.softwareVersion LIKE '7.4' THEN '2019-06-29'
    WHEN tblSoftwareUni.softwareName LIKE '%Endpoint%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Checkpoint%' AND tblSoftware.softwareVersion In ('%80.61', '%80.63', '%80.65', '%80.66', '%80.67', '%80.68', '%80.69') THEN '2018-11-01'
    WHEN tblSoftwareUni.softwareName LIKE '%Endpoint%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Checkpoint%' AND tblSoftware.softwareVersion LIKE '%5.6' THEN '2019-06-01'
    WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Server%' AND tblSoftware.softwareVersion LIKE '2.8' THEN '2019-00-20'
    WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%' AND tblSoftware.softwareVersion LIKE '32.12.%' THEN '2019-01-11'
    WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%' AND tblSoftware.softwareVersion LIKE '32.%.%' THEN '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 '2019-04-02'
    WHEN tblSoftwareUni.softwareName LIKE '%Websense%Express%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%' AND tblSoftware.softwareVersion LIKE '1.0.0' THEN '2019-03-31'
    WHEN tblSoftwareUni.softwareName LIKE '%Jboss%Web%' AND tblSoftware.softwareVersion LIKE '1.%' THEN '2020-04-01'
    WHEN tblSoftwareUni.softwareName LIKE '%Mediawiki%' AND tblSoftware.softwareVersion LIKE '1.27.%' THEN '2019-06-01'
    WHEN tblSoftwareUni.softwareName LIKE '%Exchange%2010%' THEN '2020-01-14'
    WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%' AND tblSoftware.softwareVersion LIKE '7.0.7' THEN '2019-02-25'
    WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%' AND tblSoftware.softwareVersion LIKE '7.0.8' THEN '2019-04-22'
    WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%' AND tblSoftware.softwareVersion LIKE '7.0.9' THEN '2019-06-19'
    WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%' AND tblSoftware.softwareVersion LIKE '7.0.10' THEN '2019-08-19'
    WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '5.17.3' THEN '2019-01-01'
    WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '5.17.4' THEN '2019-04-01'
    WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '5.17.5' THEN '2019-06-01'
    WHEN tblSoftwareUni.softwareName LIKE '%Connect%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '3.5' THEN '2019-05-01'
    WHEN tblSoftwareUni.softwareName LIKE '%Enterprise%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '8.5.3' THEN '2019-03-01'
    WHEN tblSoftwareUni.softwareName LIKE '%Enterprise%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '8.5.4' THEN '2019-06-01'
    WHEN tblSoftwareUni.softwareName LIKE '%IP360%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '8.1.1' THEN '2019-05-01'
    WHEN tblSoftwareUni.softwareName LIKE '%log%center%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '7.2.4' THEN '2019-03-01'
    WHEN tblSoftwareUni.softwareName LIKE '%log%center%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '7.2.5' THEN '2019-06-01'
    WHEN tblSoftwareUni.softwareName LIKE '%Intelligence%hub%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '2.7.3' THEN '2019-03-01'
    WHEN tblSoftwareUni.softwareName LIKE '%Winzip%' AND tblSoftware.softwareVersion LIKE '20%' THEN '2019-04-30'
    WHEN tblSoftwareUni.softwareName LIKE '%Horizon%' AND tblSoftwareUni.SoftwarePublisher LIKE '%vmware%' AND tblSoftware.softwareVersion LIKE '6.%' THEN '2019-06-19'
    WHEN tblSoftwareUni.softwareName LIKE '%Expression%Blend%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-01-14'
    WHEN tblSoftwareUni.softwareName LIKE '%Expression%Design%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-01-14'
    WHEN tblSoftwareUni.softwareName LIKE '%Expression%Encoder%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-01-14'
    WHEN tblSoftwareUni.softwareName LIKE '%Expression%Studio%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-01-14'
    WHEN tblSoftwareUni.softwareName LIKE '%Expression%Web%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-01-14'
    WHEN tblSoftwareUni.softwareName LIKE '%Forefront%Unified%Access%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-04-14'
    WHEN tblSoftwareUni.softwareName LIKE '%Forefront%Threat%management%2010%' And tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-04-14'
    WHEN tblSoftwareUni.softwareName LIKE '%Visual%Studio%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-07-14'
    WHEN tblSoftwareUni.softwareName LIKE '%Expression%Encoder%4%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-09-08'
    WHEN tblSoftwareUni.softwareName LIKE '%Expression%Web%4%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-09-08'
    WHEN tblSoftwareUni.softwareName LIKE '%Acess%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-10-13'
    WHEN tblSoftwareUni.softwareName LIKE '%Dynamics%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-10-13'
    WHEN tblSoftwareUni.softwareName LIKE '%Excel%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-10-13'
    WHEN tblSoftwareUni.softwareName LIKE '%Expression%Studio%4%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-10-13'
    WHEN tblSoftwareUni.softwareName LIKE '%Infopath%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-10-13'
    WHEN tblSoftwareUni.softwareName LIKE '%Office%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-10-13'
    WHEN tblSoftwareUni.softwareName LIKE '%Onenote%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-10-13'
    WHEN tblSoftwareUni.softwareName LIKE '%Outlook%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-10-13'
    WHEN tblSoftwareUni.softwareName LIKE '%powerpoint%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-10-13'
    WHEN tblSoftwareUni.softwareName LIKE '%project%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-10-13'
    WHEN tblSoftwareUni.softwareName LIKE '%publisher%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-10-13'
    WHEN tblSoftwareUni.softwareName LIKE '%sharepoint%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-10-13'
    WHEN tblSoftwareUni.softwareName LIKE '%visio%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-10-13'
    WHEN tblSoftwareUni.softwareName LIKE '%visual%basic%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-10-13'
    WHEN tblSoftwareUni.softwareName LIKE '%word%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN '2020-10-13'
  End As [EOS date (yyyy-mm-dd)],
  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 In ('%80.61', '%80.63', '%80.65', '%80.66', '%80.67', '%80.68', '%80.69', '%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 In ('32.12.%', '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.softwareName LIKE '%Expression%Blend%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Expression%Design%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Expression%Encoder%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Expression%Studio%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Expression%Web%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Forefront%Unified%Access%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Forefront%Threat%management%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Visual%Studio%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Expression%Encoder%4%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Expression%Web%4%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Acess%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Dynamics%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Excel%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Expression%Studio%4%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Infopath%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Office%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Onenote%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Outlook%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%powerpoint%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%project%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%publisher%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%sharepoint%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%visio%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%visual%basic%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%word%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' And tblState.Statename = 'Active')
Order By
  tblAssets.Domain,
  tblAssets.AssetName
francisswest
#3francisswest Member Posts: 25  
posted: 5/9/2019 4:25:39 PM(UTC)
A cool report to be sure, but did it absolutely trash LS performance for anyone else?

We added it in, and let it run, slowed down every single aspect of LS. Removed the report, everything is back to normal.
SAHarrington
#4SAHarrington Member Posts: 2  
posted: 5/9/2019 4:54:07 PM(UTC)
Originally Posted by: francisswest Go to Quoted Post
A cool report to be sure, but did it absolutely trash LS performance for anyone else?

We added it in, and let it run, slowed down every single aspect of LS. Removed the report, everything is back to normal.


Yes, it did the same to my deployment. It dragged everything to a halt even when I wasn't in the report. Not sure what it did, but after deleting the report we're back to normal.
francisswest
#5francisswest Member Posts: 25  
posted: 5/9/2019 4:57:13 PM(UTC)
Originally Posted by: SAHarrington Go to Quoted Post
Originally Posted by: francisswest Go to Quoted Post
A cool report to be sure, but did it absolutely trash LS performance for anyone else?

We added it in, and let it run, slowed down every single aspect of LS. Removed the report, everything is back to normal.


Yes, it did the same to my deployment. It dragged everything to a halt even when I wasn't in the report. Not sure what it did, but after deleting the report we're back to normal.


Im so glad it wasnt just me and like, a poorly optimized server, ha.
RC62N
#6RC62N Member Posts: 374  
posted: 5/9/2019 5:41:40 PM(UTC)
A better thought: make the EOS date a date rather than a string. That should format the output in your preferred date format while making it sortable.
Code:
Select Top 1000000
  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 DateFromParts(2019, 04, 30)
    WHEN tblSoftwareUni.softwareName LIKE 'Coldfusion' AND tblSoftware.softwareVersion LIKE '10.%' THEN DateFromParts(2019, 05, 16)
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Correspondence%Management%' And tblSoftware.softwareVersion LIKE '10.%' THEN DateFromParts(2019, 03, 31)
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Designer%ES3%' AND tblSoftware.softwareVersion LIKE '10.%' THEN DateFromParts(2019, 03, 31)
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%signature%ES3%' AND tblSoftware.softwareVersion LIKE '10.%' THEN DateFromParts(2019, 03, 31)
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Documentum%' And tblSoftware.softwareVersion LIKE '10.%' THEN DateFromParts(2019, 03, 31)
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Content%Manager%' AND tblSoftware.softwareVersion LIKE '10.%' THEN DateFromParts(2019, 03, 31)
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%FileNet%' And tblSoftware.softwareVersion LIKE '10.%' THEN DateFromParts(2019, 03, 31)
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%SharePoint%' And tblSoftware.softwareVersion LIKE '10.%' THEN DateFromParts(2019, 03, 31)
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Forms%ES3%' AND tblSoftware.softwareVersion LIKE '10.%' THEN DateFromParts(2019, 03, 31)
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Output%ES3%' AND tblSoftware.softwareVersion LIKE '10.%' THEN DateFromParts(2019, 03, 31)
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Process%Management%ES3%' And tblSoftware.softwareVersion LIKE '10.%' THEN DateFromParts(2019, 03, 31)
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Reader%ES3%' AND tblSoftware.softwareVersion LIKE '10.%' THEN DateFromParts(2019, 03, 31)
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Rights%ES3%' AND tblSoftware.softwareVersion LIKE '10.%' THEN DateFromParts(2019, 03, 31)
    WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Workbench%ES3%' AND tblSoftware.softwareVersion LIKE '10.%' THEN DateFromParts(2019, 03, 31)
    WHEN tblSoftwareUni.softwareName LIKE '%Apache%Struts%' AND tblSoftware.softwareVersion LIKE '2.3.%' THEN DateFromParts(2019, 05, 14)
    WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%' AND tblSoftware.softwareVersion LIKE '4.13' THEN DateFromParts(2019, 01, 20)
    WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%' AND tblSoftware.softwareVersion LIKE '4.14' THEN DateFromParts(2019, 02, 21)
    WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%' AND tblSoftware.softwareVersion LIKE '5.0' THEN DateFromParts(2019, 05, 02)
    WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%' AND tblSoftware.softwareVersion LIKE '5.1' THEN DateFromParts(2019, 06, 06)
    WHEN tblSoftwareUni.softwareName LIKE '%Confluence%' AND tblSoftware.softwareVersion LIKE '6.1' THEN DateFromParts(2019, 03, 20)
    WHEN tblSoftwareUni.softwareName LIKE '%Confluence%' AND tblSoftware.softwareVersion LIKE '6.2' THEN DateFromParts(2019, 05, 15)
    WHEN tblSoftwareUni.softwareName LIKE '%Jira%Core%' AND tblSoftware.softwareVersion LIKE '7.3' THEN DateFromParts(2019, 01, 03)
    WHEN tblSoftwareUni.softwareName LIKE '%Jira%Core%' AND tblSoftware.softwareVersion LIKE '7.4' THEN DateFromParts(2019, 06, 29)
    WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%' AND tblSoftware.softwareVersion LIKE '3.3' THEN DateFromParts(2019, 01, 03)
    WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%' AND tblSoftware.softwareVersion LIKE '3.4' THEN DateFromParts(2019, 03, 14)
    WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%' AND tblSoftware.softwareVersion LIKE '3.5' THEN DateFromParts(2019, 04, 27)
    WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%' AND tblSoftware.softwareVersion LIKE '3.6' THEN DateFromParts(2019, 06, 29)
    WHEN tblSoftwareUni.softwareName LIKE '%Jira%Software%' AND tblSoftware.softwareVersion LIKE '7.3' THEN DateFromParts(2019, 01, 03)
    WHEN tblSoftwareUni.softwareName LIKE '%Jira%Software%' AND tblSoftware.softwareVersion LIKE '7.4' THEN DateFromParts(2019, 06, 29)
    WHEN tblSoftwareUni.softwareName LIKE '%Endpoint%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Checkpoint%' AND tblSoftware.softwareVersion In ('%80.61', '%80.63', '%80.65', '%80.66', '%80.67', '%80.68', '%80.69') THEN DateFromParts(2018, 11, 01)
    WHEN tblSoftwareUni.softwareName LIKE '%Endpoint%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Checkpoint%' AND tblSoftware.softwareVersion LIKE '%5.6' THEN DateFromParts(2019, 06, 01)
    WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Server%' AND tblSoftware.softwareVersion LIKE '2.8' THEN DateFromParts(2019, 00, 20)
    WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%' AND tblSoftware.softwareVersion LIKE '32.12.%' THEN DateFromParts(2019, 01, 11)
    WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%' AND tblSoftware.softwareVersion LIKE '32.%.%' THEN DateFromParts(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 DateFromParts(2019, 04, 02)
    WHEN tblSoftwareUni.softwareName LIKE '%Websense%Express%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%' AND tblSoftware.softwareVersion LIKE '1.0.0' THEN DateFromParts(2019, 03, 31)
    WHEN tblSoftwareUni.softwareName LIKE '%Jboss%Web%' AND tblSoftware.softwareVersion LIKE '1.%' THEN DateFromParts(2020, 04, 01)
    WHEN tblSoftwareUni.softwareName LIKE '%Mediawiki%' AND tblSoftware.softwareVersion LIKE '1.27.%' THEN DateFromParts(2019, 06, 01)
    WHEN tblSoftwareUni.softwareName LIKE '%Exchange%2010%' THEN DateFromParts(2020, 01, 14)
    WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%' AND tblSoftware.softwareVersion LIKE '7.0.7' THEN DateFromParts(2019, 02, 25)
    WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%' AND tblSoftware.softwareVersion LIKE '7.0.8' THEN DateFromParts(2019, 04, 22)
    WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%' AND tblSoftware.softwareVersion LIKE '7.0.9' THEN DateFromParts(2019, 06, 19)
    WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%' AND tblSoftware.softwareVersion LIKE '7.0.10' THEN DateFromParts(2019, 08, 19)
    WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '5.17.3' THEN DateFromParts(2019, 01, 01)
    WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '5.17.4' THEN DateFromParts(2019, 04, 01)
    WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '5.17.5' THEN DateFromParts(2019, 06, 01)
    WHEN tblSoftwareUni.softwareName LIKE '%Connect%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '3.5' THEN DateFromParts(2019, 05, 01)
    WHEN tblSoftwareUni.softwareName LIKE '%Enterprise%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '8.5.3' THEN DateFromParts(2019, 03, 01)
    WHEN tblSoftwareUni.softwareName LIKE '%Enterprise%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '8.5.4' THEN DateFromParts(2019, 06, 01)
    WHEN tblSoftwareUni.softwareName LIKE '%IP360%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '8.1.1' THEN DateFromParts(2019, 05, 01)
    WHEN tblSoftwareUni.softwareName LIKE '%log%center%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '7.2.4' THEN DateFromParts(2019, 03, 01)
    WHEN tblSoftwareUni.softwareName LIKE '%log%center%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '7.2.5' THEN DateFromParts(2019, 06, 01)
    WHEN tblSoftwareUni.softwareName LIKE '%Intelligence%hub%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%' AND tblSoftware.softwareVersion LIKE '2.7.3' THEN DateFromParts(2019, 03, 01)
    WHEN tblSoftwareUni.softwareName LIKE '%Winzip%' AND tblSoftware.softwareVersion LIKE '20%' THEN DateFromParts(2019, 04, 30)
    WHEN tblSoftwareUni.softwareName LIKE '%Horizon%' AND tblSoftwareUni.SoftwarePublisher LIKE '%vmware%' AND tblSoftware.softwareVersion LIKE '6.%' THEN DateFromParts(2019, 06, 19)
    WHEN tblSoftwareUni.softwareName LIKE '%Expression%Blend%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 01, 14)
    WHEN tblSoftwareUni.softwareName LIKE '%Expression%Design%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 01, 14)
    WHEN tblSoftwareUni.softwareName LIKE '%Expression%Encoder%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 01, 14)
    WHEN tblSoftwareUni.softwareName LIKE '%Expression%Studio%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 01, 14)
    WHEN tblSoftwareUni.softwareName LIKE '%Expression%Web%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 01, 14)
    WHEN tblSoftwareUni.softwareName LIKE '%Forefront%Unified%Access%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 04, 14)
    WHEN tblSoftwareUni.softwareName LIKE '%Forefront%Threat%management%2010%' And tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 04, 14)
    WHEN tblSoftwareUni.softwareName LIKE '%Visual%Studio%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 07, 14)
    WHEN tblSoftwareUni.softwareName LIKE '%Expression%Encoder%4%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 09, 08)
    WHEN tblSoftwareUni.softwareName LIKE '%Expression%Web%4%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 09, 08)
    WHEN tblSoftwareUni.softwareName LIKE '%Acess%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 10, 13)
    WHEN tblSoftwareUni.softwareName LIKE '%Dynamics%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 10, 13)
    WHEN tblSoftwareUni.softwareName LIKE '%Excel%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 10, 13)
    WHEN tblSoftwareUni.softwareName LIKE '%Expression%Studio%4%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 10, 13)
    WHEN tblSoftwareUni.softwareName LIKE '%Infopath%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 10, 13)
    WHEN tblSoftwareUni.softwareName LIKE '%Office%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 10, 13)
    WHEN tblSoftwareUni.softwareName LIKE '%Onenote%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 10, 13)
    WHEN tblSoftwareUni.softwareName LIKE '%Outlook%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 10, 13)
    WHEN tblSoftwareUni.softwareName LIKE '%powerpoint%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 10, 13)
    WHEN tblSoftwareUni.softwareName LIKE '%project%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 10, 13)
    WHEN tblSoftwareUni.softwareName LIKE '%publisher%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 10, 13)
    WHEN tblSoftwareUni.softwareName LIKE '%sharepoint%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 10, 13)
    WHEN tblSoftwareUni.softwareName LIKE '%visio%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 10, 13)
    WHEN tblSoftwareUni.softwareName LIKE '%visual%basic%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(2020, 10, 13)
    WHEN tblSoftwareUni.softwareName LIKE '%word%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' THEN DateFromParts(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 In ('%80.61', '%80.63', '%80.65', '%80.66', '%80.67', '%80.68', '%80.69', '%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 In ('32.12.%', '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.softwareName LIKE '%Expression%Blend%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Expression%Design%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Expression%Encoder%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Expression%Studio%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Expression%Web%3%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Forefront%Unified%Access%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Forefront%Threat%management%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Visual%Studio%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Expression%Encoder%4%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Expression%Web%4%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Acess%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Dynamics%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Excel%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Expression%Studio%4%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Infopath%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Office%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Onenote%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%Outlook%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%powerpoint%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%project%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%publisher%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%sharepoint%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%visio%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%visual%basic%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%')
  OR (tblSoftwareUni.softwareName LIKE '%word%2010%' AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%' And tblState.Statename = 'Active')
Order By
  tblAssets.Domain,
  tblAssets.AssetName

Personally, I was more interested in seeing the results by date and software before asset, so I adjusted the ORDER BY accordingly:
Code:
Order By
  [EOS date],
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblAssets.Domain,
  tblAssets.AssetName
nnewton
#7nnewton Member Posts: 10  
posted: 5/10/2019 2:23:39 AM(UTC)
Here's a revised version that will report only active devices for all software, not just Microsoft (the conditional was the incorrect side of the parenthesis).

I've also fixed the IN clauses that used wildcards, pretty sure MS haven't added that feature yet. And cleaned up the Microsoft section at the end.

Code:

SELECT TOP 1000000 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 DATEFROMPARTS(2019,04,30)
                       WHEN tblSoftwareUni.softwareName LIKE 'Coldfusion'
                            AND tblSoftware.softwareVersion LIKE '10.%'
                       THEN DATEFROMPARTS(2019,05,16)
                       WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Correspondence%Management%'
                            AND tblSoftware.softwareVersion LIKE '10.%'
                       THEN DATEFROMPARTS(2019,03,31)
                       WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Designer%ES3%'
                            AND tblSoftware.softwareVersion LIKE '10.%'
                       THEN DATEFROMPARTS(2019,03,31)
                       WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%signature%ES3%'
                            AND tblSoftware.softwareVersion LIKE '10.%'
                       THEN DATEFROMPARTS(2019,03,31)
                       WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Documentum%'
                            AND tblSoftware.softwareVersion LIKE '10.%'
                       THEN DATEFROMPARTS(2019,03,31)
                       WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Content%Manager%'
                            AND tblSoftware.softwareVersion LIKE '10.%'
                       THEN DATEFROMPARTS(2019,03,31)
                       WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%FileNet%'
                            AND tblSoftware.softwareVersion LIKE '10.%'
                       THEN DATEFROMPARTS(2019,03,31)
                       WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%SharePoint%'
                            AND tblSoftware.softwareVersion LIKE '10.%'
                       THEN DATEFROMPARTS(2019,03,31)
                       WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Forms%ES3%'
                            AND tblSoftware.softwareVersion LIKE '10.%'
                       THEN DATEFROMPARTS(2019,03,31)
                       WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Output%ES3%'
                            AND tblSoftware.softwareVersion LIKE '10.%'
                       THEN DATEFROMPARTS(2019,03,31)
                       WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Process%Management%ES3%'
                            AND tblSoftware.softwareVersion LIKE '10.%'
                       THEN DATEFROMPARTS(2019,03,31)
                       WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Reader%ES3%'
                            AND tblSoftware.softwareVersion LIKE '10.%'
                       THEN DATEFROMPARTS(2019,03,31)
                       WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Rights%ES3%'
                            AND tblSoftware.softwareVersion LIKE '10.%'
                       THEN DATEFROMPARTS(2019,03,31)
                       WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Workbench%ES3%'
                            AND tblSoftware.softwareVersion LIKE '10.%'
                       THEN DATEFROMPARTS(2019,03,31)
                       WHEN tblSoftwareUni.softwareName LIKE '%Apache%Struts%'
                            AND tblSoftware.softwareVersion LIKE '2.3.%'
                       THEN DATEFROMPARTS(2019,05,14)
                       WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
                            AND tblSoftware.softwareVersion LIKE '4.13'
                       THEN DATEFROMPARTS(2019,01,20)
                       WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
                            AND tblSoftware.softwareVersion LIKE '4.14'
                       THEN DATEFROMPARTS(2019,02,21)
                       WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
                            AND tblSoftware.softwareVersion LIKE '5.0'
                       THEN DATEFROMPARTS(2019,05,02)
                       WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
                            AND tblSoftware.softwareVersion LIKE '5.1'
                       THEN DATEFROMPARTS(2019,06,06)
                       WHEN tblSoftwareUni.softwareName LIKE '%Confluence%'
                            AND tblSoftware.softwareVersion LIKE '6.1'
                       THEN DATEFROMPARTS(2019,03,20)
                       WHEN tblSoftwareUni.softwareName LIKE '%Confluence%'
                            AND tblSoftware.softwareVersion LIKE '6.2'
                       THEN DATEFROMPARTS(2019,05,15)
                       WHEN tblSoftwareUni.softwareName LIKE '%Jira%Core%'
                            AND tblSoftware.softwareVersion LIKE '7.3'
                       THEN DATEFROMPARTS(2019,01,03)
                       WHEN tblSoftwareUni.softwareName LIKE '%Jira%Core%'
                            AND tblSoftware.softwareVersion LIKE '7.4'
                       THEN DATEFROMPARTS(2019,06,29)
                       WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
                            AND tblSoftware.softwareVersion LIKE '3.3'
                       THEN DATEFROMPARTS(2019,01,03)
                       WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
                            AND tblSoftware.softwareVersion LIKE '3.4'
                       THEN DATEFROMPARTS(2019,03,14)
                       WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
                            AND tblSoftware.softwareVersion LIKE '3.5'
                       THEN DATEFROMPARTS(2019,04,27)
                       WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
                            AND tblSoftware.softwareVersion LIKE '3.6'
                       THEN DATEFROMPARTS(2019,06,29)
                       WHEN tblSoftwareUni.softwareName LIKE '%Jira%Software%'
                            AND tblSoftware.softwareVersion LIKE '7.3'
                       THEN DATEFROMPARTS(2019,01,03)
                       WHEN tblSoftwareUni.softwareName LIKE '%Jira%Software%'
                            AND tblSoftware.softwareVersion LIKE '7.4'
                       THEN DATEFROMPARTS(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 DATEFROMPARTS(2018,11,01)
                       WHEN tblSoftwareUni.softwareName LIKE '%Endpoint%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Checkpoint%'
                            AND tblSoftware.softwareVersion LIKE '%5.6'
                       THEN DATEFROMPARTS(2019,06,01)
                       WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Server%'
                            AND tblSoftware.softwareVersion LIKE '2.8'
                       THEN DATEFROMPARTS(2019,00,20)
                       WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%'
                            AND tblSoftware.softwareVersion LIKE '32.12.%'
                       THEN DATEFROMPARTS(2019,01,11)
                       WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%'
                            AND tblSoftware.softwareVersion LIKE '32.%.%'
                       THEN DATEFROMPARTS(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 DATEFROMPARTS(2019,04,02)
                       WHEN tblSoftwareUni.softwareName LIKE '%Websense%Express%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%'
                            AND tblSoftware.softwareVersion LIKE '1.0.0'
                       THEN DATEFROMPARTS(2019,03,31)
                       WHEN tblSoftwareUni.softwareName LIKE '%Jboss%Web%'
                            AND tblSoftware.softwareVersion LIKE '1.%'
                       THEN DATEFROMPARTS(2020,04,01)
                       WHEN tblSoftwareUni.softwareName LIKE '%Mediawiki%'
                            AND tblSoftware.softwareVersion LIKE '1.27.%'
                       THEN DATEFROMPARTS(2019,06,01)
                       WHEN tblSoftwareUni.softwareName LIKE '%Exchange%2010%'
                       THEN DATEFROMPARTS(2020,01,14)
                       WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
                            AND tblSoftware.softwareVersion LIKE '7.0.7'
                       THEN DATEFROMPARTS(2019,02,25)
                       WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
                            AND tblSoftware.softwareVersion LIKE '7.0.8'
                       THEN DATEFROMPARTS(2019,04,22)
                       WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
                            AND tblSoftware.softwareVersion LIKE '7.0.9'
                       THEN DATEFROMPARTS(2019,06,19)
                       WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
                            AND tblSoftware.softwareVersion LIKE '7.0.10'
                       THEN DATEFROMPARTS(2019,08,19)
                       WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
                            AND tblSoftware.softwareVersion LIKE '5.17.3'
                       THEN DATEFROMPARTS(2019,01,01)
                       WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
                            AND tblSoftware.softwareVersion LIKE '5.17.4'
                       THEN DATEFROMPARTS(2019,04,01)
                       WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
                            AND tblSoftware.softwareVersion LIKE '5.17.5'
                       THEN DATEFROMPARTS(2019,06,01)
                       WHEN tblSoftwareUni.softwareName LIKE '%Connect%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
                            AND tblSoftware.softwareVersion LIKE '3.5'
                       THEN DATEFROMPARTS(2019,05,01)
                       WHEN tblSoftwareUni.softwareName LIKE '%Enterprise%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
                            AND tblSoftware.softwareVersion LIKE '8.5.3'
                       THEN DATEFROMPARTS(2019,03,01)
                       WHEN tblSoftwareUni.softwareName LIKE '%Enterprise%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
                            AND tblSoftware.softwareVersion LIKE '8.5.4'
                       THEN DATEFROMPARTS(2019,06,01)
                       WHEN tblSoftwareUni.softwareName LIKE '%IP360%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
                            AND tblSoftware.softwareVersion LIKE '8.1.1'
                       THEN DATEFROMPARTS(2019,05,01)
                       WHEN tblSoftwareUni.softwareName LIKE '%log%center%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
                            AND tblSoftware.softwareVersion LIKE '7.2.4'
                       THEN DATEFROMPARTS(2019,03,01)
                       WHEN tblSoftwareUni.softwareName LIKE '%log%center%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
                            AND tblSoftware.softwareVersion LIKE '7.2.5'
                       THEN DATEFROMPARTS(2019,06,01)
                       WHEN tblSoftwareUni.softwareName LIKE '%Intelligence%hub%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
                            AND tblSoftware.softwareVersion LIKE '2.7.3'
                       THEN DATEFROMPARTS(2019,03,01)
                       WHEN tblSoftwareUni.softwareName LIKE '%Winzip%'
                            AND tblSoftware.softwareVersion LIKE '20%'
                       THEN DATEFROMPARTS(2019,04,30)
                       WHEN tblSoftwareUni.softwareName LIKE '%Horizon%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%vmware%'
                            AND tblSoftware.softwareVersion LIKE '6.%'
                       THEN DATEFROMPARTS(2019,06,19)
                       WHEN tblSoftwareUni.softwareName LIKE '%Expression%Blend%3%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,01,14)
                       WHEN tblSoftwareUni.softwareName LIKE '%Expression%Design%3%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,01,14)
                       WHEN tblSoftwareUni.softwareName LIKE '%Expression%Encoder%3%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,01,14)
                       WHEN tblSoftwareUni.softwareName LIKE '%Expression%Studio%3%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,01,14)
                       WHEN tblSoftwareUni.softwareName LIKE '%Expression%Web%3%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,01,14)
                       WHEN tblSoftwareUni.softwareName LIKE '%Forefront%Unified%Access%2010%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,04,14)
                       WHEN tblSoftwareUni.softwareName LIKE '%Forefront%Threat%management%2010%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,04,14)
                       WHEN tblSoftwareUni.softwareName LIKE '%Visual%Studio%2010%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,07,14)
                       WHEN tblSoftwareUni.softwareName LIKE '%Expression%Encoder%4%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,09,08)
                       WHEN tblSoftwareUni.softwareName LIKE '%Expression%Web%4%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,09,08)
                       WHEN tblSoftwareUni.softwareName LIKE '%Acess%2010%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,10,13)
                       WHEN tblSoftwareUni.softwareName LIKE '%Dynamics%2010%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,10,13)
                       WHEN tblSoftwareUni.softwareName LIKE '%Excel%2010%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,10,13)
                       WHEN tblSoftwareUni.softwareName LIKE '%Expression%Studio%4%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,10,13)
                       WHEN tblSoftwareUni.softwareName LIKE '%Infopath%2010%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,10,13)
                       WHEN tblSoftwareUni.softwareName LIKE '%Office%2010%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,10,13)
                       WHEN tblSoftwareUni.softwareName LIKE '%Onenote%2010%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,10,13)
                       WHEN tblSoftwareUni.softwareName LIKE '%Outlook%2010%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,10,13)
                       WHEN tblSoftwareUni.softwareName LIKE '%powerpoint%2010%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,10,13)
                       WHEN tblSoftwareUni.softwareName LIKE '%project%2010%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,10,13)
                       WHEN tblSoftwareUni.softwareName LIKE '%publisher%2010%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,10,13)
                       WHEN tblSoftwareUni.softwareName LIKE '%sharepoint%2010%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,10,13)
                       WHEN tblSoftwareUni.softwareName LIKE '%visio%2010%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,10,13)
                       WHEN tblSoftwareUni.softwareName LIKE '%visual%basic%2010%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(2020,10,13)
                       WHEN tblSoftwareUni.softwareName LIKE '%word%2010%'
                            AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
                       THEN DATEFROMPARTS(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;
Centralis IT - E
#8Centralis IT - E Member Posts: 2  
posted: 5/10/2019 8:11:43 AM(UTC)
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
Esben.D
#9Esben.D Member Administration Original PosterPosts: 1,834  
posted: 5/10/2019 9:01:59 AM(UTC)
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.

Originally Posted by: Centralis IT - E Go to Quoted Post
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.
Josi
#10Josi Member Posts: 1  
posted: 5/10/2019 9:47:39 AM(UTC)
What about Microsoft SQL server expiring in a few months? I cannot see it in the list.
Here is the reference:
https://blogs.msdn.micro...-and-sql-server-2008-r2/
Centralis IT - E
#11Centralis IT - E Member Posts: 2  
posted: 5/10/2019 10:25:16 AM(UTC)
Originally Posted by: Esben.D Go to Quoted Post
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.

Originally Posted by: Centralis IT - E Go to Quoted Post
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?
Esben.D
#12Esben.D Member Administration Original PosterPosts: 1,834  
posted: 5/10/2019 2:25:40 PM(UTC)
It's the DATEFROMPARTS specifically that is not supported. I'll take a look next week to see if I can find a workaround.
nnewton
#13nnewton Member Posts: 10  
posted: 5/13/2019 1:58:39 AM(UTC)
CONVERT should work in Compact shouldn't it?

Code:

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
#14Esben.D Member Administration Original PosterPosts: 1,834  
posted: 5/13/2019 9:04:04 AM(UTC)
Originally Posted by: nnewton Go to Quoted Post
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.
Code:
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
rasldasl
#15rasldasl Member Posts: 13  
posted: 5/13/2019 11:31:38 PM(UTC)
I'm picking up a lot of Microsoft Visual Studio 2010 Tools for Office Runtime. Are runtimes like this (and Access runtime) really affected?

Active Discussions

Lansweeper Asset Value Report
by  RC62N   Go to last post Go to first unread
Last post: 9/20/2019 7:12:29 PM(UTC)
Lansweeper Display Hyper-V Guest User report
by  GlenTB  
Go to last post Go to first unread
Last post: 9/20/2019 2:26:15 PM(UTC)
Report Center Windows Defender Antivirus Broken Scan Audit
by  Esben.D   Go to last post Go to first unread
Last post: 9/20/2019 12:18:02 PM(UTC)
Lansweeper Reports are empty
by  Mendoza  
Go to last post Go to first unread
Last post: 9/20/2019 11:12:18 AM(UTC)
Lansweeper Custom Helpdesk Report
by  StevoCamaro   Go to last post Go to first unread
Last post: 9/19/2019 11:13:05 PM(UTC)
Lansweeper Windows 7 EOL
by  RC62N  
Go to last post Go to first unread
Last post: 9/19/2019 4:42:11 PM(UTC)
Lansweeper Drive Encryption statuses
by  DFox   Go to last post Go to first unread
Last post: 9/19/2019 12:54:06 PM(UTC)
Lansweeper Patch Tuesday report, last 3 months
by  Esben.D  
Go to last post Go to first unread
Last post: 9/19/2019 10:55:07 AM(UTC)