big report

[bloodybearr]

hi there, im kinda new in sccm, and in creating custom reports.

 

actualy i need a huuuuuge information in one report.

 

-netbios name

-ad container

-current user

-top console user

-vendor

-model

-processor type

-numer of processor cores

-GHz of processor

-how many ram is there

-os version and name

-video card model and its ram

-hdd type and size

-hardware production date (when created)

-last logon

 

 

i tryed to do something, but its insane to understood how its works and writes

oh, forgot to tell that im using sccm 2007

 

 

got some code already with half of everything, but i still getting error "Must Declarare the scallar Variable @CollectionID"

if i remove this lines

 

rsys.Name0 AS 'Computer Name', Caption0 as 'Operating System',

CSDVersion0 as 'Service Pack',

and

inner join v_GS_OPERATING_SYSTEM os ON rsys.ResourceID=os.ResourceID

inner join v_FullCollectionMembership fcm on os.ResourceID=fcm.ResourceID

WHERE

fcm.CollectionID=@CollectionID

 

everything goes great.

and if i put that lanes in different sections (2xSelect and from) everything still working great

 

SELECT
V_R_SYSTEM.Name0 AS 'PC Name',
rsys.Name0 AS 'Computer Name', Caption0 as 'Operating System',
CSDVersion0 as 'Service Pack',
v_GS_PROCESSOR.Name0 AS 'Processor',
v_GS_COMPUTER_SYSTEM.Manufacturer0,
v_GS_COMPUTER_SYSTEM.Model0,v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS 'RAM (MB)'
FROM
v_r_system
inner join v_GS_PROCESSOR on v_R_system.ResourceID=v_GS_PROCESSOR.ResourceID
inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID=v_r_system.ResourceID
inner join v_GS_X86_PC_MEMORY on v_GS_X86_PC_MEMORY.ResourceID=v_r_system.ResourceID
inner join v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID=v_r_system.ResourceID
inner join v_GS_OPERATING_SYSTEM os ON rsys.ResourceID=os.ResourceID
inner join v_FullCollectionMembership fcm on os.ResourceID=fcm.ResourceID
WHERE
fcm.CollectionID=@CollectionID

Edited August 24, 2012 by bloodybearr

[bloodybearr]

still cant get something to work. everything getting alot of errors and i cant understood why

 

after long time of searching i got this code

but still dont have some types what i need

-vendor

-number of CPU cores

-video card model and ram

-hdd type

-hardware creation date

-last logon time

 

and also giving me not needed information

-serial number

 

other things seems to be usefull.

maybe can someone help ? :[

 

 

SELECT DISTINCT
				 TOP (100) PERCENT dbo.v_R_System.Netbios_Name0 AS [Computer Name],
				 dbo.v_R_System.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], dbo.v_R_System.User_Name0 AS [user Name],
				 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 AS [Top Console User],
				 dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [service Pack Level],
				 dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model,
				 dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [serial Number],
				 dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)], dbo.v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)],
				 dbo.v_GS_PROCESSOR.Name0 AS [Processor Type],
					 (SELECT	 SUM(dbo.v_GS_LOGICAL_DISK.Size0) AS Expr1
					 FROM		 dbo.v_GS_LOGICAL_DISK INNER JOIN
											 dbo.v_FullCollectionMembership ON dbo.v_FullCollectionMembership.ResourceID = dbo.v_GS_LOGICAL_DISK.ResourceID
					 WHERE	 (dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_R_System.ResourceID) AND
											 (dbo.v_FullCollectionMembership.CollectionID = 'collectionID')) AS [Disk Space (MB)],
					 (SELECT	 SUM(v_GS_LOGICAL_DISK_2.FreeSpace0) AS Expr1
					 FROM		 dbo.v_GS_LOGICAL_DISK AS v_GS_LOGICAL_DISK_2 INNER JOIN
											 dbo.v_FullCollectionMembership AS v_FullCollectionMembership_2 ON
											 v_FullCollectionMembership_2.ResourceID = v_GS_LOGICAL_DISK_2.ResourceID
					 WHERE	 (v_GS_LOGICAL_DISK_2.ResourceID = dbo.v_R_System.ResourceID) AND (v_FullCollectionMembership_2.CollectionID = 'CollectionID'))
				 AS [Free Disk Space (MB)], dbo.v_GS_WORKSTATION_STATUS.LastHWScan AS [Last Hardware Scan]
