→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
teejayuu
Engaged Sweeper
Hi

I'm looking to create a report that lists all machines that don't have Adobe Flash installed. I've managed to create one that lists those that do, but can't seem to modify it to list those that don't. I don't have much experience of writing database reports and have tried to use Not Like as a criteria, but this just shows me every other piece of software installed.

Thanks
Tony
Tony In view of the fact that God limited the intelligence of man, it seems unfair that He did not also limit his stupidity. Konrad Adenauer
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
Please use the query below.
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%YourSoftware%') And
tblAssets.Lastseen <> '' And tblAssets.Assettype = -1
Order By tblAssets.AssetUnique

View solution in original post

7 REPLIES 7
brandon_jones
Champion Sweeper III
I tried it and it worked. Are you getting an error or unexpected results?
kbecker
Engaged Sweeper II
any idea why this doesn't work ?
abustraan
Engaged Sweeper III
That looks great, except when I run it, it includes all non-active computers. How do I filter the non-active computers out? I was able to modify the code to filter out the non-active computers:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tblAssets.Lastseen,
tblAssetCustom.State
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%<YourSoftwareHere>%') And
tblAssets.Lastseen <> '' And tblAssetCustom.State = 1 And
tblAssets.Assettype = -1
Order By tblAssets.AssetUnique
friedbad
Engaged Sweeper II
Looks good - Thanks!!
Hemoco
Lansweeper Alumni
Please use the query below.
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%YourSoftware%') And
tblAssets.Lastseen <> '' And tblAssets.Assettype = -1
Order By tblAssets.AssetUnique
friedbad
Engaged Sweeper II
Any chance to get the revisions for V5 for this same report? I'm having a hard time getting this.
Hemoco
Lansweeper Alumni
Try this

Select tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain
From tblComputers
Where tblComputers.Computername Not In (Select tblsoftware.ComputerName
From tblsoftware Where tblsoftware.softwareName Like 'adobe flash%')