Custom Report for a group (Windows Servers) with specifics informations

[MasterBaK33]

Hi,

After some researches, I have tried to make a custom report but doesn't work at all.
I need to create a custom report for specific servers, so I have created a static group with my concerned servers.
The name of this group is "SF4"
I need a custom report of all servers contained in this group with all informations below :

-OS Version
- Hotfix installed for each server
- Roles / Features installed for each server
- software installed for each server
- CPU
- Memory
- Disk Drives (letter / size) for each server
- Network adaptater

Thanks !

[Daniel_B]

@EOINT, if possible could you create a new forum topic under the report requests section and explain what exactly you would like to see in your report.

[EOINT]

Daniel,
Is there a way to Group the HotFixID under Each Asset?

[MasterBaK33]

thank you so much ! Works fine !

[Daniel_B · Accepted solution]

This would cause many, many duplicate rows. We recommend using multiple individual reports for the data you are interested in.
The following report lists basic hardware details, including disk drives for computers in the static asset group "SF4"

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  tblOperatingsystem.Caption As OS,
  tblAssets.Processor,
  tblAssets.Memory,
  tDiskdrives.Caption As [HDD drive letter],
  Cast(Cast(tDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As [HDD size],
  Cast(Cast(tDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As
  [HDD free space]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
  Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
    tblAssetGroupLink.AssetGroupID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join (Select tblDiskdrives.Caption,
    tblDiskdrives.AssetID,
    tblDiskdrives.Size,
    tblDiskdrives.Freespace
  From tblDiskdrives
  Where tblDiskdrives.DriveType = 3) tDiskdrives On tblAssets.AssetID =
    tDiskdrives.AssetID
Where tblAssetCustom.State = 1 And tblAssetGroups.AssetGroup Like 'sf4'
Order By tblAssets.AssetName,
  [HDD drive letter]

The following report lists network adapters:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  tblNetworkAdapter.Name As [network adapter],
  tblNetworkAdapter.Manufacturer,
  tblNetworkAdapter.Speed
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
  Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
    tblAssetGroupLink.AssetGroupID
  Inner Join tblNetworkAdapter On tblAssets.AssetID = tblNetworkAdapter.AssetID
Where tblAssetCustom.State = 1 And tblAssetGroups.AssetGroup Like 'sf4' And
  tblNetworkAdapter.NetEnabled = 1
Order By tblAssets.AssetName,
  [network adapter]

The following report lists software installations:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  tblSoftwareUni.softwareName,
  tblSoftwareUni.SoftwarePublisher,
  tblSoftware.softwareVersion
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
  Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
    tblAssetGroupLink.AssetGroupID
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssetCustom.State = 1 And tblAssetGroups.AssetGroup Like 'sf4'
Order By tblAssets.AssetName,
  tblSoftwareUni.softwareName

The following report lists some information about server roles:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  tblDomainroles.Domainrolename,
  tblComputersystem.Roles
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
  Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
    tblAssetGroupLink.AssetGroupID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblDomainroles On tblDomainroles.Domainrole =
    tblComputersystem.Domainrole
Where tblAssetCustom.State = 1 And tblAssetGroups.AssetGroup Like 'sf4'
Order By tblAssets.AssetName

And this report lists installed Windows hotfixes:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  tblQuickFixEngineeringUni.HotFixID,
  tblQuickFixEngineeringUni.Description,
  tblQuickFixEngineering.InstalledOn
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
  Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
    tblAssetGroupLink.AssetGroupID
  Inner Join tblQuickFixEngineering On tblAssets.AssetID =
    tblQuickFixEngineering.AssetID
  Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
    tblQuickFixEngineering.QFEID
Where tblAssetCustom.State = 1 And tblAssetGroups.AssetGroup Like 'sf4'
Order By tblAssets.AssetName,
  tblQuickFixEngineeringUni.HotFixID