Custom Report - Online/Offiline

[jtoma]

I need a custom report of...

AssetName
domain
OSname
type
SP
Ip Address
Virtual/Physical
Manufacturer
Model
Serial
Processor
RAM
Last Logged on
OS C:
State:
Updatetime
Warranty
AntiVirus
Monitors

Thanks in advanced!

[Daniel_B · Accepted solution]

Most items are being listed by the following report. Data of connected monitors can be listed through the built-in report "Monitor: Information" or, in case your database is running on SQL server Express or higher edition, through this report. Antivirus information is more complex to report on. Please find an example report at the bottom of this page.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblOperatingsystem.Caption As OS,
  tsysAssetTypes.AssetTypename As Type,
  tblOperatingsystem.ServicePackMajorVersion As SP,
  tblAssets.IPAddress,
  Case When tsysAssetTypes.AssetType = -1 Then Case
      When tblAssetCustom.Serialnumber Like '%virtual%' Or
      tblAssetCustom.Model Like '%virtual%' Then 'virtual' Else 'physical' End
  End As [Virtual/Physical],
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssets.Processor,
  tblAssets.Memory As RAM,
  tblAssets.Username As [Last user logged on],
  tblOperatingsystem.SystemDrive As [OS system drive],
  tblState.Statename As State,
  tblAssets.Lastseen As [Last scanned],
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
Order By tblAssets.AssetName

Anti-Virus report below. For details on anti-virus software scanning, please refer to this KB article.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Lastseen,
  tAVSoftware.softwareName [AV software],
  Case When tblAntivirus.onAccessScanningEnabled = 1 Then 'Yes' Else 'No'
  End As [Antivirus Enabled],
  Case When tblAntivirus.productUpToDate = 1 Then 'Yes' Else 'No'
  End As [Antivirus Up To Date],
  Case When Coalesce(tAVSoftware.softwareName, '') = '' Then '#FF9999'
    Else '#AAFFAA' End As backgroundcolor,
  Case When tblAntivirus.onAccessScanningEnabled = 0 Or
    tblAntivirus.productUpToDate = 0 Then '#FF0000' Else '#000000'
  End As foregroundcolor
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join (Select tblSoftware.AssetID,
    tblSoftwareUni.softwareName
  From tblSoftware
    Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Inner Join tsysantivirus On tblSoftwareUni.softwareName Like
      tsysantivirus.Software) tAVSoftware On tAVSoftware.AssetID =
    tblAssets.AssetID
  Left Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID
Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Order By tblAssets.AssetName