Conducting an audit and I need to add the 'PhysicalDeliveryOfficeName' (Active Directory attribute) to a pre-existing SCCM2012 report. The report gives me everything I need except the 'PhysicalDeliveryOfficeName'. Under system discovery, I have already added this attribute so SCCM2012 scans it. Could someone please tell me how I can add the PhysicalDeliveryOfficeName to my report so when I enter in the collection ID, it will give me the information I need as well as the PhysicalDeliveryOfficeName?
If I make a query, I can find the attribute but I have to make it 'like' or 'similiar to', but I just want it to display whatever attribute is in the PhsyicalDeliveryOfficeName. For example :
select * from SMS_R_System where SMS_R_System.physicalDeliveryOfficeName = ""
I cannot find how to add this active directory attribute for the life of me! Any help is greatly appreciated,
Thanks to everyone in advance!
select distinctdbo.v_R_System_Valid.ResourceID,
dbo.v_R_System_Valid.Netbios_Name0 AS [Computer Name],
dbo.v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],
dbo.v_Site.SiteName as [SMS Site Name],
[Top Console User] = CASE
when (dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1')
then 'Unknown'
Else dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0
End,
dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],
dbo.v_GS_PC_BIOS.SerialNumber0 AS [Serial Number],
dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag],
dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer],
dbo.v_GS_COMPUTER_SYSTEM.Model0 AS [Model],
dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)],
dbo.v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)],
(Select sum(Size0)
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_Valid.ResourceID and
dbo.v_FullCollectionMembership.CollectionID = @CollectionID) As [Disk Space (MB)],
(Select sum(dbo.v_GS_LOGICAL_DISK.FreeSpace0)
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_Valid.ResourceID and dbo.v_FullCollectionMembership.CollectionID = @CollectionID) As [Free Disk Space (MB)]
from dbo.v_R_System_Valid
inner join dbo.v_GS_OPERATING_SYSTEM on (dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System_Valid.ResourceID)
left join dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE on (dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = dbo.v_R_System_Valid.ResourceID)
inner join dbo.v_GS_COMPUTER_SYSTEM on (dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System_Valid.ResourceID)
inner join dbo.v_GS_X86_PC_MEMORY on (dbo.v_GS_X86_PC_MEMORY.ResourceID = dbo.v_R_System_Valid.ResourceID)
inner join dbo.v_GS_PROCESSOR on (dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System_Valid.ResourceID)
inner join dbo.v_GS_PC_BIOS on (dbo.v_GS_PC_BIOS.ResourceID = dbo.v_R_System_Valid.ResourceID)
inner join dbo.v_FullCollectionMembership on (dbo.v_FullCollectionMembership.ResourceID = dbo.v_R_System_Valid.ResourceID)
Left join dbo.v_Site on (dbo.v_FullCollectionMembership.SiteCode = dbo.v_Site.SiteCode)
inner join dbo.v_GS_LOGICAL_DISK on (dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_R_System_Valid.ResourceID) and dbo.v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(dbo.v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2)
left join dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = dbo.v_R_System_Valid.ResourceID)
Order by dbo.v_R_System_Valid.Netbios_Name0
- Edited by Sam from IT 21 hours 33 minutes ago
Other recent topics
