Notification

Icon
Error

Software audit with installation date

Posted: Friday, July 3, 2020 6:27:28 AM(UTC)
ellis.tsui

ellis.tsui

Member Original PosterPosts: 1
0
Like
I want to make a software audit report with installation date using the default report "Assets: Installed software by computer" as base.
The error “All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.” is shown when I added “Install date” into the software audit report. Please help to see what when wrong. Thanks.

The codes are as follows:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tblSoftware.Installdate As [Install date],
tblSoftware.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblState.Statename = 'Active'
Union
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblMacOSInfo.SystemVersion As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName As Software,
tblMacApplications.Version As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tblMacApplications.LastChanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblMacApplications On
tblAssets.AssetID = tblMacApplications.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblMacApplications.softid
Inner Join tblMacOSInfo On tblMacOSInfo.AssetID = tblAssets.AssetID
Where tblState.Statename = 'Active'
Union
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblLinuxSystem.OSRelease As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName As Software,
tblLinuxSoftware.Version As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tblLinuxSoftware.LastChanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblLinuxSoftware On tblAssets.AssetID = tblLinuxSoftware.AssetID
Inner Join tblSoftwareUni On
tblSoftwareUni.SoftID = tblLinuxSoftware.SoftwareUniID
Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where tblState.Statename = 'Active'
Order By Domain,
AssetName,
Software

RC62N
#1RC62N Member Posts: 450  
posted: 7/3/2020 3:39:41 PM(UTC)
The columns in each of the data sets that you're trying to UNION have to match. In your case, they don't: you have one more column in your first SELECT than you have in your second and third.
Code:
tblAssets.AssetID,                                tblAssets.AssetID,                                tblAssets.AssetID,
tblAssets.AssetName,                              tblAssets.AssetName,                              tblAssets.AssetName,
tblAssets.Domain,                                 tblAssets.Domain,                                 tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,        tsysAssetTypes.AssetTypename As AssetType,        tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,                               tblAssets.Username,                               tblAssets.Username,
tblAssets.Userdomain,                             tblAssets.Userdomain,                             tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 As icon,           tsysAssetTypes.AssetTypeIcon10 As icon,           tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,                              tblAssets.IPAddress,                              tblAssets.IPAddress,
tsysIPLocations.IPLocation,                       tsysIPLocations.IPLocation,                       tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,                      tblAssetCustom.Manufacturer,                      tblAssetCustom.Manufacturer,
tblAssetCustom.Model,                             tblAssetCustom.Model,                             tblAssetCustom.Model,
tsysOS.OSname As OS,                              tblMacOSInfo.SystemVersion As OS,                 tblLinuxSystem.OSRelease As OS,
tblAssets.SP,                                     tblAssets.SP,                                     tblAssets.SP,
tblAssets.Lastseen,                               tblAssets.Lastseen,                               tblAssets.Lastseen,
tblAssets.Lasttried,                              tblAssets.Lasttried,                              tblAssets.Lasttried,
tblSoftwareUni.softwareName As Software,          tblSoftwareUni.softwareName As Software,          tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,           tblMacApplications.Version As Version,            tblLinuxSoftware.Version As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,    tblSoftwareUni.SoftwarePublisher As Publisher,    tblSoftwareUni.SoftwarePublisher As Publisher,
tblSoftware.Installdate As [Install date],
tblSoftware.Lastchanged                           tblMacApplications.LastChanged                    tblLinuxSoftware.LastChanged

If you have nothing to correspond to the first in the second and third SELECTs, use NULL as a filler.

Active Discussions

Lansweeper Software List by Computer/Server Name
by  Andy.S  
Go to last post Go to first unread
Last post: Yesterday at 8:06:43 AM(UTC)
Lansweeper Asset: Duplicate Serial Number (Built-in)
by  brodiemac-too   Go to last post Go to first unread
Last post: 8/5/2020 1:28:56 PM(UTC)
Lansweeper A Simple Report (or so I thought)
by  Andy.S  
Go to last post Go to first unread
Last post: 8/5/2020 11:30:45 AM(UTC)
Lansweeper Paid Report Writer Service
by  endyk   Go to last post Go to first unread
Last post: 8/4/2020 11:56:20 AM(UTC)
Lansweeper Multiple Computers per User
by  brodiemac-too  
Go to last post Go to first unread
Last post: 7/30/2020 12:43:38 PM(UTC)
Lansweeper Workstations with No AV
by  brodiemac-too   Go to last post Go to first unread
Last post: 7/29/2020 7:44:53 PM(UTC)
Lansweeper AV Audit Report
by  RC62N  
Go to last post Go to first unread
Last post: 7/29/2020 5:17:42 PM(UTC)