locked
Converting WQL to SQL query RRS feed

  • Question

  • When I tried to convert WQL query to SQL report all columns appears except for IP address.  Please help.

    WQL:  select distinct SMS_R_System.Name, SMS_R_System.LastLogonUserName, SMS_R_System.IPAddresses, SMS_G_System_NETWORK_ADAPTER.Name from  SMS_R_System inner join SMS_G_System_NETWORK_ADAPTER_CONFIGURATION on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_NETWORK_ADAPTER on SMS_G_System_NETWORK_ADAPTER.ResourceID = SMS_R_System.ResourceId where SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress like "192.168.%" and SMS_G_System_NETWORK_ADAPTER.Name like "cisco%"

    SQL:  select  distinct SMS_R_System.ItemKey,SMS_R_System.Name0,SMS_R_System.User_Name0,SMS_G_System_NETWORK_ADAPTER.Name00 from vSMS_R_System AS SMS_R_System INNER JOIN Network_DATA AS __WORK_ADAPTER_CONFIGURATION0 ON __WORK_ADAPTER_CONFIGURATION0.MachineID = SMS_R_System.ItemKey  INNER JOIN Netcard_DATA AS SMS_G_System_NETWORK_ADAPTER ON SMS_G_System_NETWORK_ADAPTER.MachineID = SMS_R_System.ItemKey  INNER JOIN _RES_COLL_SMS00001 AS SMS_CM_RES_COLL_SMS00001 ON SMS_CM_RES_COLL_SMS00001.MachineID = SMS_R_System.ItemKey   where (__WORK_ADAPTER_CONFIGURATION0.IPAddress00 like N'192.168.%' AND SMS_G_System_NETWORK_ADAPTER.Name00 like N'cisco%')

    Tuesday, April 28, 2020 6:35 PM

Answers

  • I assumed that you are looking for Cisco NIC with and IP address of 192.168.*.*. Then using  supported views only. your query should be. Notice that I had to Joined the ServiceName on the NIC with the Service Name on the Config, otherwise you will get wrong results. 

    select distinct
    	R.Name0,
    	R.User_Name0,
    	NA.Name0,
    	NAC.IPAddress0
    from 
    	dbo.v_R_System R
    	join dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION NAC on NAC.ResourceID = R.ResourceID
    	join dbo.v_GS_NETWORK_ADAPTER NA on Na.ResourceID = R.ResourceID and NAC.ServiceName0 = NA.ServiceName0
    where 
    	NAC.IPAddress0 like N'192.168.%' 
    	AND NA.Name0 like N'cisco%'


    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

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

    • Marked as answer by Liam Matthew Thursday, April 30, 2020 11:59 PM
    Wednesday, April 29, 2020 1:33 PM

