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]] On
Behalf Of Atkinson, Matt T
Sent: Tuesday, March 24, 2015 2:41 PM
To: [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.
