Total Number of Cores

[admin3]

Hi all,

is there a way to get the total number of cores per computer?

We have a physical machine with 2 CPU's and 4 cores per CPU.

So we want the query to report 8 cores.

Any help would be appreciated.

Best regards

[Hemoco · Accepted solution]

The report below will work for Lansweeper 5.0.

Select Distinct Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tsysOS.OSname,
tblComputersystem.SystemType,
tblProcessor.NumberOfCores * tblAssets.NrProcessors As TotalCores,
tblAssets.Memory
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Order By tblAssets.Domain,
tblAssets.AssetName

[ghough]

Lansweeper wrote:
The report below will work for Lansweeper 5.0.

Select Distinct Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tsysOS.OSname,
tblComputersystem.SystemType,
tblProcessor.NumberOfCores * tblAssets.NrProcessors As TotalCores,
tblAssets.Memory
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Order By tblAssets.Domain,
tblAssets.AssetName

Thank You!

[kconverse]

I cannot get the last query to return results. Could there be an issue with the query below not being written in English?



Select Top 1000000 a.SortOrder, e.Computer, e.IP, b.OS, c.Architektur, d.TotalCores, f.Memory, a.Funktionen, b.icon, a.Computername, a.Umgebung From (Select tblCompCustom.Computername, tblCompCustom.Custom8 As Funktionen, tblCompCustom.Custom9 As Umgebung, tblCompCustom.Custom7 As SortOrder From tblCompCustom Where tblCompCustom.Custom7 > ' ') a Left Outer Join (Select Web40OSName.OSname As OS, Web40OSName.Compimage As icon, Web40OSName.Computername From Web40OSName) b On a.Computername = b.Computername Left Outer Join (Select tblComputersystem.SystemType As Architektur, tblComputersystem.Computername From tblComputersystem) c On a.Computername = c.Computername Left Outer Join (Select Distinct a.Computername, IsNull(a.NumberOfCores, 0) As CoresperSocket, b.NrOfProcessors As Sockets, IsNull(a.NumberOfCores, 1) * b.NrOfProcessors As TotalCores From tblPROCESSOR a, web40ProcessorCapacity b Where a.Computername = b.Computername) d On a.Computername = d.Computername Left Outer Join (Select tblcomputers.Computer, tblcomputers.Computername, tblcomputers.LastknownIP As IP From tblcomputers) e On a.Computername = e.Computername Left Outer Join (Select Cast(Cast(tblComputersystem.TotalPhysicalMemory As bigint) / 1048576 As numeric) As Memory, tblComputersystem.Computername From tblComputersystem) f On a.Computername = f.Computername Order By a.SortOrder

[Hemoco]

kconverse wrote:
I cannot get the last query to return results. Could there be an issue with the query below not being written in English?

No, the problem is a custom field criterion included in the report. Please try running the report as shown below instead.

Select Top 1000000 a.SortOrder, e.Computer, e.IP, b.OS, c.Architektur, d.TotalCores, f.Memory, a.Funktionen, b.icon, a.Computername, a.Umgebung From (Select tblCompCustom.Computername, tblCompCustom.Custom8 As Funktionen, tblCompCustom.Custom9 As Umgebung, tblCompCustom.Custom7 As SortOrder From tblCompCustom) a Left Outer Join (Select Web40OSName.OSname As OS, Web40OSName.Compimage As icon, Web40OSName.Computername From Web40OSName) b On a.Computername = b.Computername Left Outer Join (Select tblComputersystem.SystemType As Architektur, tblComputersystem.Computername From tblComputersystem) c On a.Computername = c.Computername Left Outer Join (Select Distinct a.Computername, IsNull(a.NumberOfCores, 0) As CoresperSocket, b.NrOfProcessors As Sockets, IsNull(a.NumberOfCores, 1) * b.NrOfProcessors As TotalCores From tblPROCESSOR a Inner Join web40ProcessorCapacity b On b.Computername = a.Computername Where a.Computername = b.Computername) d On a.Computername = d.Computername Left Outer Join (Select tblComputers.Computer, tblComputers.Computername, tblComputers.LastknownIP As IP From tblComputers) e On a.Computername = e.Computername Left Outer Join (Select Cast(Cast(tblComputersystem.TotalPhysicalMemory As bigint) / 1048576 As numeric) As Memory, tblComputersystem.Computername From tblComputersystem) f On a.Computername = f.Computername Order By a.SortOrder

[ghough]

