Help with a report

[e5franson]

Hey all. I'm trying to get a few reports setup, but I have almost 0 knowledge of SQL syntax (its on my list to learn more of). I've got my 1st query here that's just pulling some hardware info for all of my computers. I have a second query that is pulling the OU for each machine. I'd like to combine these into one so its nice and pretty. Any help is greatly appreciated.

SELECT  distinctCS.name0 as 'Computer Name',CS.domain0 as 'Domain',CS.UserName0 as 'User',BIOS.SerialNumber0 as 'Bios serial',SE.SerialNumber0 as 'System Enclosure serial',CS.Manufacturer0 as 'Manufacturer',CS.Model0 as 'model',OS.Caption0 as 'OS',OS.LastBootUpTime0 as 'Last Boot Time',RAA.SMS_Assigned_Sites0 as 'Site',RAM.TotalPhysicalMemory0 as 'Total Memory',sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size',sum(isnull(LDisk.FreeSpace0,'0')) AS 'Free Space',CPU.MaxClockSpeed0 as 'Max CPU Speed',CPU.Name0 as 'CPU Model',CPU.Is64Bit0 as '64 Bit Compatible'from   v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceIDright join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceIDright join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceIDright join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceIDright join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID   right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceIDwhereLDisk.DriveType0 =3group byCS.Name0,CS.domain0,CS.Username0,BIOS.SerialNumber0,SE.SerialNumber0,CS.Manufacturer0,CS.Model0,OS.Caption0,OS.LastBootUpTime0,RAA.SMS_Assigned_Sites0,RAM.TotalPhysicalMemory0,CPU.MaxClockSpeed0,CPU.Name0,CPU.Is64Bit0ORDER BY CS.name0 select sys.Netbios_Name0,     (select top 1 ou2.System_OU_Name0 from v_RA_System_SystemOUName ou2     where ou.ResourceID = ou2.ResourceID and LEN(ou2.System_OU_Name0) = MAX(LEN(ou.System_OU_Name0))) OUfrom v_R_System_Valid sysinner join v_RA_System_SystemOUName ou on sys.ResourceID = ou.ResourceIDgroup by sys.Netbios_Name0, ou.ResourceID--------------------------------------------------------------------------------

SELECT     dbo.v_R_System.Name0 AS [Computer Name], A.System_OU_Name0FROM         dbo.v_RA_System_SystemOUName AS A INNER JOIN                          (SELECT     ResourceID, MAX(LEN(System_OU_Name0)) AS len                            FROM          dbo.v_RA_System_SystemOUName                            GROUP BY ResourceID) AS B ON A.ResourceID = B.ResourceID AND LEN(A.System_OU_Name0) = B.len INNER JOIN                      dbo.v_R_System ON B.ResourceID = dbo.v_R_System.ResourceID

[e5franson]

I found a report similar to what I was looking for on another forum. I modified it slightly to fit my needs, but if anyone else is looking heres the query:

 

select distinct
v_R_System_Valid.ResourceID,
v_R_System_Valid.Netbios_Name0 AS [Computer Name],
v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],
v_Site.SiteName as [sMS Site Name],
[Top Console User] = CASE
when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1')
then 'Unknown'
Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0
End,
max(v_RA_SYSTEM_SystemOUName.System_OU_Name0) AS [systemOUName],
v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
v_GS_OPERATING_SYSTEM.LastBootUpTime0 AS [Last Boot Time],
v_GS_OPERATING_SYSTEM.CSDVersion0 AS [service Pack Level],
v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [serial Number],
v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag],
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer],
v_GS_COMPUTER_SYSTEM.Model0 AS [Model],
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 / 1024 AS [Memory (MBytes)],
v_GS_PROCESSOR.Name0 AS [Name0],
v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)],
(Select sum(Size0)
from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )
where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and
v_FullCollectionMembership.CollectionID = @CollectionID) As [Disk Space (MB)],
(Select sum(v_GS_LOGICAL_DISK.FreeSpace0)
from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )
where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Free Disk Space (MB)]
from v_R_System_Valid
inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)
left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID)
LEFT join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)
LEFT join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID)
LEFT join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID)
inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID)
left join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode)
LEFT join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2)
left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID)
left join v_RA_SYSTEM_SystemOUName on (v_RA_SYSTEM_SystemOUName.ResourceID = v_R_System_Valid.ResourceID)
Where v_FullCollectionMembership.CollectionID = @CollectionID
group by v_R_System_Valid.ResourceID, v_R_System_Valid.Netbios_Name0, v_R_System_Valid.Resource_Domain_OR_Workgr0, v_Site.SiteName, v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0, v_GS_OPERATING_SYSTEM.Caption0,v_GS_OPERATING_SYSTEM.LastBootUpTime0, v_GS_OPERATING_SYSTEM.CSDVersion0,v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0, v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0, v_GS_COMPUTER_SYSTEM.Manufacturer0, v_GS_COMPUTER_SYSTEM.Model0, v_GS_X86_PC_MEMORY.TotalPhysicalMemory0, v_GS_PROCESSOR.Name0, v_GS_PROCESSOR.NormSpeed0
Order by v_R_System_Valid.Netbios_Name0