Notification

Icon
Error

Include MS Office with version on my report - How to include MS Office with its version on my report

Posted: Thursday, March 16, 2017 12:17:51 AM(UTC)
nicklulu

nicklulu

Member Original PosterPosts: 2
0
Like
This issue has been solved! Click here to view the solution
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, Angel
David.G
#1David.G Member Administration Posts: 113  
posted: 3/16/2017 10:08:33 AM(UTC)
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.
Code:
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 '%[h]Microsoft Office[/h]%' And
  tblAssetCustom.State = 1 And tblDiskdrives.DriveType = 3
nicklulu
#2nicklulu Member Original PosterPosts: 2  
posted: 3/20/2017 4:55:58 PM(UTC)
I really appreciate the help.

Thank You!
Apaulcolypse
#3Apaulcolypse Member Posts: 23  
posted: 4/16/2019 10:14:04 PM(UTC)
What about making this report include the Build number?
How would one create the part that decodes the versions to build numbers?


Originally Posted by: David.G Go to Quoted Post
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.
Code:
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 '%[h]Microsoft Office[/h]%' And
  tblAssetCustom.State = 1 And tblDiskdrives.DriveType = 3


Apaulcolypse
#4Apaulcolypse Member Posts: 23  
posted: 4/23/2019 3:27:21 PM(UTC)
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.





Code:
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

Active Discussions

Lansweeper Email attachments
by  krejar   Go to last post Go to first unread
Last post: 8/15/2019 10:15:07 PM(UTC)
Lansweeper HP Printer not showing correctly
by  Fathom  
Go to last post Go to first unread
Last post: 8/15/2019 4:14:55 PM(UTC)
Lansweeper Install LsAgent on Mac
by  Jonnym   Go to last post Go to first unread
Last post: 8/14/2019 10:14:08 AM(UTC)
Lansweeper Asset State Question
by  rtaylor  
Go to last post Go to first unread
Last post: 8/13/2019 5:53:24 PM(UTC)
Lansweeper LSAgent Version
by  roberto.m  
Go to last post Go to first unread
Last post: 8/13/2019 3:33:50 PM(UTC)
Lansweeper Lansweeper and snmp v3
by  Justin5135   Go to last post Go to first unread
Last post: 8/12/2019 7:07:22 PM(UTC)
Lansweeper Hikvision CCTV Cameras SNMP scan
by  thejduk  
Go to last post Go to first unread
Last post: 8/12/2019 2:34:05 PM(UTC)