→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jtoma
Engaged Sweeper
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!
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
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