Modify Report to Exclude Certain Publishers

[andrewlovesyou]

Hi, everyone! 

I currently have a LanSweeper report that reports all software changes in the last seven days. I want to exclude certain publishers that we approve of, such as Microsoft, Dell, HP, etc. 

With my current report, it shows all publishers and I manually filter them out in an excel file. How can I modify my report to exclude certain publishers? My report is below, thank you so much for any assistance! 

Select Top (1000000) tsysOS.Image As Icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  Case tblSoftwareHist.Action
    When '1' Then '+'
    When '2' Then '-'
  End As [+/-],
  tblSoftwareUni.softwareName As Software,
  tblSoftwareHist.softwareVersion As Version,
  (Select Case tblSoftware.MsStore
        When 0 Then 'Desktop app'
        Else 'Microsoft Store app'
      End) As 'Type',
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblSoftwareHist.Installdate,
  tblSoftwareHist.Lastchanged,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan],
  indirect.lastIndirectScan As [Last indirect scan]
From tblAssets
  Inner Join tblSoftwareHist On tblAssets.AssetID = tblSoftwareHist.AssetID
  Inner Join tblSoftwareUni On tblSoftwareHist.softid = tblSoftwareUni.SoftID
  Inner Join tblSoftware On tblSoftwareHist.softid = tblSoftware.softID And
      tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Left Join (Select Max(tblIndirectScan.LastChanged) As lastIndirectScan,
      tblIndirectScan.AssetId
    From tblIndirectScan
    Group By tblIndirectScan.AssetId) indirect On tblAssets.AssetID =
      indirect.AssetId
Where tblSoftwareHist.Lastchanged > GetDate() - 7 And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  tblSoftwareHist.Lastchanged Desc,
  Software

[Obi_1_Cinobi · Accepted solution]

Hello there!

You could use where clause to exclude certain publishers. This is an example:

WHERE tblSoftwareUni.SoftwarePublisher NOT IN (
    'Adobe',
    'Microsoft Corporation',
    'Google LLC'
)

[andrewlovesyou]

Thank you so much! I am still learning these reports and this worked great! Have a wonderful day. 🙂