Report similar to the information shown on summary of an asset

[MGNT]

Hello all SQL guru's out here

Would it be possible to create a report that generates the same information as the summary screen shows when selecting an asset, but then for all assets in the database?

So i mean for example:

01) Asset Type
02) OS
03) Manufacturer
04) Model
05) Memory
06) Processor
07) Domain
08) AD description
09) Disk1 (+size + free), Disk2 (+size + free), etc for all disks in the system
10) State
11) IP Location
12) Asset location
13) VMWare (On which ESX server does the guest run)
14) Serial
15) Uptime
16) First seen
17) Last seen

And lastly, if possible, i would love to have the VMWare annotations (especially the notes) added but i cannot find that information anywhere in the database... does any know if the VMWare annotations are currently being scanned by Lansweeper?

I have tried creating a report for the above but it gets foggy when i try to add the VMWare esx server a virtual host is running on... Also getting the disks on one line - rather than creating multiple lines for the same asset, seems to be difficult (for me atleast)

Any help is greatly appreciated!

[MGNT]

Daniel,

Thank you for your reply! I have imported this report and it is working perfectly! It is nice to see how you solved that VMWare host issue i was having 🙂 I am defenately going to analyse that!

We are using SQL server (2008R2) which might support getting mutiple things on a single row. If it does it will be a good exersize for me to get (each detected) disk in a separate column in a single row (per asset).

I will try to define a couple of collumns and get the information in them. Maybe something like this:

~ | Disk1 | Disk1 Size | Disk1 %free | Disk2 | Disk2 Size | Disk2 %free (untill disk 10)
~ C: 50GB 20% 😧 150GB 2%

If there is no disk (2,3,4, etc), it will simply be empty.

Thanks again for your help and for the links to the information! Greatly appreciated!

Kind regards!

[Daniel_B · Accepted solution]

Please find a report below which lists the data you would like to display. We are summing up the hard-drive disk size and free space per asset as stuffing multiple values into one row is not really supported by SQL which we are using for reports, at least not on SQL Compact.

Instructions for running reports can be found here. If you are interested in building or modifying reports, we would recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder uses standard SQL queries. This seems like a good tutorial.
  
• Updating to Lansweeper 5.2, if you haven't already. Lansweeper 5.2 includes a database dictionary, which is linked at the top of the report builder.
  

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblOperatingsystem.Caption As OS,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.Processor,
  tblAssets.Domain,
  tblADComputers.Description As [AD description],
  Cast(Cast(tDiskdrives.Freespace As bigint) / 1024 / 1024 / 1024 As Numeric) As
  [Sum diskdrive free space],
  Cast(Cast(tDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As Numeric) As
  [Sum diskdrive size],
  tblState.Statename As State,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Location,
  tVMwareHost.AssetName As [VMware host],
  tblAssetCustom.Serialnumber,
  tblAssets.Uptime,
  tblAssets.Firstseen,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join (Select tblDiskdrives.AssetID,
    Sum(tblDiskdrives.Freespace) As Freespace,
    Sum(tblDiskdrives.Size) As Size
  From tblDiskdrives
  Where tblDiskdrives.DriveType = 3
  Group By tblDiskdrives.AssetID) tDiskdrives On tblAssets.AssetID =
    tDiskdrives.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysIPLocations
    On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
    tsysIPLocations.EndIP
  Left Join tblVmwareGuestNetwork
    On tblAssets.Mac = tblVmwareGuestNetwork.MacAddress
  Left Join tblVmwareGuest On tblVmwareGuestNetwork.GuestID =
    tblVmwareGuest.GuestID
  Left Join tblAssets tVMwareHost On tVMwareHost.AssetID =
    tblVmwareGuest.AssetID
Order By tblAssets.AssetName