Notification

Icon
Error

Autocad query expansion help please - Autocad, revit and libraries

Posted: Wednesday, December 18, 2019 12:02:34 AM(UTC)
TimHolmes1973

TimHolmes1973

Member Original PosterPosts: 2
0
Like
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.c...lications.aspx#post28696
RC62N
#1RC62N Member Posts: 430  
posted: 1/16/2020 5:52:37 PM(UTC)
Take a look at what you're requesting when you process the list of software:
Code:
  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.
Code:
  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 ...

Active Discussions

Lansweeper Find machines missing any 1 of several apps
by  BennettL   Go to last post Go to first unread
Last post: Yesterday at 8:35:28 PM(UTC)
Lansweeper Max Software Version and Deploy to Only Outdated
by  GenDev   Go to last post Go to first unread
Last post: Yesterday at 1:06:50 PM(UTC)
Lansweeper Patch Tuesday - Superseded MS Updates
by  doone128  
Go to last post Go to first unread
Last post: Yesterday at 11:27:10 AM(UTC)
Lansweeper Report on basic Router information
by  Liljack  
Go to last post Go to first unread
Last post: 3/30/2020 7:44:22 AM(UTC)
Lansweeper Windows: Static IP address configured (Built-in)
by  RobinPSU   Go to last post Go to first unread
Last post: 3/27/2020 8:52:12 PM(UTC)
Lansweeper Adding top user to asset report
by  Argon0  
Go to last post Go to first unread
Last post: 3/26/2020 5:20:51 PM(UTC)