Audit Server

[olipop]

Hi All,

I have tried many times to create a report combined of many tabled but I haven't succeded at all. I am not very good at this. I always get red errors in lines, etc. Trying to combine different tables of info that is in the system and scanned already. Comparing endless rows of codes and tables, but still no success. That's why I turn here.

I need help in creating a Server Report icluding the following info:
Servername; Server OS, IP address, Server Type: (Virtual or Phisical), VMware version, VMware Host, Server OS Install Date, SQL Sever version and Edition, CPU/Cores.

Greatly appreciate all answers!

P.S. I anyone can point me in the direction of some kind of paper or instruction on how to compile a new report using multiple, different tables I would be happy to learn.

Brgds,
Oliver P.

[olipop]

Thank you very much once again, you deliver great quality and service!
I'm not good with SQL report building, but I will follow your advice and visit the link you provided. Thanks again!

Brgds,
Oliver P.

[Daniel_B · Accepted solution]

The report editor on the Lansweeper web console is quite similar to the report editor in SQL Server Management Studio. In general we recommend that you learn a bit of SQL. If you know how to build SQL reports, you will easily understand how to build reports in Lansweeper. This seems to be a good tutorial.
The report you would like to create however may not be a good starting point for learning as it involves a lot of different tables which need to be joined in different ways (the list of VMware guest machines' Mac addresses is scanned on VMware hosts and stored in tblVMwareGuestNetwork. You need to join tblVMwareGuestNetwork.MacAddress to tblAssets.Mac and then join tblVMwareGuestNetwork to tblVMwareGuest and this one to a new alias version of tblAssets in order to get the asset name and optionally further details of your VMware hosts).

Please find a report below which lists all successfully scanned Windows servers with the required details.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblOperatingsystem.Caption,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  Case When tblAssetCustom.Manufacturer Like '%vmware%' Or
    tblAssetCustom.Model Like '%virtual%' Or
    tblAssetCustom.Serialnumber Like '%virtual%' Then 'Virtual' Else 'Physical'
  End As Type,
  tblVmwareInfo.Version As [VMware Version],
  tVMwareHost.AssetName As [VMware Host],
  tblOperatingsystem.InstallDate As [Server OS install date],
  tblSqlServers.displayVersion As [SQL Server Version],
  tblSqlServers.skuName As [SQL Server Edition],
  CPUCount.[Count physical CPUs],
  CPUCount.[Count CPU cores],
  CPUCountVMware.[Count physical CPUs] As [VMware Host physical CPUs],
  CPUCountVMware.[Count CPU cores] As [VMware Host CPU cores],
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblComputersystem On tblComputersystem.AssetID = tblAssets.AssetID
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Join tblVmwareGuestNetwork On tblVmwareGuestNetwork.MacAddress =
    tblAssets.Mac
  Left Join tblVmwareGuest On tblVmwareGuestNetwork.GuestID =
    tblVmwareGuest.GuestID
  Left Join tblAssets tVMwareHost On tVMwareHost.AssetID =
    tblVmwareGuest.AssetID
  Left Join tblVmwareInfo On tblVmwareInfo.AssetID = tVMwareHost.AssetID
  Left Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
  Left Join (Select tblProcessor.AssetID,
    Sum(tblProcessor.NumberOfLogicalProcessors) As [Count logical CPUs],
    Sum(tblProcessor.NumberOfCores) As [Count CPU cores],
    Count(tblProcessor.Caption) As [Count physical CPUs]
  From tblProcessor
  Group By tblProcessor.AssetID) CPUCount On tblAssets.AssetID =
    CPUCount.AssetID
  Left Join (Select tblVmwareInfo.AssetID,
    tblVmwareInfo.numCpuThreads As [Count logical CPUs],
    tblVmwareInfo.numCpuCores As [Count CPU cores],
    tblVmwareInfo.numCpuPkgs As [Count physical CPUs]
  From tblVmwareInfo) CPUCountVMware On tVMwareHost.AssetID =
    CPUCountVMware.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1
Order By tblAssets.AssetName