FROM		 dbo.v_R_System INNER JOIN
				 dbo.v_FullCollectionMembership AS v_FullCollectionMembership_1 ON
				 v_FullCollectionMembership_1.ResourceID = dbo.v_R_System.ResourceID LEFT OUTER JOIN
				 dbo.v_GS_X86_PC_MEMORY ON dbo.v_GS_X86_PC_MEMORY.ResourceID = dbo.v_R_System.ResourceID LEFT OUTER JOIN
				 dbo.v_GS_OPERATING_SYSTEM ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System.ResourceID LEFT OUTER JOIN
				 dbo.v_GS_WORKSTATION_STATUS ON dbo.v_GS_WORKSTATION_STATUS.ResourceID = dbo.v_R_System.ResourceID LEFT OUTER JOIN
				 dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE ON
				 dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = dbo.v_R_System.ResourceID LEFT OUTER JOIN
				 dbo.v_GS_LOGICAL_DISK AS v_GS_LOGICAL_DISK_1 ON v_GS_LOGICAL_DISK_1.ResourceID = dbo.v_R_System.ResourceID AND
				 v_GS_LOGICAL_DISK_1.DeviceID0 = SUBSTRING(dbo.v_GS_OPERATING_SYSTEM.WindowsDirectory0, 1, 2) LEFT OUTER JOIN
				 dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID LEFT OUTER JOIN
				 dbo.v_GS_PROCESSOR ON dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System.ResourceID LEFT OUTER JOIN
				 dbo.v_Site ON v_FullCollectionMembership_1.SiteCode = dbo.v_Site.SiteCode LEFT OUTER JOIN
				 dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP ON
				 dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = dbo.v_R_System.ResourceID
WHERE	 (v_FullCollectionMembership_1.CollectionID = 'CollectionID')
ORDER BY [Computer Name]

[bloodybearr]

found another way to do what i need with query.

thread can be closed.

[fr0zen]

how?

[GarthMJ]

The query above works.... So Fr0zen, how to what?

[bdazle]

This should help, this is one we use for out true-up for server modelling :

 

SELECT

distinct CS.Name0 as 'Host Name',

AD.AD_Site_name0 AS 'Site name',

BI.DisplayName as 'Site Description',

CS.Manufacturer0 as 'Manufacturer',

CS.Model0 as 'Model',

BIOS.SerialNumber0 as 'Bios Serial',

OS.Caption0 as 'Operating System',

OPSYS.CSDVersion0 as 'SP Level',

CPU.Is64Bit0 as '64 Bit Compatible' ,

CPU2.CPUSockets as 'CPU Socket Count',

cl.numberofprocessors0 as 'Number of Logical Processors',

RAM.TotalPhysicalMemory0/1024 as 'RAM (MB)',

OPSYS.InstallDate0 as 'OS Install Date',

OPSYS.LastBootUpTime0 as 'Last Boot',

Addes.Description0 AS 'AD Description',

OPSYS.Description0 AS 'Server Description'

from

v_GS_COMPUTER_SYSTEM CS

right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID

right join v_R_System AD on AD.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_PROCESSOR CPU on CPU.ResourceID = CS.ResourceID

right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID

right join v_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID

right join v_R_System Addes on Addes.ResourceID = CS.ResourceID

right join v_GS_LOGICAL_DISK LDisk on LDisk.ResourceID = CS.ResourceID

right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID

left join v_BoundaryInfo BI on AD.AD_Site_Name0 = BI.Value

right join v_GS_OPERATING_SYSTEM OPSYS on OPSYS.ResourceID = CS.ResourceID

right join v_FullCollectionMembership FCM on FCM.ResourceID = CS.ResourceID

right join (select cpu1.ResourceID, cpu1.SystemName0, Count(cpu1.SocketDesignation0) as 'CPUSockets' from (select ResourceID, SystemName0, SocketDesignation0 from v_GS_processor group by ResourceID, SystemName0, SocketDesignation0) cpu1 group by cpu1.ResourceID, cpu1.SystemName0) CPU2 ON CPU2.ResourceID = CS.ResourceID

INNER JOIN dbo.v_GS_Computer_System as cl

ON sys.resourceID = cl.resourceID

where LDisk.DriveType0 =3 and SYS.SystemRole0 = 'Server'

 

