Show only newest microsoft Office

[fpino]

please help with upgrade of this query to last lansweeper version.

[Susan_A]

The report I posted only lists software packages whose name: starts with "Microsoft Office", ends in a year (e.g. 2010) and does not contain the word "viewer". I added these filters to prevent all kinds of irrelevant Office tools and components from showing up in the report output.

The only solution I see for a more "accurate" report is for you to manually submit the software names you wish to report on in the SQL query. See sample query below. You'll need to submit the Office installations you wish to report on twice and exactly as they are listed in Add/Remove Programs. If you have Office installations that are listed in Add/Remove Programs simply as "Microsoft Office", you will need to add Cases to the report to convert those software names to a "full" Office name that includes a version number.

For further modifications, please take some time to familiarize yourself with SQL first. If you know SQL, you'll be able to understand how our reports work and make changes on your own.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tsysOS.Image As icon
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join (Select Top 1000000 tblSoftware.AssetID,
Max(Cast(SubString(tblSoftwareUni.softwareName, CharIndex('20',
tblSoftwareUni.softwareName), 4) As int)) As ShortVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName In ('Microsoft Office Standard 2010',
'Microsoft Office Professional 2010')
Group By tblSoftware.AssetID) SubQuery1 On SubQuery1.AssetID =
tblSoftware.AssetID And
SubQuery1.ShortVersion = Cast(SubString(tblSoftwareUni.softwareName,
CharIndex('20', tblSoftwareUni.softwareName), 4) As int)
Where tblSoftwareUni.softwareName In ('Microsoft Office Standard 2010',
'Microsoft Office Professional 2010') And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
Software

[fpino]

thanks you!!. The report is casi perfect but i can not see assets with software name:
Microsoft Office ( something machines display this name to office 2010) and Microsoft office 2000 versions.
I have Microsoft Office 2000 SR-1 Standard, Microsoft Office 2000 SR-1 Professional,Microsoft Office 2000 Professional, Microsoft Office 2000 Premium, Microsoft Office 2000 Standard). Please help me with this modification. thanks you!!.

[Susan_A · Accepted solution]

If only the latest Microsoft Office version is still installed and listed in Add/Remove Programs, the report referred to in my previous post should already do what you require. If Add/Remove Programs still lists older versions as well, use the modified report below to list only the latest version.

The report below is not a direct "conversion" of the report you initially referred to however. The report you referred to lists *all* Microsoft Office installations. The customer in the old thread had fully uninstalled old Office versions from his computers, but was confused because he was still seeing them listed in the license key table. We explained to him that he needed to run a software installation report, not a license key report, and provided him with a report to list *all* Office installations.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tsysOS.Image As icon
From tblAssets
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join (Select Top 1000000 tblSoftware.AssetID,
    Max(Cast(Reverse(SubString(Reverse(tblSoftwareUni.softwareName), 1,
    4)) As int)) As ShortVersion
  From tblSoftware
    Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Where tblSoftwareUni.softwareName Like 'Microsoft Office%' And
    tblSoftwareUni.softwareName Not Like '%viewer%' And
    SubString(tblSoftwareUni.softwareName, Len(tblSoftwareUni.softwareName) - 4,
    3) = ' 20'
  Group By tblSoftware.AssetID) SubQuery1 On SubQuery1.AssetID =
    tblSoftware.AssetID And
    SubQuery1.ShortVersion =
    Cast(Reverse(SubString(Reverse(tblSoftwareUni.softwareName), 1, 4)) As int)
Where tblSoftwareUni.softwareName Like 'Microsoft Office%' And
  tblSoftwareUni.softwareName Not Like '%viewer%' And
  SubString(tblSoftwareUni.softwareName, Len(tblSoftwareUni.softwareName) - 4,
  3) = ' 20' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName,
  Software

[fpino]

Ok, but i need report that show only the newest Office installed into machine:
example:

before:
test-pc1, office 2003
test-pc1, office 2007
test-pc2, office 2007
test-pc3, office 2007
test-pc3, office 2010

after:
test-pc1, office 2007
test-pc2, office 2007
test-pc3, office 2010

is possible this report?
thanks you.

[Susan_A]

A sample Microsoft Office report compatible with the latest Lansweeper release can be found in the Report Center, here.