Report specific versions of a Software with his version field empty

[servicedesk]

I want to remove a specific versions of a software that has their version embed in the "software name" field, and their "version" field is empty. Due to that, I created two "after scanning" configurations to uninstall the same software.

In the first report is set Like 'SOFTWARE_NAME %._' and to other Like 'SOFTWARE_NAME%Home Edition'

I would like to just have one report, how I should update it?

*And also.. I need to see only the ones with more than 7 days after install date.

Select Top 1000000 tblAssets.AssetID,
tblAssets.IPAddress,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Username,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tblSoftwareUni.SoftwarePublisher As publisher,
tsysOS.Image As icon,
tblADComputers.OU
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblSoftwareUni.softwareName Like 'SOFTWARE_NAME %._' And
tblADComputers.OU Like '%COUNTRY%' And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
software,
version

Many thanks

[servicedesk]

perfect

[Daniel_B · Accepted solution]

With the details you gave, the following report should make it:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.IPAddress,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Username,
  tblSoftwareUni.softwareName As software,
  tblSoftware.softwareVersion As version,
  tblSoftwareUni.SoftwarePublisher As publisher,
  tsysOS.Image As icon,
  tblADComputers.OU
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where (tblSoftwareUni.softwareName Like 'iTunes 1.2.9%' Or
    tblSoftwareUni.softwareName Like 'iTunes 1.3 Home Edition%') And
  tblADComputers.OU Like '%COUNTRY%' And tblSoftware.Installdate < GetDate() - 7
  And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  software,
  version

[servicedesk]

Actually gives me "No results"...

Just to check if I explain it well, I need to list computers with "iTunes 1.2.9" and "iTunes 1.3 Home Edition"

In that case I will filter using 'iTunes *._' and 'iTunes%Home Edition' so If there's an "iTunes 1.3 Pro" will be not listed.

I accept suggestions!

Thx

[Daniel_B]

You can add several filters for tblSoftwareUni.softwareName. The install date can be filtered through tblSoftware.Installdate:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.IPAddress,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Username,
  tblSoftwareUni.softwareName As software,
  tblSoftware.softwareVersion As version,
  tblSoftwareUni.SoftwarePublisher As publisher,
  tsysOS.Image As icon,
  tblADComputers.OU
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblSoftwareUni.softwareName Like 'SOFTWARE_NAME %._' And
  tblSoftwareUni.softwareName Like '%home edition' And
  tblADComputers.OU Like '%COUNTRY%' And tblSoftware.Installdate < GetDate() - 7
  And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  software,
  version