→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

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

View solution in original post

9 REPLIES 9
Hemoco
Lansweeper Alumni
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
Engaged Sweeper
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
Engaged Sweeper
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
Lansweeper Alumni
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
Engaged Sweeper
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
Engaged Sweeper
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
Engaged Sweeper
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
Lansweeper Alumni
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
Lansweeper Alumni
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)