Thanks again, Garth. This is what I needed!
Best Regards, Mike Murray Desktop Management Coordinator - IT Support Services California State University, Chico 530.898.4357 [email protected]<mailto:[email protected]> ________________________________ From: [email protected] <[email protected]> on behalf of Murray, Mike <[email protected]> Sent: Tuesday, March 24, 2015 1:15 PM To: [email protected] Subject: [mssms] RE: Help with SQL for report It appears removing RV.Description0 fixed the problem. Is there another way to grab that field? From: [email protected] [mailto:[email protected]] On Behalf Of Garth Jones Sent: Tuesday, March 24, 2015 1:10 PM To: [email protected] Subject: [mssms] RE: Help with SQL for report I did a quick look at one of my clean test labs and System description is clearly not within the Out of the Box SQL view. However it is within my main lab and Mike’s environment. ;-) From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Atkinson, Matt T Sent: Tuesday, March 24, 2015 4:04 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Help with SQL for report I had a small error with the query provided by Garth, had to remove RV.Description0 from the select statement before it would run (doesn’t exist, unless I’m missing something). Once I did that it ran fine. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Garth Jones Sent: Tuesday, March 24, 2015 12:45 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Help with SQL for report Where exactly are you seeing that… I ran this query within SSMS and it run without any errors, so help me understand what you are trying to do. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Murray, Mike Sent: Tuesday, March 24, 2015 3:25 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Help with SQL for report Hi Garth, thank you! I’m getting this error when I save or run: “An item with the same key has already been added” Thanks, Mike From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Garth Jones Sent: Tuesday, March 24, 2015 12:07 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Help with SQL for report Here is a cleanup fixed up and following best practices version of this query. select distinct RV.ResourceID, BIOS.SerialNumber0, SEU.SMBIOSAssetTag0, RV.Netbios_Name0, RV.Client_Version0, RV.Distinguished_Name0, OS.Caption0, OS.CSDVersion0, RV.Last_Logon_Timestamp0, WS.LastHWScan, CS.Manufacturer0, CS.Model0, CS.TotalPhysicalMemory0, OS.InstallDate0, OS.LastBootUpTime0, SCU.TopConsoleUser0, RV.User_Name0,RV.User_Domain0, BIOS.SMBIOSBIOSVersion0, EV.DriveLetter0, EV.ProtectionStatus0, OS.Description0, RV.Description0 from dbo.v_R_System AS RV LEFT OUTER JOIN dbo.v_RA_System_SystemOUName AS RASSOUN ON RV.ResourceID = RASSOUN.ResourceID LEFT OUTER JOIN dbo.v_GS_COMPUTER_SYSTEM AS CS ON CS.ResourceID = RV.ResourceID LEFT OUTER JOIN dbo.v_GS_OPERATING_SYSTEM AS OS ON OS.ResourceID = RV.ResourceID LEFT OUTER JOIN dbo.v_GS_PC_BIOS AS BIOS ON BIOS.ResourceID = RV.ResourceID LEFT OUTER JOIN dbo.v_GS_SYSTEM_CONSOLE_USAGE AS SCU ON SCU.ResourceID = RV.ResourceID LEFT OUTER JOIN dbo.v_GS_ENCRYPTABLE_VOLUME AS EV ON EV.ResourceID = RV.ResourceID LEFT OUTER JOIN dbo.v_GS_WORKSTATION_STATUS AS WS ON WS.ResourceID = RV.ResourceID left outer join dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE SEU ON RV.ResourceID = SEU.ResourceID order by RV.Netbios_Name0 From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Atkinson, Matt T Sent: Tuesday, March 24, 2015 2:41 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Help with SQL for report I also tried to run it in SSMS and it looks like some of the column names are incorrect: SMS_R_System.operatingSystem0 should be SMS_R_System.Operating_System_Name_And0, etc. [cid:[email protected]] From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Murray, Mike Sent: Tuesday, March 24, 2015 11:23 AM To: [email protected]<mailto:[email protected]> Subject: [mssms] Help with SQL for report I’m trying to use this to create a report. When I run the report, I get: * An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand) Here’s the SQL: select distinct SMS_R_System.ItemKey,SMS_G_System_PC_BIOS.SerialNumber00,v_GS_SYSTEM_ENCLOSURE_UNIQUE.AssetTag,SMS_R_System.Name0,SMS_R_System.Client_Version0,SMS_R_System.Distinguished_Name0,SMS_R_System.operatingSystem0,SMS_R_System.operatingSystemServicePac0,SMS_R_System.Last_Logon_Timestamp0,___System_WORKSTATION_STATUS3.LastHWScan,SMS_G_System_COMPUTER_SYSTEM.Manufacturer00,SMS_G_System_COMPUTER_SYSTEM.Model00,SMS_G_System_COMPUTER_SYSTEM.TotalPhysicalMemory00,SMS_G_System_OPERATING_SYSTEM.InstallDate00,SMS_G_System_OPERATING_SYSTEM.LastBootUpTime00,__ystem_SYSTEM_CONSOLE_USAGE1.TopConsoleUser00,SMS_R_System.User_Name0,SMS_R_System.User_Domain0,SMS_G_System_PC_BIOS.SMBIOSBIOSVersion00,___System_ENCRYPTABLE_VOLUME2.DriveLetter00,___System_ENCRYPTABLE_VOLUME2.ProtectionStatus00,SMS_G_System_OPERATING_SYSTEM.Description00,SMS_R_System.description0 from vSMS_R_System AS SMS_R_System LEFT OUTER JOIN System_System_OU_Name_ARR AS __mSystem_System_OU_Name_ARR0 ON SMS_R_System.ItemKey = __mSystem_System_OU_Name_ARR0.ItemKey LEFT OUTER JOIN Computer_System_DATA AS SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.MachineID = SMS_R_System.ItemKey LEFT OUTER JOIN Operating_System_DATA AS SMS_G_System_OPERATING_SYSTEM ON SMS_G_System_OPERATING_SYSTEM.MachineID = SMS_R_System.ItemKey LEFT OUTER JOIN PC_BIOS_DATA AS SMS_G_System_PC_BIOS ON SMS_G_System_PC_BIOS.MachineID = SMS_R_System.ItemKey LEFT OUTER JOIN SYSTEM_CONSOLE_USAGE_DATA AS __ystem_SYSTEM_CONSOLE_USAGE1 ON __ystem_SYSTEM_CONSOLE_USAGE1.MachineID = SMS_R_System.ItemKey LEFT OUTER JOIN ENCRYPTABLE_VOLUME_DATA AS ___System_ENCRYPTABLE_VOLUME2 ON ___System_ENCRYPTABLE_VOLUME2.MachineID = SMS_R_System.ItemKey LEFT OUTER JOIN WorkstationStatus_DATA AS ___System_WORKSTATION_STATUS3 ON ___System_WORKSTATION_STATUS3.MachineID = SMS_R_System.ItemKey where (SMS_R_System.Obsolete0 = N'0') order by name0 Best Regards, Mike Murray Desktop Management Coordinator - IT Support Services California State University, Chico 530.898.4357 [email protected]<mailto:[email protected]> ________________________________ This message is intended for the sole use of the addressee, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message. ________________________________ This message is intended for the sole use of the addressee, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message.