Select Top 1000000 a.SortOrder, e.Computer, e.IP, b.OS, c.Architektur, d.TotalCores, f.Memory, a.Funktionen, b.icon, a.Computername, a.Umgebung From (Select tblCompCustom.Computername, tblCompCustom.Custom8 As Funktionen, tblCompCustom.Custom9 As Umgebung, tblCompCustom.Custom7 As SortOrder From tblCompCustom) a Left Outer Join (Select Web40OSName.OSname As OS, Web40OSName.Compimage As icon, Web40OSName.Computername From Web40OSName) b On a.Computername = b.Computername Left Outer Join (Select tblComputersystem.SystemType As Architektur, tblComputersystem.Computername From tblComputersystem) c On a.Computername = c.Computername Left Outer Join (Select Distinct a.Computername, IsNull(a.NumberOfCores, 0) As CoresperSocket, b.NrOfProcessors As Sockets, IsNull(a.NumberOfCores, 1) * b.NrOfProcessors As TotalCores From tblPROCESSOR a Inner Join web40ProcessorCapacity b On b.Computername = a.Computername Where a.Computername = b.Computername) d On a.Computername = d.Computername Left Outer Join (Select tblComputers.Computer, tblComputers.Computername, tblComputers.LastknownIP As IP From tblComputers) e On a.Computername = e.Computername Left Outer Join (Select Cast(Cast(tblComputersystem.TotalPhysicalMemory As bigint) / 1048576 As numeric) As Memory, tblComputersystem.Computername From tblComputersystem) f On a.Computername = f.Computername Order By a.SortOrder

Saw this and thought it might be useful to me. I am using V.5 and copied it into the Report Builder and had it fail with the following error:

Invalid object name 'tblCompCustom'.

I am guessing it needs an update to work properly with V.5?

Thanks.

[admin3]

We finally have a working query:

Select Top 1000000 a.SortOrder, e.Computer, e.IP, b.OS, c.Architektur, d.TotalCores, f.Memory, a.Funktionen, b.icon, a.Computername, a.Umgebung From (Select tblCompCustom.Computername, tblCompCustom.Custom8 As Funktionen, tblCompCustom.Custom9 As Umgebung, tblCompCustom.Custom7 As SortOrder From tblCompCustom Where tblCompCustom.Custom7 > ' ') a Left Outer Join (Select Web40OSName.OSname As OS, Web40OSName.Compimage As icon, Web40OSName.Computername From Web40OSName) b On a.Computername = b.Computername Left Outer Join (Select tblComputersystem.SystemType As Architektur, tblComputersystem.Computername From tblComputersystem) c On a.Computername = c.Computername Left Outer Join (Select Distinct a.Computername, IsNull(a.NumberOfCores, 0) As CoresperSocket, b.NrOfProcessors As Sockets, IsNull(a.NumberOfCores, 1) * b.NrOfProcessors As TotalCores From tblPROCESSOR a, web40ProcessorCapacity b Where a.Computername = b.Computername) d On a.Computername = d.Computername Left Outer Join (Select tblcomputers.Computer, tblcomputers.Computername, tblcomputers.LastknownIP As IP From tblcomputers) e On a.Computername = e.Computername Left Outer Join (Select Cast(Cast(tblComputersystem.TotalPhysicalMemory As bigint) / 1048576 As numeric) As Memory, tblComputersystem.Computername From tblComputersystem) f On a.Computername = f.Computername Order By a.SortOrder

Best regards

[admin3]

Thank you for the link.

I know that all information we need is in the database.

sockets --> web40ProcessorCapacity.NrOfProcessors
cores --> tblProcessor.NumberOfCores

So we have all the data we need.

I dont know how to design the sql query to get the following information:

computername: Server34
sockets: 2
cores per sockets: 4
total cores: 8

Do you know what i mean?

Best regards

[Hemoco]

Could you try the report below.

Select Distinct Top 1000000 tblComputers.Computername,
tblComputers.ComputerUnique, tblComputers.Domain,
web40ProcessorCapacity.NrOfProcessors As Sockets, tblPROCESSOR.NumberOfCores
As Cores, web40ProcessorCapacity.NrOfProcessors * tblPROCESSOR.NumberOfCores
As Total
From tblComputers Left Join
web40ProcessorCapacity On web40ProcessorCapacity.Computername =
tblComputers.Computername Inner Join
tblPROCESSOR On tblPROCESSOR.Caption = web40ProcessorCapacity.Name And
tblPROCESSOR.Computername = web40ProcessorCapacity.Computername
Order By tblComputers.ComputerUnique

To use the specified report, do the following:
• Open the Lansweeper configuration console and select Reports & Alerts\Report Builder. Hit the “New” button.
• Copy the SQL code provided and paste it at the bottom of the newly created report, replacing the default SQL code.
• Click somewhere near tblComputers so the new code applies.
• Give the report a “View name” and a “Report name” and hit the “Save” button.
• Double-click on the report in the report list to see its results and export options.

[Hemoco]

Please see this link:
http://support.microsoft.com/kb/932370

Depending on the operating system/patch the information you are looking for can be in different tables (or not available)