locked
WQL to SQL RRS feed

  • Question

  • Hi There

    I have created this query on SCCM:

    select SMS_R_System.Name, SMS_G_System_COMPUTER_SYSTEM.UserName, SMS_R_System.ADSiteName from  SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ENCRYPTABLE_VOLUME on SMS_G_System_ENCRYPTABLE_VOLUME.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows 7 Enterprise" and SMS_G_System_ENCRYPTABLE_VOLUME.ProtectionStatus = 0


    And i tried to change it to SQL query :

    
    
    select v_R_System.Name, v_GS_COMPUTER_SYSTEM.UserName, v_R_System.ADSiteName from  v_R_System inner join v_GS_OPERATING_SYSTEM on v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceId inner join v_GS_ENCRYPTABLE_VOLUME on v_GS_ENCRYPTABLE_VOLUME.ResourceID = v_R_System.ResourceId inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId where v_GS_OPERATING_SYSTEM.Caption0 = 'Microsoft Windows 7 Enterprise' and v_GS_ENCRYPTABLE_VOLUME.ProtectionStatus = 0

    But SQL report builder doesn't get it and  errors :

    Invalid column name 'ProtectionStatus'.
    Invalid column name 'Name'.
    Invalid column name 'UserName'.
    Invalid column name 'ADSiteName'.

    Can someone help ?

    Thanks!!


    My Website:www.Pelegit.co.il Mcitp /Mcsa 2012



    • Edited by Meir Peleg Wednesday, March 9, 2016 8:31 PM
    Wednesday, March 9, 2016 8:14 PM

Answers

  • You need to spend some time within SQL Server Management Studio to look up the column names.

    All of those column name have an "0" afterward in SQL.


    Garth Jones

    Blog: http://www.enhansoft.com/blog Old Blog: http://smsug.ca/blogs/garth_jones/default.aspx

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleased

    Wednesday, March 9, 2016 9:00 PM
  • Never Mind I managed guys thanks :

    select v_R_System.Name0, v_GS_COMPUTER_SYSTEM.UserName0, AD_Site_Name0 from v_R_System inner join v_GS_OPERATING_SYSTEM on v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceId inner join v_GS_ENCRYPTABLE_VOLUME on v_GS_ENCRYPTABLE_VOLUME.ResourceID = v_R_System.ResourceId inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId where v_GS_OPERATING_SYSTEM.Caption0 = 'Microsoft Windows 7 Enterprise' and v_GS_ENCRYPTABLE_VOLUME.ProtectionStatus0=0

    • Edited by Meir Peleg Thursday, March 10, 2016 10:04 AM
    • Proposed as answer by Garth JonesMVP Saturday, April 8, 2017 2:23 PM
    • Marked as answer by Garth JonesMVP Tuesday, June 20, 2017 9:43 PM
    Thursday, March 10, 2016 6:00 AM

All replies

  • You need to spend some time within SQL Server Management Studio to look up the column names.

    All of those column name have an "0" afterward in SQL.


    Garth Jones

    Blog: http://www.enhansoft.com/blog Old Blog: http://smsug.ca/blogs/garth_jones/default.aspx

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleased

    Wednesday, March 9, 2016 9:00 PM
  • Never Mind I managed guys thanks :

    select v_R_System.Name0, v_GS_COMPUTER_SYSTEM.UserName0, AD_Site_Name0 from v_R_System inner join v_GS_OPERATING_SYSTEM on v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceId inner join v_GS_ENCRYPTABLE_VOLUME on v_GS_ENCRYPTABLE_VOLUME.ResourceID = v_R_System.ResourceId inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId where v_GS_OPERATING_SYSTEM.Caption0 = 'Microsoft Windows 7 Enterprise' and v_GS_ENCRYPTABLE_VOLUME.ProtectionStatus0=0

    • Edited by Meir Peleg Thursday, March 10, 2016 10:04 AM
    • Proposed as answer by Garth JonesMVP Saturday, April 8, 2017 2:23 PM
    • Marked as answer by Garth JonesMVP Tuesday, June 20, 2017 9:43 PM
    Thursday, March 10, 2016 6:00 AM