Report Information

[ESIT]

I need a report that has the following:

1. Computer Name
2. Vendor
3. Model
4. Type (server, notebook, workstation)
5. Number of CPU’s
6. Max Clock Speed
7. Location

Is that possible? I know most of this is, but I don't see anything in the tables or views for Location.

Thanks!

[taeratrin]

Heh, no prob. I'm desktop support, and didn't know any SQL up until a few months ago. To add info from another table, simple nest another SELECT into the statement :

SELECT DISTINCT TOP (100) PERCENT Computername, Manufacturer, Model, NumberOfProcessors,
(SELECT DISTINCT Domain
FROM dbo.tblComputers
WHERE (Computername = dbo.tblComputersystem.Computername)) AS Expr1,
(SELECT DISTINCT CurrentClockSpeed
FROM dbo.tblPROCESSOR
WHERE (Computername = dbo.tblComputersystem.Computername) AND (DeviceID = 'CPU0')) AS expr2
FROM dbo.tblComputersystem

I had to put in the DeviceID= 'CPU0' because it will return multiple values for multi-core/multi-CPU systems, which would result in having the same error as before. The number of processors comes from tblComputerSystem.

[taeratrin]

Sorry, tested it without having anything in the tblADComputers. Try the following :

SELECT DISTINCT TOP (100) PERCENT Computername, Manufacturer, Model,
(SELECT DISTINCT Location
FROM dbo.tblADComputers
WHERE (Computername = dbo.tblComputersystem.Computername)) AS Expr1
FROM dbo.tblComputersystem

[ESIT]

taeratrin wrote:
Sorry, tested it without having anything in the tblADComputers. Try the following :

SELECT DISTINCT TOP (100) PERCENT Computername, Manufacturer, Model,
(SELECT DISTINCT Location
FROM dbo.tblADComputers
WHERE (Computername = dbo.tblComputersystem.Computername)) AS Expr1
FROM dbo.tblComputersystem

Sweet, this is working. Now how do I add a third query to show number processors and max proc speed? table for that is tblPROCESSOR.

Sorry for the questions, I'm a Systems Engineer that has no DB background......yet.

[taeratrin]

I'm not really good at JOIN's, but our DB admin pointed out a way to do this without them :

SELECT DISTINCT TOP (100) PERCENT Computername, Manufacturer, Model,
(SELECT DISTINCT Location
FROM dbo.tblADComputers) AS Expr1
FROM dbo.tblComputersystem

You can extend on this as needed. It's ugly, but it works.

[ESIT]

taeratrin wrote:
I'm not really good at JOIN's, but our DB admin pointed out a way to do this without them :

SELECT DISTINCT TOP (100) PERCENT Computername, Manufacturer, Model,
(SELECT DISTINCT Location
FROM dbo.tblADComputers) AS Expr1
FROM dbo.tblComputersystem

You can extend on this as needed. It's ugly, but it works.

Thank You, but could you ask your DB admin why I'm getting this error when trying to run this:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.

[taeratrin]

Location would be under tblADComputers

[ESIT]

taeratrin wrote:
Location would be under tblADComputers

That you, that worked, but here's my next question.

How do I get one report with what I need above? I've tried adding other tables and views that have the needed info and tried linking them, but the report always comes up blank unless I only have one table.

Thanks again!