Custom report needed 1 for Hardware and one for all software

[Sdonovan415]

Hi,

I am not a SQL guru so I need help. My customer has Lansweeper 5.1.0.50. I need to create one query for a hardware file that produces DomainName,DeviceName,OperatingSystem,DeviceType,ServicePack,IPAddress,PhysicalOrVirtual,Computer Make, Model SerialNumber,CPUName,NumberPhysicalCPUs,NumberLogicalCPUs,RAM,LastLoggedOnUser,LastSeenDate,Environment (meaning test or Dev,and PhysicalHost.

The Software report needs to contain these for all installed software in program files.AssetName,softwareName,softwareVersion,and SoftwarePublisher.

PLEASE HELP!!!

[gknowles]

This report has a glitch on the logical procs field. On multi-proc systems it seems to be somehow multiplying instead of adding (table join issue?)

[Sdonovan415]

Thank you very much.

[Daniel_B · Accepted solution]

Here is a report which covers your requested fields, except the Environment (test or dev). In which field did you enter this information? If it is in a custom field, you could enter this to the report easily. Custom fields are saved in tblAssetCustom. Just take care to include it also in the GROUP BY part.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tsysOS.Image As icon,
  tblAssets.Domain,
  tsysOS.OSname,
  tsysAssetTypes.AssetTypename As Type,
  tblAssets.SP,
  tblAssets.IPAddress As [IP Address],
  Case When tblAssetCustom.Model Like '%virtual%' Then 'virtual' Else 'physical'
  End As [virtual/physical],
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber As Serial,
  tblAssets.Processor,
  tblAssets.NrProcessors As #CPU,
  Sum(tblProcessor.NumberOfLogicalProcessors) As [Logical Processors],
  Cast(Cast(Sum(tblPhysicalMemory.Capacity) As bigint) / 1024 / 1024 As numeric)
  As RAM,
  tblAssets.Userdomain + '\' + tblAssets.Username As [Last logged on],
  tblAssets.Lastseen,
  SubQuery1.HyperVhost As [Hyper-V Host]
From tblAssets
  Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
  Inner Join tblPhysicalMemory On tblAssets.AssetID = tblPhysicalMemory.AssetID
  Left Join (Select Top 1000000 tblAssets.AssetID,
    tblAssets1.AssetName As HyperVhost
  From tblAssets
    Inner Join tblAssetMacAddress
      On tblAssets.AssetID = tblAssetMacAddress.AssetID
    Inner Join TblHyperVGuestNetwork On TblHyperVGuestNetwork.MacAddress =
      tblAssetMacAddress.Mac
    Left Join tblHyperVGuest On TblHyperVGuestNetwork.HyperVGuestID =
      tblHyperVGuest.hypervguestID
    Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
      tblHyperVGuest.AssetID) SubQuery1 On SubQuery1.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetID,
  tblAssets.AssetUnique,
  tsysOS.Image,
  tblAssets.Domain,
  tsysOS.OSname,
  tsysAssetTypes.AssetTypename,
  tblAssets.SP,
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssets.Processor,
  tblAssets.NrProcessors,
  tblAssets.Lastseen,
  SubQuery1.HyperVhost,
  tblAssets.Userdomain,
  tblAssets.Username
Order By tblAssets.AssetID