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 Help Desk Workflow
by  lswanson   Go to last post Go to first unread
Last post: Yesterday at 11:12:21 PM(UTC)
Lansweeper Changing to remote scanning due to COVID
by  FrankSc  
Go to last post Go to first unread
Last post: Yesterday at 9:35:26 PM(UTC)
Lansweeper Not giving hackers the Domain Admin password / account
by  FrankSc   Go to last post Go to first unread
Last post: Yesterday at 9:27:13 PM(UTC)
Lansweeper LsAgent failing - Lansweeper SSL Expired
by  lansweeper25t34  
Go to last post Go to first unread
Last post: Yesterday at 8:33:28 PM(UTC)
Lansweeper Is there a chance to get the firewall off via Lansweeper?
by  EDV_OHZ   Go to last post Go to first unread
Last post: Yesterday at 4:57:26 PM(UTC)
Lansweeper Merge Asset button
by  KeithBecker  
Go to last post Go to first unread
Last post: 8/5/2020 9:27:54 PM(UTC)
Lansweeper Merge Two Assets or Update Based on Serial Number
by  KeithBecker   Go to last post Go to first unread
Last post: 8/5/2020 9:25:16 PM(UTC)
Lansweeper Hyper-V guest assets
by  bgstein  
Go to last post Go to first unread
Last post: 8/5/2020 5:50:24 PM(UTC)