→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
servicedesk
Champion Sweeper II
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
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

4 REPLIES 4
servicedesk
Champion Sweeper II
perfect
Daniel_B
Lansweeper Alumni
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
Champion Sweeper II
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
Lansweeper Alumni
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