I just need a report to show all server

[daniel890]

Hi Guys,

I am using the below SQL script.....I only need it to report on the servers not PC's also can you tell me how to show if they are VM's or physical servers.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName As [Computer Name],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblProcessor.NumberOfCores,
tsysIPLocations.IPLocation As [Office Location],
tsysOS.OSname As [Operating System],
tblAssets.IPAddress As [IP Address],
tblAssets.FQDN As [Host Name],
tblAssets.SP
From tblAssets
Inner Join (Select tblAssets.AssetID,
Max(tblProcessor.MaxClockSpeed) As MaxClockSpeed,
Count(tblProcessor.WIN32_PROCESSORid) As NrOfProcessors,
Max(tblProcessor.MaxClockSpeed) As [total Proc Capacity],
Max(tblProcessor.Architecture) As Architecture,
Max(tblProcessor.Caption) As Name,
Max(Replace(Replace(tblProcessor.Name, '(R)', ''), '(TM)', '')) As CPU
From tblAssets
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Group By tblAssets.AssetID) ProcCapacity On ProcCapacity.AssetID =
tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Where tsysIPLocations.IPLocation Like '%'
Order By tblAssetCustom.Model,
[Computer Name]

[daniel890]

Just to add its reporting back on everything 4 times?

[Hemoco · Accepted solution]

daniel890 wrote:
I only need it to report on the servers not PC's also can you tell me how to show if they are VM's or physical servers

Use the SQL query below for this.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName As [Computer Name],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblProcessor.NumberOfCores,
tsysIPLocations.IPLocation As [Office Location],
tsysOS.OSname As [Operating System],
tblAssets.IPAddress As [IP Address],
tblAssets.FQDN As [Host Name],
tblAssets.SP,
Case When tblAssetCustom.Model Like '%virtual%' Then 'virtual' Else 'physical'
End As VirtualCheck
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblComputersystem.Domainrole > 1
Order By tblAssetCustom.Model,
[Computer Name]

daniel890 wrote:
Just to add its reporting back on everything 4 times?

This is expected behavior if the asset has multiple processors and/or is part of multiple IP locations. SQL displays each table record (processor, IP location) as one line.