Notification

Icon
Error

Trying to display different versions of software - I have the report to say if the software is there, but I woud like it to say the software version

Posted: Wednesday, October 28, 2020 6:20:43 PM(UTC)
TimHolmes1973

TimHolmes1973

Member Original PosterPosts: 20
0
Like
This issue has been solved! Click here to view the solution
Here is my code so far, I copied it and thought I could just add to it to ask for columns at the top, software name and version

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%OneDrive' And
tblSoftware.softwareVersion Not Like '19.232%') And
tblAssets.Lastseen <> '' And tblAssets.Assettype = -1
Order By tblAssets.AssetUnique

As always, any and all help greatly appreciated.
I am starting on basic SQL courses, the free ones seem good, any suggestions for report writing courses please?
Thanks in advance
Tim
RC62N
#1RC62N Member Posts: 488  
posted: 10/28/2020 8:32:51 PM(UTC)
To display fields from the software tables you need to select those tables. What you're currently doing isn't including the tables directly, it's just pulling a list of AssetID values that happen to be from tblSoftware.

By using the sub-SELECT, you're Dr. Frankenstein (the main SELECT) asking Igor (the sub-SELECT) to go check the software tables for machines that match conditions X and Y, then give you a list of the machines that matched. You don't get to see any details, just the final list of machine IDs. By including the software tables in your main query, you're doing the checking yourself and can thus reference all the associated details.

Try this:
Code:
Select Top 1000000
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress As IP,
  tblSoftwareUni.SoftwareName,
  tblSoftware.softwareVersion
From
  tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblSoftware ON tblSoftware.AssetID = tblAssets.AssetID
  INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
Where
  tblSoftwareUni.softwareName Like '%OneDrive'
  And tblSoftware.softwareVersion Not Like '19.232%'
  And tblAssets.Lastseen <> ''
  And tblAssets.Assettype = -1
Order By
   tblAssets.AssetUnique
TimHolmes1973
#2TimHolmes1973 Member Original PosterPosts: 20  
posted: 10/28/2020 8:54:04 PM(UTC)
Thank you so much, hoping this can help towards me, hopefully, not asking anymore questions about these queries.
Tim

Active Discussions

Lansweeper SSH - Keyboard Interactive Authentication
by  blackmoonwolf   Go to last post Go to first unread
Last post: Today at 5:12:41 AM(UTC)
Lansweeper Get My Documents Folder Size
by  cbraafhart  
Go to last post Go to first unread
Last post: Yesterday at 2:25:18 PM(UTC)
Lansweeper Lansweeper Errors - Licencing and scanning
by  dnkleaf   Go to last post Go to first unread
Last post: 11/27/2020 1:02:13 PM(UTC)
Lansweeper Search for Blank Fields
by  CS Caritas Socialis IT  
Go to last post Go to first unread
Last post: 11/27/2020 12:19:05 PM(UTC)
Lansweeper Uninstalled software still listed
by  Dennis Gewillig   Go to last post Go to first unread
Last post: 11/27/2020 9:19:19 AM(UTC)
Lansweeper Software deployment initiated by user
by  LS_enthusiast_4444   Go to last post Go to first unread
Last post: 11/26/2020 9:28:18 PM(UTC)
Lansweeper Help Desk not disabling for regular users?
by  FrankSc  
Go to last post Go to first unread
Last post: 11/26/2020 8:18:38 PM(UTC)