group by CS.Name0, ad.AD_Site_Name0, BI.DisplayName, CS.Domain0, CS.UserName0, BIOS.SerialNumber0, SE.SerialNumber0, CS.Manufacturer0, CS.Model0,

OS.Caption0, RAA.SMS_Assigned_Sites0, RAM.TotalPhysicalMemory0, CPU.CurrentClockSpeed0, CPU2.CPUSockets, FCM.SiteCode,

OPSYS.Caption0, OPSYS.Version0, CPU.Is64Bit0, OPSYS.CSDVersion0, OPSYS.InstallDate0, OPSYS.LastBootUpTime0, OPSYS.WindowsDirectory0, Addes.Description0, OPSYS.Description0, cl.numberofprocessors0

[bloodybearr]

hello bump for this theme.

 

firstly i created a query that was limited to collection. and it working, but now its again needed to see this report on web report.

i re-writed and tryed to run this report, but i got an error:

 

An error occurred when the report was run. The details are as follows:

The SELECT permission was denied on the object '_RES_COLL_CEN00A66', database 'SMS_CEN', schema 'dbo'.

Error Number: -2147217911

Source: Microsoft OLE DB Provider for SQL Server

Native Error: 229

 

 

report code looks like:

 

select  all SMS_R_System.ItemKey,SMS_R_System.Netbios_Name0,SMS_R_System.User_Name0,__System_SYSTEM_CONSOLE_USER0.SystemConsoleUser00,SMS_G_System_VIDEO_CONTROLLER.Name00,SMS_G_System_VIDEO_CONTROLLER.AdapterRAM00,SMS_G_System_LastSoftwareScan.LastUpdateDate,SMS_G_System_DISK.InterfaceType0,SMS_G_System_DISK.Size0,SMS_G_System_OPERATING_SYSTEM.Name0,SMS_G_System_OPERATING_SYSTEM.Version0,SMS_G_System_OPERATING_SYSTEM.LastBootUpTime0,SMS_G_System_X86_PC_MEMORY.TotalPhysicalMemory0,SMS_G_System_COMPUTER_SYSTEM.Manufacturer00,SMS_G_System_COMPUTER_SYSTEM.Model0,SMS_G_System_COMPUTER_SYSTEM.NumberOfProcessors00,SMS_G_System_PROCESSOR.Name0,SMS_G_System_PROCESSOR.MaxClockSpeed0 from System_DISC AS SMS_R_System INNER JOIN SYSTEM_CONSOLE_USER_DATA AS __System_SYSTEM_CONSOLE_USER0 ON __System_SYSTEM_CONSOLE_USER0.MachineID = SMS_R_System.ItemKey  INNER JOIN Video_Controller_DATA AS SMS_G_System_VIDEO_CONTROLLER ON SMS_G_System_VIDEO_CONTROLLER.MachineID = SMS_R_System.ItemKey  INNER JOIN SoftwareInventoryStatus AS SMS_G_System_LastSoftwareScan ON SMS_G_System_LastSoftwareScan.ClientId = SMS_R_System.ItemKey  INNER JOIN Disk_DATA AS SMS_G_System_DISK ON SMS_G_System_DISK.MachineID = SMS_R_System.ItemKey  INNER JOIN Operating_System_DATA AS SMS_G_System_OPERATING_SYSTEM ON SMS_G_System_OPERATING_SYSTEM.MachineID = SMS_R_System.ItemKey  INNER JOIN PC_Memory_DATA AS SMS_G_System_X86_PC_MEMORY ON SMS_G_System_X86_PC_MEMORY.MachineID = SMS_R_System.ItemKey  INNER JOIN Computer_System_DATA AS SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.MachineID = SMS_R_System.ItemKey  INNER JOIN Processor_DATA AS SMS_G_System_PROCESSOR ON SMS_G_System_PROCESSOR.MachineID = SMS_R_System.ItemKey  INNER JOIN _RES_COLL_CEN00A66 AS SMS_CM_RES_COLL_CEN00A66 ON SMS_CM_RES_COLL_CEN00A66.MachineID = SMS_R_System.ItemKey  

 

any advice how to fix, or where is a problem ?

[bdazle]

"permission was denied on the object" do you have full reporting rights for your user account ?

[GarthMJ]

any advice how to fix, or where is a problem ?

 

You are getting access denied because you are querying the tables instead of the views, by default none of the reporting options within CM07 or CM12 have permissions to any of the table data. BTW, It is bad form to query the tables directly and can lead to table locking issue on a busy site.