All replies

  • First off it is not supported to query the tables directly, instead only use the supported views. https://docs.microsoft.com/en-us/mem/configmgr/develop/core/understand/sqlviews/sql-server-views-configuration-manager

    Secondly,  VSMS_System is not a supported view. v_R_system is the correct view.

    What do you mean about IP Address, none is listed within the column. Are you saying that you don't know where SMS_R_System.IPAddresses is within the views? 

     


    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

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

    Tuesday, April 28, 2020 6:45 PM
  • Sir in WQL I have this view, initialize the query and get the converted sql parameters in SCCM.  However when I add the query to Report Builder I am getting without ip address instead I have Itemkey.  I quote "What do you mean about IP Address, none is listed within the column. Are you saying that you don't know where SMS_R_System.IPAddresses is within the views? "  yes Sir I don't know how to insert or do it.  Newbie here.  I am sorry.  Thanks!

    Tuesday, April 28, 2020 8:18 PM
  • So I have looked at your query and you are mixing and matching details within CM, which don't link together. 

    You are mixing discovery data and hardware inventory together, incorrectly even in your WQL query. 

    What exactly are you trying to do? 

    If you want to learn more about querying and reporting. You should check out this ebook. https://www.informit.com/store/system-center-configuration-manager-reporting-unleashed-9780134466811


    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

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

    Tuesday, April 28, 2020 8:36 PM
  • I managed to make it work.  I have read couple of basic introductions.  Thanks for your time.  

    select  distinct

    SMS_R_System.ItemKey,SMS_R_System.Name0,SMS_R_System.User_Name0,SMS_G_System_NETWORK_ADAPTER.Name00,IP.IPAddress0
    from v_GS_NETWORK_ADAPTER_CONFIGURATION, vSMS_R_System AS SMS_R_System 
    INNER JOIN Network_DATA AS __WORK_ADAPTER_CONFIGURATION0 ON __WORK_ADAPTER_CONFIGURATION0.MachineID = SMS_R_System.ItemKey  
    INNER JOIN Netcard_DATA AS SMS_G_System_NETWORK_ADAPTER ON SMS_G_System_NETWORK_ADAPTER.MachineID = SMS_R_System.ItemKey  
    INNER JOIN v_GS_NETWORK_ADAPTER_CONFIGURATION as IP on SMS_R_System.ItemKey = IP.ResourceID
    INNER JOIN _RES_COLL_SMS00001 AS SMS_CM_RES_COLL_SMS00001 ON SMS_CM_RES_COLL_SMS00001.MachineID = SMS_R_System.ItemKey   
    where (__WORK_ADAPTER_CONFIGURATION0.IPAddress00 like N'10.84.%' AND SMS_G_System_NETWORK_ADAPTER.Name00 like N'cisco%')

    • Edited by Liam Matthew Wednesday, April 29, 2020 12:42 PM Correct Variable
    Wednesday, April 29, 2020 11:54 AM
  • I managed to make it work.  I have read couple of basic introductions.  Thanks for your time.  

    select  distinct

    SMS_R_System.ItemKey,SMS_R_System.Name0,SMS_R_System.User_Name0,SMS_G_System_NETWORK_ADAPTER.Name00,IP.IPAddress0
    from v_GS_NETWORK_ADAPTER_CONFIGURATION, vSMS_R_System AS SMS_R_System 
    INNER JOIN Network_DATA AS __WORK_ADAPTER_CONFIGURATION0 ON __WORK_ADAPTER_CONFIGURATION0.MachineID = SMS_R_System.ItemKey  
    INNER JOIN Netcard_DATA AS SMS_G_System_NETWORK_ADAPTER ON SMS_G_System_NETWORK_ADAPTER.MachineID = SMS_R_System.ItemKey  
    INNER JOIN v_GS_NETWORK_ADAPTER_CONFIGURATION as IP on SMS_R_System.ItemKey = IP.ResourceID
    INNER JOIN _RES_COLL_SMS00001 AS SMS_CM_RES_COLL_SMS00001 ON SMS_CM_RES_COLL_SMS00001.MachineID = SMS_R_System.ItemKey   
    where (__WORK_ADAPTER_CONFIGURATION0.IPAddress00 like N'10.84.%' AND SMS_G_System_NETWORK_ADAPTER.Name00 like N'cisco%')

    First off it is COMPLETELY unsupported to use that method to convert WQL to SQL. As you previously posted. https://msendpointmgr.com/2017/12/18/how-to-use-smsprov-log-to-build-reports/

    Secondly, your orginal query WQL doesn't find the IP address of Cisco device, it find all devices with an 192.168.*.* address, which have a Cisco nic. I'm sure that is NOT what you are looking for. 

    As stated this updated query is completely unsupported, Plus is again doesn't join the NICs to the NIC Config. So again, I'm sure this is NOT what you are looking for. 

    So again are you trying to find all CISCO NICs with a particular IP range? 


    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

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

    Wednesday, April 29, 2020 12:55 PM
  • I assumed that you are looking for Cisco NIC with and IP address of 192.168.*.*. Then using  supported views only. your query should be. Notice that I had to Joined the ServiceName on the NIC with the Service Name on the Config, otherwise you will get wrong results. 

    select distinct
    	R.Name0,
    	R.User_Name0,
    	NA.Name0,
    	NAC.IPAddress0
    from 
    	dbo.v_R_System R
    	join dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION NAC on NAC.ResourceID = R.ResourceID
    	join dbo.v_GS_NETWORK_ADAPTER NA on Na.ResourceID = R.ResourceID and NAC.ServiceName0 = NA.ServiceName0
    where 
    	NAC.IPAddress0 like N'192.168.%' 
    	AND NA.Name0 like N'cisco%'


    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

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

    • Marked as answer by Liam Matthew Thursday, April 30, 2020 11:59 PM
    Wednesday, April 29, 2020 1:33 PM
  • My goal is to query all machines which is connected via vpn whereas the given ip address and adapter name as criteria.  As I mentioned previously I use convert sccm wql to sql from the logs.  On wql query as per the attachment I was able to add variable ip address for its view.  I was thinking that it will also appears when it was converted to sql.  Not familiar reading those variables until I have read some basic functions.  Celebrating too soon as per above query showing ip address however I cannot combine them to one column as our vpn configuration will show the private and company ip address.  I am trying to get the idea on the built in reports in basic computer information showing both ip address when connecting to vpn.  Sir thank you very much for helping me and I admit lacked of experience unto this matter.   
    Wednesday, April 29, 2020 5:07 PM
  • The query that I provided can do that. 

    Again that method to covert WQL to SQL is NOT supported and will create unsupported queries. 

    I'm not sure what you mean by "I cannot combine them to one column as our vpn configuration will show the private and company ip address."

    You really should look at the ebook that I pointed you too. It is a start to finish guide for creating reports. 


    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

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

    Wednesday, April 29, 2020 5:47 PM
  • This is what I meant.  I am starting to read last night that's why I managed to make it populate however still trying to making it similar to the native report whereas it will give as per below view.  Thank you very much.

    > Hardware - Disk > Disk information for a specific computer - Logical disks

    Wednesday, April 29, 2020 6:06 PM
  • Sir when I tried the query I am getting this error message.  Thanks.



    Wednesday, April 29, 2020 6:26 PM
  • Sir when I tried the query I am getting this error message.  Thanks.



    That is because Name0 is listed twice within the query. use an Alais. https://www.enhansoft.com/why-is-using-a-sql-server-alias-for-column-names-important/

    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

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

    Wednesday, April 29, 2020 7:28 PM
  • This is what I meant.  I am starting to read last night that's why I managed to make it populate however still trying to making it similar to the native report whereas it will give as per below view.  Thank you very much.

    > Hardware - Disk > Disk information for a specific computer - Logical disks

    First off I hope that you make a copy of the report and used a different name. As you customized version will be overwritten if the names are the same as existing report. 

    So you are only looking for the 192.168 IP address? or the 10.x.x.x address? 


    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

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

    Wednesday, April 29, 2020 7:32 PM
  • So you are only looking for the 192.168 IP address? or the 10.x.x.x address?  > If the machine is connected to vpn if will give both IP addresses.  One IP address for the net adapter and the other one if for the vpn adapter.
    Wednesday, April 29, 2020 8:14 PM
  • Thank you very much!  Learning a lot.  

    select distinct
    R.Name0 as 'Computer Name',
    R.User_Name0 as 'User Name',
    NA.Name0 as 'Adapter Name',
    NAC.IPAddress0 'IP Address'
    from 
    dbo.v_R_System R
    join dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION NAC on NAC.ResourceID = R.ResourceID
    join dbo.v_GS_NETWORK_ADAPTER NA on Na.ResourceID = R.ResourceID and NAC.ServiceName0 = NA.ServiceName0
    where 
    NAC.IPAddress0 like N'192.168%' 
    AND NA.Name0 like N'cisco%'


    • Edited by Liam Matthew Wednesday, April 29, 2020 8:32 PM Wrong text
    Wednesday, April 29, 2020 8:28 PM