→ 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: 
usfitdci
Engaged Sweeper
Trying to create a report with the following columns:
Server Name
Domain
Tech Contact (Created in Custom Field)
OS
Model
Local Drives (up to 9)
# of CPU
Processor Speed
Total Memory
Symantec Version
TSM Version
OU

I get through model that's were it brakes. I've gotten the software and OU too. The drives, CPU and Memory where it continues to brake on me. Here is what I got so far:

Select Top 1000000 tblComputers.Computer, tblComputers.Domain,
tblCompCustom.Custom2 As [Tech Contact], tblADComputers.OperatingSystem,
tblComputersystem.Model
From tblComputers Inner Join
tblCompCustom On tblComputers.Computername = tblCompCustom.Computername
Inner Join
tblADComputers On tblComputers.Computername = tblADComputers.Computername
Inner Join
tblComputersystem On tblComputers.Computername =
tblComputersystem.Computername
Order By tblComputers.Computer

Thanks for your help.

dc
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
usfitdci wrote:
Here is my code, so you can take a look at it. I'm getting multipe entries on the servers. And is there a way on the hard drive information to make them there own columns?


Your code is ok, you can't display the drives as separate columns because there are multiple records in the database.

View solution in original post

4 REPLIES 4
usfitdci
Engaged Sweeper
Here is my code, so you can take a look at it. I'm getting multipe entries on the servers. And is there a way on the hard drive information to make them there own columns? Appreciate your help.

Select Top 1000000 dbo.tblComputers.Computer, dbo.tblComputers.Domain, tblCompCustom.Custom2 As [Tech Contact], tblADComputers.OperatingSystem, tblComputersystem.Model, dbo.tblDiskdrives.Caption As Drive, Cast(Cast(dbo.tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As [Free Space], Cast(Cast(dbo.tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As [Total size], tblComputersystem.TotalPhysicalMemory As [Total Physical Memory], tblComputersystem1.NumberOfProcessors As [# of Processors], tblPROCESSOR.Name As [Processor Name], web40usedsoftware.softwareVersion As [Symantec Version], web40usedsoftware1.softwareVersion As [TSM Version], tblADComputers1.OU, tblCompCustom1.LastPatched From dbo.tblComputers Inner Join dbo.tblDiskdrives On dbo.tblComputers.Computername = dbo.tblDiskdrives.Computername Inner Join dbo.tblOperatingsystem On dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername Inner Join dbo.web40ActiveComputers On dbo.tblComputers.Computername = dbo.web40ActiveComputers.Computername Inner Join tblComputersystem On dbo.tblComputers.Computername = tblComputersystem.Computername Inner Join tblCompCustom On dbo.tblComputers.Computername = tblCompCustom.Computername Inner Join tblADComputers On dbo.tblComputers.Computername = tblADComputers.Computername Inner Join tblComputersystem tblComputersystem1 On dbo.tblComputers.Computername = tblComputersystem1.Computername Inner Join tblPROCESSOR On dbo.tblComputers.Computername = tblPROCESSOR.Computername Inner Join tblADComputers tblADComputers1 On dbo.tblComputers.Computername = tblADComputers1.Computername Inner Join tblCompCustom tblCompCustom1 On dbo.tblComputers.Computername = tblCompCustom1.Computername, web40usedsoftware, web40usedsoftware web40usedsoftware1 Where Cast(Cast(dbo.tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) <> 0 And dbo.tblDiskdrives.DriveType = '3' And web40usedsoftware.softwareName = 'Symantec Endpoint Protection' And web40usedsoftware1.softwareName = 'IBM Tivoli Storage Manager Client' Order By dbo.tblComputers.Computer, Cast(Cast(dbo.tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric), dbo.tblDiskdrives.Caption
Hemoco
Lansweeper Alumni
usfitdci wrote:
Here is my code, so you can take a look at it. I'm getting multipe entries on the servers. And is there a way on the hard drive information to make them there own columns?


Your code is ok, you can't display the drives as separate columns because there are multiple records in the database.
usfitdci
Engaged Sweeper
I get a number of the same server entries. Like I will have 30 of server1. Or I'll get a blank page.

dc
Hemoco
Lansweeper Alumni
What exactly do you mean with "it brakes"

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now