Include MS Office with version on my report

[nicklulu]

Greetings,

I'm pretty much a newb, so please be patient with me.

We are in the process of doing a refresh that the machines need to meet 4 criteria:

OS,HDD,RAM and MS Office

I have so far figured out most of the things I need in my report, except for the MS Office on what version.

Here is what I got so far:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssetCustom.Model,
tblAssets.OScode,
tblComputersystem.SystemType,
tblAssets.Memory,
tblAssets.Processor,
tblDiskdrives.Size,
tblDiskdrives.Caption,
tblFloppy.Model As Model1,
tblAssetCustom.PurchaseDate
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssetCustom.Model <> 'VMware Virtual Platform' And
tblAssetCustom.Model Not Like '%PowerEdge%' And tblDiskdrives.Caption = 'C:'
And tblAssetCustom.State = 1 And tblDiskdrives.DriveType = 3


Your guidance is greatly appreciated.

warmest regards,

[paulruvalcaba]

I solved my own question for those interested.

This will also include the build number so that you don't have to reference another list to decode yourself.

You can edit the builds which you want to mark as Green, yellow, or red.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tsysOS.OSname As OS,
  Case
    When tblSoftware.softwareVersion Like '16.0.11425%' Then '1903'
    When tblSoftware.softwareVersion Like '16.0.11328%' Then '1902'
    When tblSoftware.softwareVersion Like '16.0.11231%' Then '1901'
    When tblSoftware.softwareVersion Like '16.0.11126%' Then '1812'
    When tblSoftware.softwareVersion Like '16.0.11029%' Then '1811'
    When tblSoftware.softwareVersion Like '16.0.11001%' Then '1810'
    When tblSoftware.softwareVersion Like '16.0.10827%' Then '1809'
    When tblSoftware.softwareVersion Like '16.0.10730%' Then '1808'
    When tblSoftware.softwareVersion Like '16.0.10325%' Then '1807'
    When tblSoftware.softwareVersion Like '16.0.10228%' Then '1806'
    When tblSoftware.softwareVersion Like '16.0.9330%' Then '1805'
    When tblSoftware.softwareVersion Like '16.0.9226%' Then '1804'
    When tblSoftware.softwareVersion Like '16.0.9126%' Then '1803'
    When tblSoftware.softwareVersion Like '16.0.9029%' Then '1802'
    When tblSoftware.softwareVersion Like '16.0.9001%' Then '1801'
    When tblSoftware.softwareVersion Like '16.0.8827%' Then '1712'
    When tblSoftware.softwareVersion Like '16.0.8730%' Then '1711'
    When tblSoftware.softwareVersion Like '16.0.8625%' Then '1710'
    When tblSoftware.softwareVersion Like '16.0.8528%' Then '1709'
    When tblSoftware.softwareVersion Like '16.0.8431%' Then '1708'
    When tblSoftware.softwareVersion Like '16.0.8326%' Then '1707'
    When tblSoftware.softwareVersion Like '16.0.8229%' Then '1706'
    When tblSoftware.softwareVersion Like '16.0.8201%' Then '1705'
    When tblSoftware.softwareVersion Like '16.0.8067%' Then '1704'
    When tblSoftware.softwareVersion Like '16.0.7967%' Then '1703'
    When tblSoftware.softwareVersion Like '16.0.7870%' Then '1702'
    When tblSoftware.softwareVersion Like '16.0.7766%' Then '1701'
    When tblSoftware.softwareVersion Like '16.0.7668%' Then '1612'
    When tblSoftware.softwareVersion Like '16.0.7571%' Then '1611'
    Else 'Unknown'
  End As Build,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.softwareName As Software,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case
    When tblSoftware.softwareVersion Like '16.0.9126%' Then '#feffad'
    When tblSoftware.softwareVersion Like '16.0.9029%' Then '#feffad'
    When tblSoftware.softwareVersion Like '16.0.9001%' Then '#feffad'
    When tblSoftware.softwareVersion Like '16.0.8827%' Then '#ffadad'
    When tblSoftware.softwareVersion Like '16.0.8730%' Then '#ffadad'
    When tblSoftware.softwareVersion Like '16.0.8625%' Then '#ffadad'
    When tblSoftware.softwareVersion Like '16.0.8528%' Then '#ffadad'
    When tblSoftware.softwareVersion Like '16.0.8431%' Then '#ffadad'
    When tblSoftware.softwareVersion Like '16.0.8326%' Then '#ffadad'
    When tblSoftware.softwareVersion Like '16.0.8229%' Then '#ffadad'
    When tblSoftware.softwareVersion Like '16.0.8201%' Then '#ffadad'
    When tblSoftware.softwareVersion Like '16.0.8067%' Then '#ffadad'
    When tblSoftware.softwareVersion Like '16.0.7967%' Then '#ffadad'
    When tblSoftware.softwareVersion Like '16.0.7870%' Then '#ffadad'
    When tblSoftware.softwareVersion Like '16.0.7766%' Then '#ffadad'
    When tblSoftware.softwareVersion Like '16.0.7668%' Then '#ffadad'
    When tblSoftware.softwareVersion Like '16.0.7571%' Then '#ffadad'
    Else '#d4f4be'
  End As backgroundcolor
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
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblSoftwareUni.softwareName Like '%Office 365%' And tblState.Statename =
  'Active'
Order By Build

[nicklulu]

I really appreciate the help.

Thank You!

[David_G · Accepted solution]

We have edited the report you have provided us with so it will give back Microsoft Office installations as well, do note that it might give back duplicate rows as there is a difference between for instance Microsoft Office Professional 2010 and Microsoft Office 365 ProPlus. You can edit what we have highlighted below so it will fit your needs. Additionally, we have changed the disk size field so it will be given back in gigabytes instead of bytes.

Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssetCustom.Model,
  tblAssets.OScode,
  tblComputersystem.SystemType,
  tblAssets.Memory,
  tblAssets.Processor,
  Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As numeric) As
  [Disk Size in GB],
  tblDiskdrives.Caption,
  tblFloppy.Model As [HD Model],
  tblAssetCustom.PurchaseDate,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssetCustom.Model <> 'VMware Virtual Platform' And
  tblAssetCustom.Model Not Like '%PowerEdge%' And tblDiskdrives.Caption = 'C:'
  And tblSoftwareUni.softwareName Like '%Microsoft Office%' And
  tblAssetCustom.State = 1 And tblDiskdrives.DriveType = 3

[paulruvalcaba]

What about making this report include the Build number?
How would one create the part that decodes the versions to build numbers?

David.G wrote:
We have edited the report you have provided us with so it will give back Microsoft Office installations as well, do note that it might give back duplicate rows as there is a difference between for instance Microsoft Office Professional 2010 and Microsoft Office 365 ProPlus. You can edit what we have highlighted below so it will fit your needs. Additionally, we have changed the disk size field so it will be given back in gigabytes instead of bytes.

Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssetCustom.Model,
  tblAssets.OScode,
  tblComputersystem.SystemType,
  tblAssets.Memory,
  tblAssets.Processor,
  Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As numeric) As
  [Disk Size in GB],
  tblDiskdrives.Caption,
  tblFloppy.Model As [HD Model],
  tblAssetCustom.PurchaseDate,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssetCustom.Model <> 'VMware Virtual Platform' And
  tblAssetCustom.Model Not Like '%PowerEdge%' And tblDiskdrives.Caption = 'C:'
  And tblSoftwareUni.softwareName Like '%Microsoft Office%' And
  tblAssetCustom.State = 1 And tblDiskdrives.DriveType = 3