→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
erankave
Engaged Sweeper II
I need to show two different values of the same attribute in one report.

To be more specific. In the report we want to see
  • the currently installed Office Client

LIKE 'Microsoft Office Professional%' OR
'Microsoft Office Standard%' OR
'Microsoft Office 365%')


as well as the currently installed
  • Microsoft Dynamics Addon

= 'Microsoft Dynamics 365 for Microsoft Office Outlook'



I would really appreciate it, if someone can help us.
1 ACCEPTED SOLUTION
Esben_D
Lansweeper Employee
Lansweeper Employee
Easiest method would be to create a subquery for Microsoft dynamics. The first part is the built-in software report with the first criteria you mentioned.

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,
MSDynamic.[Microsoft Dynamics],
tblSoftware.Lastchanged
From tblAssets
Inner Join (Select Top 1000000 tblAssets.AssetID,
tblSoftwareUni.softwareName As [Microsoft Dynamics]
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
tblSoftwareUni.softwareName =
'Microsoft Dynamics 365 for Microsoft Office Outlook') As MSDynamic On
MSDynamic.AssetID = tblAssets.AssetID
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 (tblSoftwareUni.softwareName Like 'Microsoft Office Professional%' Or
tblSoftwareUni.softwareName Like 'Microsoft Office Standard%' Or
tblSoftwareUni.softwareName Like 'Microsoft Office 365%') And
tblAssetCustom.State = 1

View solution in original post

2 REPLIES 2
Esben_D
Lansweeper Employee
Lansweeper Employee
Easiest method would be to create a subquery for Microsoft dynamics. The first part is the built-in software report with the first criteria you mentioned.

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,
MSDynamic.[Microsoft Dynamics],
tblSoftware.Lastchanged
From tblAssets
Inner Join (Select Top 1000000 tblAssets.AssetID,
tblSoftwareUni.softwareName As [Microsoft Dynamics]
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
tblSoftwareUni.softwareName =
'Microsoft Dynamics 365 for Microsoft Office Outlook') As MSDynamic On
MSDynamic.AssetID = tblAssets.AssetID
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 (tblSoftwareUni.softwareName Like 'Microsoft Office Professional%' Or
tblSoftwareUni.softwareName Like 'Microsoft Office Standard%' Or
tblSoftwareUni.softwareName Like 'Microsoft Office 365%') And
tblAssetCustom.State = 1
erankave
Engaged Sweeper II
Thank you very much for your effort. I really appreciate it. That is exactly what I had in mind.
Charles.X wrote:
Easiest method would be to create a subquery for Microsoft dynamics. The first part is the built-in software report with the first criteria you mentioned.