This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
- Lansweeper Community
- Forums
- Reports & Analytics
- End of Support Software Report
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Register to ask a question, start a topic or share an idea
Join the Community
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-07-2019 03:35 PM
Based on the following lists of EOS software, I've created a report which can be used to identify whether you've got software in your network which is close or is no longer supported. The EOS date is also shown after each software found. For more information, check out the blog post here.
Due to the size and coverage of this report, you will notice some performance impact on the report builder. This impact will only last while the report is being compiled or processed.
Due to the size and coverage of this report, you will notice some performance impact on the report builder. This impact will only last while the report is being compiled or processed.
- https://www.cisecurity.org/wp-content/uploads/2019/03/EOS-Table.pdf
- https://support.microsoft.com/en-us/help/4470235/products-reaching-end-of-support-for-2020
SELECT tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
COALESCE(tsysOS.IMAGE,tsysAssetTypes.AssetTypeIcon10) AS icon,
tblAssets.IPAddress,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.softwareVersion AS Version,
CASE
WHEN tblSoftwareUni.softwareName LIKE 'Coldfusion'
AND tblSoftware.softwareVersion LIKE '11.%'
THEN cast('2019-04-30' as datetime)
WHEN tblSoftwareUni.softwareName LIKE 'Coldfusion'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-05-16' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Correspondence%Management%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Designer%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%signature%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Documentum%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Content%Manager%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%FileNet%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%SharePoint%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Forms%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Output%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Process%Management%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Reader%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Rights%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%LiveCycle%Workbench%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Apache%Struts%'
AND tblSoftware.softwareVersion LIKE '2.3.%'
THEN cast('2019-05-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion LIKE '4.13'
THEN cast('2019-01-20' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion LIKE '4.14'
THEN cast('2019-02-21' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion LIKE '5.0'
THEN cast('2019-05-02' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion LIKE '5.1'
THEN cast('2019-06-06' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Confluence%'
AND tblSoftware.softwareVersion LIKE '6.1'
THEN cast('2019-03-20' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Confluence%'
AND tblSoftware.softwareVersion LIKE '6.2'
THEN cast('2019-05-15' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Core%'
AND tblSoftware.softwareVersion LIKE '7.3'
THEN cast('2019-01-03' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Core%'
AND tblSoftware.softwareVersion LIKE '7.4'
THEN cast('2019-06-29' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion LIKE '3.3'
THEN cast('2019-01-03' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion LIKE '3.4'
THEN cast('2019-03-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion LIKE '3.5'
THEN cast('2019-04-27' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion LIKE '3.6'
THEN cast('2019-06-29' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Software%'
AND tblSoftware.softwareVersion LIKE '7.3'
THEN cast('2019-01-03' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jira%Software%'
AND tblSoftware.softwareVersion LIKE '7.4'
THEN cast('2019-06-29' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Endpoint%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Checkpoint%'
AND (tblSoftware.softwareVersion LIKE '%80.61'
OR tblSoftware.softwareVersion LIKE '%80.63'
OR tblSoftware.softwareVersion LIKE '%80.65'
OR tblSoftware.softwareVersion LIKE '%80.66'
OR tblSoftware.softwareVersion LIKE '%80.67'
OR tblSoftware.softwareVersion LIKE '%80.68'
OR tblSoftware.softwareVersion LIKE '%80.69'
OR tblSoftware.softwareVersion LIKE '%5.6')
THEN cast('2018-11-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Endpoint%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Checkpoint%'
AND tblSoftware.softwareVersion LIKE '%5.6'
THEN cast('2019-06-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Server%'
AND tblSoftware.softwareVersion LIKE '2.8'
THEN cast('2019-00-20' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%'
AND tblSoftware.softwareVersion LIKE '32.12.%'
THEN cast('2019-01-11' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%'
AND tblSoftware.softwareVersion LIKE '32.%.%'
THEN cast('2019-05-24' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Websense%URL%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%'
AND tblSoftware.softwareVersion IN('7.0','7.1','7.5')
THEN cast('2019-04-02' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Websense%Express%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%'
AND tblSoftware.softwareVersion LIKE '1.0.0'
THEN cast('2019-03-31' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Jboss%Web%'
AND tblSoftware.softwareVersion LIKE '1.%'
THEN cast('2020-04-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Mediawiki%'
AND tblSoftware.softwareVersion LIKE '1.27.%'
THEN cast('2019-06-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Exchange%2010%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion LIKE '7.0.7'
THEN cast('2019-02-25' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion LIKE '7.0.8'
THEN cast('2019-04-22' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion LIKE '7.0.9'
THEN cast('2019-06-19' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion LIKE '7.0.10'
THEN cast('2019-08-19' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '5.17.3'
THEN cast('2019-01-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '5.17.4'
THEN cast('2019-04-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '5.17.5'
THEN cast('2019-06-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Connect%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '3.5'
THEN cast('2019-05-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Enterprise%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '8.5.3'
THEN cast('2019-03-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Enterprise%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '8.5.4'
THEN cast('2019-06-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%IP360%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '8.1.1'
THEN cast('2019-05-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%log%center%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '7.2.4'
THEN cast('2019-03-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%log%center%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '7.2.5'
THEN cast('2019-06-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Intelligence%hub%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '2.7.3'
THEN cast('2019-03-01' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Winzip%'
AND tblSoftware.softwareVersion LIKE '20%'
THEN cast('2019-04-30' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Horizon%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%vmware%'
AND tblSoftware.softwareVersion LIKE '6.%'
THEN cast('2019-06-19' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Blend%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Design%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Encoder%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Studio%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Web%3%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-01-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Forefront%Unified%Access%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-04-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Forefront%Threat%management%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-04-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Visual%Studio%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-07-14' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Encoder%4%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-09-08' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Web%4%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-09-08' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Acess%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Dynamics%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Excel%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Expression%Studio%4%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Infopath%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Office%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Onenote%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%Outlook%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%powerpoint%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%project%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%publisher%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%sharepoint%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%visio%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%visual%basic%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
WHEN tblSoftwareUni.softwareName LIKE '%word%2010%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
THEN cast('2020-10-13' as datetime)
END AS [EOS date],
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname AS OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftware.Lastchanged
FROM tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tsysIPLocations ON tsysIPLocations.LocationID = tblAssets.LocationID
INNER JOIN tblState ON tblState.STATE = tblAssetCustom.STATE
INNER JOIN tblSoftware ON tblAssets.AssetID = tblSoftware.AssetID
LEFT JOIN tsysOS ON tsysOS.OScode = tblAssets.OScode
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftwareUni.softwareName LIKE 'Coldfusion'
AND (tblSoftware.softwareVersion LIKE '11.%'
OR tblSoftware.softwareVersion LIKE '10.%')
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Correspondence%Management%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Designer%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%signature%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Documentum%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%Content%Manager%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%FileNet%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Connector%ES3%SharePoint%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Forms%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Output%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Process%Management%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Reader%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Rights%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%LiveCycle%Workbench%ES3%'
AND tblSoftware.softwareVersion LIKE '10.%'
OR tblSoftwareUni.softwareName LIKE '%Apache%Struts%'
AND tblSoftware.softwareVersion LIKE '2.3.%'
OR tblSoftwareUni.softwareName LIKE '%Bitbucket%'
AND tblSoftware.softwareVersion IN('4.13','4.14','5.0','5.1')
OR tblSoftwareUni.softwareName LIKE '%Confluence%'
AND tblSoftware.softwareVersion IN('6.1','6.2')
OR tblSoftwareUni.softwareName LIKE '%Jira%Core%'
AND tblSoftware.softwareVersion IN('7.3','7.4')
OR tblSoftwareUni.softwareName LIKE '%Jira%Service%'
AND tblSoftware.softwareVersion IN('3.3','3.4','3.5','3.6')
OR tblSoftwareUni.softwareName LIKE '%Jira%Software%'
AND tblSoftware.softwareVersion IN('7.3','7.4')
OR tblSoftwareUni.softwareName LIKE '%Endpoint%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Checkpoint%'
AND (tblSoftware.softwareVersion LIKE '%80.61'
OR tblSoftware.softwareVersion LIKE '%80.63'
OR tblSoftware.softwareVersion LIKE '%80.65'
OR tblSoftware.softwareVersion LIKE '%80.66'
OR tblSoftware.softwareVersion LIKE '%80.67'
OR tblSoftware.softwareVersion LIKE '%80.68'
OR tblSoftware.softwareVersion LIKE '%80.69'
OR tblSoftware.softwareVersion LIKE '%5.6')
OR tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Server%'
AND tblSoftware.softwareVersion LIKE '2.8'
OR tblSoftwareUni.softwareName LIKE '%WebEx%Meetings%Suite%'
AND (tblSoftware.softwareVersion LIKE '32.12.%'
OR tblSoftware.softwareVersion LIKE '32.%.%')
OR tblSoftwareUni.softwareName LIKE '%Websense%URL%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%'
AND tblSoftware.softwareVersion IN('7.0','7.1','7.5')
OR tblSoftwareUni.softwareName LIKE '%Websense%Express%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Forcepoint%'
AND tblSoftware.softwareVersion LIKE '1.0.0'
OR tblSoftwareUni.softwareName LIKE '%Jboss%Web%'
AND tblSoftware.softwareVersion LIKE '1.%'
OR tblSoftwareUni.softwareName LIKE '%Mediawiki%'
AND tblSoftware.softwareVersion LIKE '1.27.%'
OR tblSoftwareUni.softwareName LIKE '%Exchange%2010%'
OR tblSoftwareUni.softwareName LIKE '%syslog-ng%'
AND tblSoftware.softwareVersion IN('7.0.7','7.0.8','7.0.9','7.0.10')
OR tblSoftwareUni.softwareName LIKE '%Compliance%manager%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion IN('5.17.3','5.17.4','5.17.5')
OR tblSoftwareUni.softwareName LIKE '%Connect%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '3.5'
OR tblSoftwareUni.softwareName LIKE '%Enterprise%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion IN('8.5.3','8.5.4')
OR tblSoftwareUni.softwareName LIKE '%IP360%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '8.1.1'
OR tblSoftwareUni.softwareName LIKE '%log%center%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion IN('7.2.4','7.2.5')
OR tblSoftwareUni.softwareName LIKE '%Intelligence%hub%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%Tripwire%'
AND tblSoftware.softwareVersion LIKE '2.7.3'
OR tblSoftwareUni.softwareName LIKE '%Winzip%'
AND tblSoftware.softwareVersion LIKE '20%'
OR tblSoftwareUni.softwareName LIKE '%Horizon%'
AND tblSoftwareUni.SoftwarePublisher LIKE '%vmware%'
AND tblSoftware.softwareVersion LIKE '6.%'
OR tblSoftwareUni.SoftwarePublisher LIKE '%Microsoft%'
AND (tblSoftwareUni.softwareName LIKE '%Expression%Blend%3%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Design%3%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Encoder%3%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Studio%3%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Web%3%'
OR tblSoftwareUni.softwareName LIKE '%Forefront%Unified%Access%2010%'
OR tblSoftwareUni.softwareName LIKE '%Forefront%Threat%management%2010%'
OR tblSoftwareUni.softwareName LIKE '%Visual%Studio%2010%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Encoder%4%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Web%4%'
OR tblSoftwareUni.softwareName LIKE '%Acess%2010%'
OR tblSoftwareUni.softwareName LIKE '%Dynamics%2010%'
OR tblSoftwareUni.softwareName LIKE '%Excel%2010%'
OR tblSoftwareUni.softwareName LIKE '%Expression%Studio%4%'
OR tblSoftwareUni.softwareName LIKE '%Infopath%2010%'
OR tblSoftwareUni.softwareName LIKE '%Office%2010%'
OR tblSoftwareUni.softwareName LIKE '%Onenote%2010%'
OR tblSoftwareUni.softwareName LIKE '%Outlook%2010%'
OR tblSoftwareUni.softwareName LIKE '%powerpoint%2010%'
OR tblSoftwareUni.softwareName LIKE '%project%2010%'
OR tblSoftwareUni.softwareName LIKE '%publisher%2010%'
OR tblSoftwareUni.softwareName LIKE '%sharepoint%2010%'
OR tblSoftwareUni.softwareName LIKE '%visio%2010%'
OR tblSoftwareUni.softwareName LIKE '%visual%basic%2010%'
OR tblSoftwareUni.softwareName LIKE '%word%2010%')
AND tblState.Statename = 'Active'
ORDER BY tblAssets.Domain,
tblAssets.AssetName
Labels:
- Labels:
-
Finished Reports
-
Report Center
16 REPLIES 16
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-10-2019 03:23 AM
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.
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.
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;
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-09-2019 05:25 PM
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.
We added it in, and let it run, slowed down every single aspect of LS. Removed the report, everything is back to normal.
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-09-2019 05:54 PM
francisswest wrote:
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.
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-09-2019 05:57 PM
SAHarrington wrote:francisswest wrote:
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.
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-09-2019 05:09 PM
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.
For the benefit of those who prefer ISO date formatting, I've modified that, including kevinrojones's catch of the 20200 typo.
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
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-09-2019 06:41 PM
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.
Personally, I was more interested in seeing the results by date and software before asset, so I adjusted the ORDER BY accordingly:
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:
Order By
[EOS date],
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblAssets.Domain,
tblAssets.AssetName
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-09-2019 04:22 PM
Great Report!.. There is a typo.. just an extra zero:
Line 157 tblSoftwareUni.SoftwarePublisher Like '%Microsoft%' Then '7/14/20200'
Line 157 tblSoftwareUni.SoftwarePublisher Like '%Microsoft%' Then '7/14/20200'
New to Lansweeper?
Try Lansweeper For Free
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now
Related Content
- Report to Show PC's active in last 30 days as well as software installed on them in Reports & Analytics
- report for all software on 2 OUs only in General Discussions
- Report for New software installs - modifying the Software Change report in Reports & Analytics
- Inside Lansweeper: Weekly Recap in General Discussions
- Introducing SIM - Software Inventory Manager - Developed by Licenseware & Powered by Lansweeper in General Discussions
