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

View solution in original post

4 REPLIES 4
Daniel_B
Lansweeper Alumni
@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
Engaged Sweeper
Daniel,
Is there a way to Group the HotFixID under Each Asset?
MasterBaK33
Engaged Sweeper
thank you so much ! Works fine !
Daniel_B
Lansweeper Alumni
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