Creating SQL reports for CM 2012 in Report Builder 3

[hughmc]

Hi! So sql report writing would have to be the most confusing thing on the earth. And i've learnt mandarin!

 

I have this report and i want to add processor type, remove the domain and add AD Site then have it prompt for a collection ID when run.

 

Said report, i flogged off technet or from Garth's blog i can't recall now. it's perfect apart from the above requirements.

 

SELECT  distinct 
   CS.Manufacturer0 as 'Manufacturer',
   CS.Model0 as 'model',
     CASE 
         WHEN ES.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop'
         WHEN ES.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop'
         Else 'Unknown'
 END as 'Description',
  BIOS.SerialNumber0 as 'Bios serial',
  CS.name0 as 'Computer Name', 
  RAM.TotalPhysicalMemory0 as 'Total Memory', 
  sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size',
  OS.Caption0 as 'OS',
  CSDVersion0 as 'Service Pack',
  CS.UserName0 as 'User',
  CS.domain0 as 'Domain'
 from  
   v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
 right 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_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID 
 right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID 
 INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ES on SYS.ResourceID = ES.ResourceID
 where 
  LDisk.DriveType0 =3
 group by 
   CS.Manufacturer0,
   CS.Model0,
   ChassisTypes0,
   BIOS.SerialNumber0,
   CS.Name0, 
   RAM.TotalPhysicalMemory0,
   OS.Caption0, 
   CSDVersion0,
   CS.Username0,
   CS.domain0

 

I had another report working (including AD site and Processor info) but was struggling so bad with getting a collection prompt working i gave up on that and tried the above query out.

 

This is the other report that i found here but the double IP line thing is super annoying and i couldnt get the prompt to work with that one.

 

SELECT
A.Name0,
B.SerialNumber0,
A.Manufacturer0,
A.Model0, C.Name0 AS 'Processor',
D.TotalPhysicalMemory0 AS 'Memory (KBytes)',
E.Size0 AS 'Disk Size (MBytes)',
F.MACAddress0,
F.IPAddress0,
G.AD_Site_Name0 AS 'AD Site',
A.UserName0 AS 'Last user logged in',
H.Caption0 AS 'Operating System',
H.CSDVersion0 AS 'Service Pack',
G.Creation_Date0 AS 'Creationdate in SMS', I.LastHWScan

FROM
v_GS_COMPUTER_SYSTEM A,
v_GS_PC_BIOS B,
v_GS_PROCESSOR C,
v_GS_X86_PC_MEMORY D,
v_GS_DISK E,
v_GS_NETWORK_ADAPTER_CONFIGUR F,
v_R_System G,
v_GS_OPERATING_SYSTEM H,
v_GS_WORKSTATION_STATUS I

WHERE
A.ResourceID = B.ResourceID AND
A.ResourceID = C.ResourceID AND
A.ResourceID = D.ResourceID AND
A.ResourceID = E.ResourceID AND
A.ResourceID = F.ResourceID AND
A.ResourceID = G.ResourceID AND
A.ResourceID = H.ResourceID AND
A.ResourceID = I.ResourceID and

F.IPAddress0 is not NULL AND
F.IPAddress0 not like '0.0.0.0'

GROUP BY A.Name0, B.SerialNumber0, A.Manufacturer0, A.Model0, C.Name0, D.TotalPhysicalMemory0, E.Size0, F.IPAddress0, F.MACAddress0, G.AD_Site_Name0, A.UserName0, H.Caption0, H.CSDVersion0, G.Creation_Date0, I.LastHWScan

 

 

i'd been following these instructions to add a prompt to the above query which would have been great, you guys all make it sound so simple but it's absolutely the most confusing thing ive ever seen huhu. i actually got the prompt to work on this one but it's missing the processor and ad site etc

 

I've been trying to find out where i can see all this information or how to search for it or something. Stuff like this:

 

SELECT distinct

 

BIOS.SerialNumber0,
CS.Name0,
RAM.TotalPhysicalMemory0,
OS.Caption0,
CSDVersion0,
CS.Username0,
CS.domain0

 

where does that come from? why is it different to the 2nd query?

 

SELECT
A.Name0,
B.SerialNumber0,
A.Manufacturer0,

A.Model0, C.Name0 AS 'Processor',

 

If i want to add processor and ad site to that first one how on earth do i do it? Trying to combine different queries only yields truckloads of syntax errors. Also, can anyone explain to me how all these reports look like entirely different yet all work the same way? It's doing my head in!

 

Any help would be greatly appreciated!