Software audit with installation date

[ellis_tsui]

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

[RCorbeil]

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.

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.