→ 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: 
daniel890
Engaged Sweeper
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]
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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.

View solution in original post

2 REPLIES 2
daniel890
Engaged Sweeper
Just to add its reporting back on everything 4 times?
Hemoco
Lansweeper Alumni
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.