→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
lifo
Engaged Sweeper
I am trying to add a Microsoft Office Version column where it displays the Software Name as the results but im stuck.

here is the subquery:

Case When tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Microsoft Office % 20%') Then
'installed' Else '---' End As [MS Office],


I dont want it to show whether its installed rather i want to fill the field with the software name

Ex. microsoft standard 2010

Any help would be appreciated!


1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
I've pasted a sample report below.
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
SubQuery.Software,
SubQuery.Version,
SubQuery.Publisher
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Top 1000000 tblSoftware.AssetID,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%microsoft%office%') SubQuery
On SubQuery.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName

View solution in original post

3 REPLIES 3
Susan_A
Lansweeper Alumni
I've pasted a sample report below.
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
SubQuery.Software,
SubQuery.Version,
SubQuery.Publisher
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Top 1000000 tblSoftware.AssetID,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%microsoft%office%') SubQuery
On SubQuery.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName
lifo
Engaged Sweeper
Thanks Daniel. Is there a way to show all PCs regardless of whether they have office or not? Currently your example only shows PCs that have Office.

Thanks
Daniel_B
Lansweeper Alumni
You seem to have copied a part of another report which was used in another context. Have a look at this page, it provides a report which lists office installations.