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: 425  
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 Report on clients with no LSAgent
by  RobertB   Go to last post Go to first unread
Last post: Yesterday at 6:55:32 PM(UTC)
Lansweeper Lansweeper report to match computer name
by  RC62N   Go to last post Go to first unread
Last post: 2/19/2020 5:02:27 PM(UTC)
Lansweeper Distinct user logins per PC
by  Bruce Garoutte   Go to last post Go to first unread
Last post: 2/17/2020 6:36:29 PM(UTC)
Lansweeper Software version report issue
by  RC62N  
Go to last post Go to first unread
Last post: 2/17/2020 4:07:20 PM(UTC)
Lansweeper Patch Tuesday - Exclude Win 2008 & Win7
by  doone128   Go to last post Go to first unread
Last post: 2/17/2020 1:34:36 PM(UTC)
Lansweeper Performance statistics combined into one report
by  djs1789  
Go to last post Go to first unread
Last post: 2/15/2020 1:50:32 AM(UTC)