cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Tholmes
Engaged Sweeper III
Hi all,
I have managed to successfully use other peoples queries and generate a lot of really useful data, however, here is where the challenge arises for me.
They want multiple versions of Revit(13)Autodesk Material Libraries (9)and 4 versions of AutoCAD 2011 checked.
How can I create a query for 3 different apps, all versions, in one query.
I am a little unsure what to look for, as everyone says to use the exact name as it appears in Lansweeper, and when I did, it did not work, i ahve tried and the code is beneath, but not getting the version number.
Any guidance would be greatly received.
Regards

Here is the code I got from the beneath link, give credit where it's due.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tsysOS.OSname,
tblAssets.SP,
tblAssets.IPAddress,
SoftwareCheck.softwareName,
SoftwareCheck.softwareVersion,
SoftwareCheck.SoftwarePublisher
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Autocad 2011%' And
tblSoftwareUni.softwareName Like '%Autodesk Material LIbraries 2011%'
And tblSoftwareUni.softwareName Like '%Autodesk Revit 2013%') As
SoftwareCheck On SoftwareCheck.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

https://www.lansweeper.com/forum/yaf_postst6665_Simple-query-to-show-all-my-computers-with-or-without-xxx-applications.aspx#post28696
1 REPLY 1
RCorbeil
Honored Sweeper II
Take a look at what you're requesting when you process the list of software:
  Left Join (Select
tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.softwareVersion
From
tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
tblSoftwareUni.softwareName Like 'Autocad 2011%'
And tblSoftwareUni.softwareName Like '%Autodesk Material LIbraries 2011%'
And tblSoftwareUni.softwareName Like '%Autodesk Revit 2013%') As SoftwareCheck ...

Remember, the query is scanning the software entries one by one.

As an analogy, consider that you've been given a big bag of gumballs and you've been asked to pull out the white, red and green ones, but none of the others. You pull the gumballs out one by one and, in the logic you've listed above, ask yourself:
  • is this gumball white?
  • AND is this gumball also red?
  • AND is this gumball also green?
If you answer yes to all three questions, then the gumball is selected. You then select the next gumball and repeat the process until the entire bag of gumballs has been processed.

AND means that all conditions must be true: the gumball must be simultaneously white, red, and green in order to be selected.

OR means that any one (or more) of the conditions must be true: the gumball must be ANY ONE OF white, red, or green.

Try changing the software evaluations from AND to OR.
  Left Join (Select
tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.softwareVersion
From
tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
tblSoftwareUni.softwareName Like 'Autocad 2011%'
OR tblSoftwareUni.softwareName Like '%Autodesk Material LIbraries 2011%'
OR tblSoftwareUni.softwareName Like '%Autodesk Revit 2013%') As SoftwareCheck ...

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now