you should use the 0 char when it's actually in your database like that. "most of" the time, the 0 char gets added when it's custom content. (hardware inventory customizations for example, it almost always happens). But there's no guarantees that's the ONLY time it could happen. ConfigMgr/SQL try to be as standard as possible--but different environments, different locale's, different sql configurations, all could be differentiators so that in order for ConfigMgr to communicate with SQL; some customizations that may or may not be unique to your specific environment "might" happen. So really... just look and see what those views and the columns in those views are actually called.
On Mon, Mar 21, 2016 at 1:17 PM, Murray, Mike <[email protected]> wrote: > That did the trick, thanks! So when should I be using the zero character? > > > > *From:* [email protected] [mailto: > [email protected]] *On Behalf Of *Andrew Craig > *Sent:* Monday, March 21, 2016 11:00 AM > *To:* [email protected] > *Subject:* [mssms] RE: Report help? > > > > This works for me. J > > > > You had zeroes on the column names where you shouldn’t have had… > > > > select distinct > > v_R_System_Valid.ResourceID, > > v_R_System_Valid.Netbios_Name0 AS [Computer Name], > > v_R_System_Valid.user_name0 AS [User 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, > > v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], > > v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level], > > v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number], > > v_GS_PC_BIOS.ReleaseDate0 AS [BIOS Release Date], > > 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 AS [Memory (KBytes)], > > 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)] , > > > > v_GS_Chico640.Chico_AssetTag0 as [Reg Asset], > > v_GS_Chico640.Chico_BuildingCode0 as [Building], > > v_GS_Chico640.Chico_RoomCode0 as [Room], > > v_GS_Chico640.Chico_BusinessArea0 as [Bus Area], > > v_GS_Chico640.OSD_Base0 as [OSD Base], > > v_GS_Chico640.OSD_Deploy0 as [OSD Deploy], > > v_GS_Chico640.Role_Admin0 as [Role Admin], > > v_GS_Chico640.Roles0 as [Roles], > > v_GS_NETWORK_ADAPTER_CONFIGURATION.Description0 AS [NIC Description], > > v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 AS [IP Address], > > v_GS_NETWORK_ADAPTER_CONFIGURATION.IPSubnet0 AS [Subnet Mask], > > v_GS_NETWORK_ADAPTER_CONFIGURATION.DefaultIPGateway0 AS [Gateway], > > v_CH_ClientSummary.LastDDR as [Heartbeat Disc], > > v_CH_ClientSummary.LastHW as [HW Scan], > > v_CH_ClientSummary.LastSW as [SW Scan], > > v_CH_ClientSummary.LastPolicyRequest as [Policy Req], > > v_CH_ClientSummary.LastStatusMessage as [Status Msg], > > v_CH_ClientSummary.LastActiveTime as [Active] > > 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) > > inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = > v_R_System_Valid.ResourceID) > > inner join v_GS_PC_BIOS on (v_GS_PC_BIOS.ResourceID = v_R_System_Valid. > ResourceID) > > inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = > v_R_System_Valid.ResourceID) > > inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid > .ResourceID) > > left join v_GS_Chico640 on (v_GS_Chico640.ResourceID = v_R_System_Valid. > ResourceID) > > inner join v_GS_NETWORK_ADAPTER_CONFIGURATION on ( > v_GS_NETWORK_ADAPTER_CONFIGURATION.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) > > inner 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_CH_ClientSummary on v_R_System_Valid.ResourceID = > v_CH_ClientSummary.ResourceID > > Where v_FullCollectionMembership.CollectionID = @CollectionID > > Order by v_R_System_Valid.Netbios_Name0 > > > > > > *From:* [email protected] [ > mailto:[email protected] <[email protected]>] *On > Behalf Of *Murray, Mike > *Sent:* 21 March 2016 18:43 > *To:* [email protected] > *Subject:* [mssms] Report help? > > > > Hey SQL gurus, can someone assist? I want to add the below columns to an > existing report. I’ve tried, but it’s not working. Existing report SQL is > listed further below. > > > > Thanks! > > > > New columns: > > > > v_CH_ClientSummary.LastDDR0 as [Heartbeat Disc], > > v_CH_ClientSummary.LastHW0 as [HW Scan], > > v_CH_ClientSummary.LastSW0 as [SW Scan], > > v_CH_ClientSummary.LastPolicyRequest0 as [Policy Req], > > v_CH_ClientSummary.LastStatusMessage0 as [Status Msg], > > v_CH_ClientSummary.LastActiveTime0 as [Active], > > > > > > Existing report: > > > > select distinct > > v_R_System_Valid.ResourceID, > > v_R_System_Valid.Netbios_Name0 AS [Computer Name], > > v_R_System_Valid.user_name0 AS [User 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, > > v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], > > v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level], > > v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number], > > v_GS_PC_BIOS.ReleaseDate0 AS [BIOS Release Date], > > 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 AS [Memory (KBytes)], > > 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)] , > > > > v_GS_Chico640.Chico_AssetTag0 as [Reg Asset], > > v_GS_Chico640.Chico_BuildingCode0 as [Building], > > v_GS_Chico640.Chico_RoomCode0 as [Room], > > v_GS_Chico640.Chico_BusinessArea0 as [Bus Area], > > v_GS_Chico640.OSD_Base0 as [OSD Base], > > v_GS_Chico640.OSD_Deploy0 as [OSD Deploy], > > v_GS_Chico640.Role_Admin0 as [Role Admin], > > v_GS_Chico640.Roles0 as [Roles], > > > > v_GS_NETWORK_ADAPTER_CONFIGURATION.Description0 AS [NIC Description], > > v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 AS [IP Address], > > v_GS_NETWORK_ADAPTER_CONFIGURATION.IPSubnet0 AS [Subnet Mask], > > v_GS_NETWORK_ADAPTER_CONFIGURATION.DefaultIPGateway0 AS [Gateway] > > > > > > 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) > > > > inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = > v_R_System_Valid.ResourceID) > > inner join v_GS_PC_BIOS on (v_GS_PC_BIOS.ResourceID = > v_R_System_Valid.ResourceID) > > inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = > v_R_System_Valid.ResourceID) > > inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = > v_R_System_Valid.ResourceID) > > > > left join v_GS_Chico640 on (v_GS_Chico640.ResourceID = > v_R_System_Valid.ResourceID) > > > > inner join v_GS_NETWORK_ADAPTER_CONFIGURATION on > (v_GS_NETWORK_ADAPTER_CONFIGURATION.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) > > inner 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) > > Where v_FullCollectionMembership.CollectionID = @CollectionID > > Order by v_R_System_Valid.Netbios_Name0 > > > > > > > > Best Regards, > > > > Mike Murray > > Desktop Management Coordinator - IT Support Services > > California State University, Chico > > 530.898.4357 > [email protected] > > > > > > > > -- Thank you, Sherry Kissinger My Parameters: Standardize. Simplify. Automate Blogs: http://www.mofmaster.com, http://mnscug.org/blogs/sherry-kissinger, http://www.smguru.org
