Notification

Icon
Error

Need Help Editing a Report

Posted: Friday, April 9, 2021 5:04:37 PM(UTC)
Tyler M.

Tyler M.

Member Original PosterPosts: 25
1
Like
This issue has been solved! Click here to view the solution
Can somebody please help me edit this report so that instead of listing software as up to date it will just exclude it in the report? I have a report that has over 1000+ assets listed for outdated software but in reality only 40 of them are actually out of date.



Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  Case
    When tblSoftware.softwareVersion Like '14.2.4814.1101' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftware.Lastchanged,
  Case
    When tblSoftware.softwareVersion Like '14.2.4814.1101' Then '#d4f4be'
    Else '#ffadad'
  End As backgroundcolor
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
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like 'Symantec Endpoint Protection' And
  tblState.Statename = 'Active'
Order By Version,
  [Patch Status],
  tblAssets.Lastseen Desc,
  tblAssets.AssetName
Brandon
#1Brandon Member Posts: 136  
posted: 4/9/2021 6:01:18 PM(UTC)
Try this and see if it gives you the information you are looking for.

Quote:
Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tblassets.Username,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
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
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblassets.OScode
Where tblSoftwareUni.softwareName Like 'Symantec Endpoint Protection' And
tblSoftware.softwareVersion Not Like '14.2.4814.1101' And tblState.Statename =
'Active'
Order By Version,
tblassets.Lastseen Desc,
tblassets.AssetName



Originally Posted by: Tyler M. Go to Quoted Post
Can somebody please help me edit this report so that instead of listing software as up to date it will just exclude it in the report? I have a report that has over 1000+ assets listed for outdated software but in reality only 40 of them are actually out of date.



Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  Case
    When tblSoftware.softwareVersion Like '14.2.4814.1101' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftware.Lastchanged,
  Case
    When tblSoftware.softwareVersion Like '14.2.4814.1101' Then '#d4f4be'
    Else '#ffadad'
  End As backgroundcolor
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
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like 'Symantec Endpoint Protection' And
  tblState.Statename = 'Active'
Order By Version,
  [Patch Status],
  tblAssets.Lastseen Desc,
  tblAssets.AssetName


Tyler M.
#2Tyler M. Member Original PosterPosts: 25  
posted: 4/9/2021 6:26:37 PM(UTC)
Originally Posted by: Brandon Go to Quoted Post
Try this and see if it gives you the information you are looking for.

Quote:
Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tblassets.Username,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
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
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblassets.OScode
Where tblSoftwareUni.softwareName Like 'Symantec Endpoint Protection' And
tblSoftware.softwareVersion Not Like '14.2.4814.1101' And tblState.Statename =
'Active'
Order By Version,
tblassets.Lastseen Desc,
tblassets.AssetName



Originally Posted by: Tyler M. Go to Quoted Post
Can somebody please help me edit this report so that instead of listing software as up to date it will just exclude it in the report? I have a report that has over 1000+ assets listed for outdated software but in reality only 40 of them are actually out of date.



Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  Case
    When tblSoftware.softwareVersion Like '14.2.4814.1101' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftware.Lastchanged,
  Case
    When tblSoftware.softwareVersion Like '14.2.4814.1101' Then '#d4f4be'
    Else '#ffadad'
  End As backgroundcolor
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
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like 'Symantec Endpoint Protection' And
  tblState.Statename = 'Active'
Order By Version,
  [Patch Status],
  tblAssets.Lastseen Desc,
  tblAssets.AssetName




That is perfect! Thanks for the help!

Active Discussions

Lansweeper Automatic Follow-Up for Tickets
by  Francis Lee Mondia - Endace   Go to last post Go to first unread
Last post: Yesterday at 11:06:51 PM(UTC)
Lansweeper Can't see devices on Lansweeper
by  vqT4cDoP9iXyMZwoDUWU  
Go to last post Go to first unread
Last post: Yesterday at 8:33:21 PM(UTC)
Lansweeper LAPS managed password
by  SystemAdmin   Go to last post Go to first unread
Last post: Yesterday at 6:08:42 PM(UTC)
Lansweeper Install Adobe Reader Error
by  Brandon  
Go to last post Go to first unread
Last post: Yesterday at 2:46:54 PM(UTC)
Lansweeper Stop deployment
by  Tyler M.   Go to last post Go to first unread
Last post: Yesterday at 1:59:22 PM(UTC)
Lansweeper "Out of memory" in errorlog.txt
by  dua  
Go to last post Go to first unread
Last post: Yesterday at 1:00:42 PM(UTC)
Lansweeper Installation issues
by  Troy   Go to last post Go to first unread
Last post: Yesterday at 9:24:54 AM(UTC)
Lansweeper End of Life Asset Report
by  DJ Bradley  
Go to last post Go to first unread
Last post: 5/11/2021 4:09:33 PM(UTC)