none
How To Create a Query on report builder For specific processor Type computers

    Question

  • Hello everyone, at the moment I am trying to create a query that allows me to know computers by processor type. for instance a user enters core 2 duo, or I3 and get all computers with that processor type. surprisingly this looked like an easy task at first but for some reason I am getting no results from my created Querys perhaps I am missing something? I have try multiple ones and the ones I am able to compile do not populate the list at all i get Zero results.

    the one Query I think should work looks like:

    SELECT DISTINCT SYS.Netbios_Name0, Processor.Name0, Processor.MaxClockSpeed0, Processor.DeviceID0 
    FROM fn_rbac_R_System(@UserSIDs)  SYS 
    JOIN fn_rbac_GS_PROCESSOR(@UserSIDs)  Processor on SYS.ResourceID=Processor.Name0 
    WHERE Processor.Name0 LIKE '%@variable%'  
    ORDER BY SYS.Netbios_Name0

    any type of help iskindly apprecciated. 

    thanks a lot 

    Thursday, September 26, 2013 6:07 PM

Answers

  • I tested both of your queries and both of your queries above work as written.

    • Your first query will find all PC's CPU based on a PC name.(you all ready know that)
    • Your second query will find all PC based on a CPU name.

    There is nothing wrong with your queries.


    http://www.enhansoft.com/

    • Marked as answer by JuanBurger Friday, October 04, 2013 8:44 PM
    Thursday, September 26, 2013 9:14 PM
  • So just to be clear about my post above both query does return results. I’m sure that are return exactly what you indented them to return.

    I guess the question should be exactly how are you testing the queries above?


    http://www.enhansoft.com/

    • Marked as answer by JuanBurger Friday, October 04, 2013 8:44 PM
    Thursday, September 26, 2013 11:23 PM
  • So how are you building your report in RB? aka what are the steps that you are taking. As long as you get results in SSMS, RB will give you the exact same results.

    http://www.enhansoft.com/

    • Marked as answer by JuanBurger Friday, October 04, 2013 8:44 PM
    Sunday, September 29, 2013 5:17 PM
  • It sounds like you are taking an existing report and updating the query, right?

    http://www.enhansoft.com/

    • Marked as answer by JuanBurger Friday, October 04, 2013 8:44 PM
    Tuesday, October 01, 2013 10:26 AM
  • So are you re-freshing the query in RB and are you updating the columns in the table? If you don’t you will get no data in the report. And it has to be done in that order too.


    http://www.enhansoft.com/

    • Marked as answer by JuanBurger Friday, October 04, 2013 8:43 PM
    Tuesday, October 01, 2013 1:52 PM
  • Uploading the report is not the same thing as re-freshing the query in RB.

    I have created the RDL.

    www.enhansoft.com/Downloads/Custom Reports/BurguerLand.zip


    http://www.enhansoft.com/



    • Edited by Garth JonesMVP Friday, October 04, 2013 8:23 PM typo
    • Marked as answer by JuanBurger Friday, October 04, 2013 8:43 PM
    Friday, October 04, 2013 1:20 AM
  • It a MS thing. Copy the link into IE and it will download.

    http://www.enhansoft.com/

    • Marked as answer by JuanBurger Friday, October 04, 2013 8:43 PM
    Friday, October 04, 2013 6:09 PM

All replies

  • Hello everyone, at the moment I am trying to create a query that allows me to know computers by processor type. for instance a user enters core 2 duo, or I3 and get all computers with that processor type. surprisingly this looked like an easy task at first but for some reason I am getting no results from my created Querys perhaps I am missing something? I have try multiple ones and the ones I am able to compile do not populate the list at all i get Zero results.

    the one Query I think should work looks like:

    SELECT DISTINCT SYS.Netbios_Name0, Processor.Name0, Processor.MaxClockSpeed0, Processor.DeviceID0 
    FROM fn_rbac_R_System(@UserSIDs)  SYS 
    JOIN fn_rbac_GS_PROCESSOR(@UserSIDs)  Processor on SYS.ResourceID=Processor.Name0 
    WHERE Processor.Name0 LIKE '%@variable%'  
    ORDER BY SYS.Netbios_Name0

    any type of help iskindly apprecciated. 

    thanks a lot 

    Actually your query doesn't work .

    It should be SYS.ResourceID = Processor.ResourceID

    BTW, I will ask to have this post move to the CM12 general forum as this query is clearly CM12.


    http://www.enhansoft.com/

    Thursday, September 26, 2013 7:13 PM
  • Hello sir, thanks for your help and prompt reply, yes that query I  did try too, and it does not seem to populate no result, what is strange is that if I request Processor information for an specific computer the processor field Name0 populates with the correct information such as 

    Intel(R) Core(TM)2 Duo CPU ....

    the query

    SELECT SYS.Netbios_Name0, Processor.Name0, Processor.MaxClockSpeed0, Processor.DeviceID0 
    FROM fn_rbac_R_System(@UserSIDs)  SYS 
    JOIN fn_rbac_GS_PROCESSOR(@UserSIDs)  Processor on SYS.ResourceID=Processor.ResourceID 
    WHERE SYS.Netbios_Name0 LIKE @variable  
    ORDER BY SYS.Netbios_Name0

    works without a problem however when selecting a different value :

    SELECT  DISTINCT SYS.Netbios_Name0, Processor.Name0, Processor.MaxClockSpeed0, Processor.DeviceID0 
    FROM fn_rbac_R_System(@UserSIDs)  SYS 
    JOIN fn_rbac_GS_PROCESSOR(@UserSIDs)  Processor on SYS.ResourceID=Processor.ResourceID 
    WHERE Processor.Name0 LIKE @variable  
    ORDER BY SYS.Netbios_Name0

    the last query does not produce results.

    regards

    Thursday, September 26, 2013 8:20 PM
  • I tested both of your queries and both of your queries above work as written.

    • Your first query will find all PC's CPU based on a PC name.(you all ready know that)
    • Your second query will find all PC based on a CPU name.

    There is nothing wrong with your queries.


    http://www.enhansoft.com/

    • Marked as answer by JuanBurger Friday, October 04, 2013 8:44 PM
    Thursday, September 26, 2013 9:14 PM
  • Thanks a lot!!! I am not sure if this is the right forum to ask this but, if those querys dont populate any results, what else can I look at on SCCM ?? 
    Thursday, September 26, 2013 10:18 PM
  • So just to be clear about my post above both query does return results. I’m sure that are return exactly what you indented them to return.

    I guess the question should be exactly how are you testing the queries above?


    http://www.enhansoft.com/

    • Marked as answer by JuanBurger Friday, October 04, 2013 8:44 PM
    Thursday, September 26, 2013 11:23 PM
  • Yes, thanks again for your fast reply, This is why i got so confused I knew they would work they seem to be pretty straight forward. I am building this querys on report builder, when building these I run these reports on SCCM but i get no results, again just to be clear the first one returns the desired results but not the second one and I am not sure where to approach this problem. 
    Sunday, September 29, 2013 4:10 PM
  • So how are you building your report in RB? aka what are the steps that you are taking. As long as you get results in SSMS, RB will give you the exact same results.

    http://www.enhansoft.com/

    • Marked as answer by JuanBurger Friday, October 04, 2013 8:44 PM
    Sunday, September 29, 2013 5:17 PM
  • Thank you sir, Yes that is exactly what is confusing as the first report works on both sides, Report builder and SCCM and the other one does not work on either side.
    in reality the query does not change a bit other that instead putting processor speed I select processor name to be the variable in reality I am just adding a:  DISTINCT statement 
    and changing from the first query(the one that works) : 
    WHERE SYS.Netbios_Name0 LIKE @variable

    for (the one that does not work) :

    WHERE Processor.Name0 LIKE @variable

    on the first query when have the information of the processor for an specific computer I am able to see the processor name with information such as: Intel Core2Duo family etc.. etc.. So perhaps I should add a wildcard? so when the user enters for instance: 
    AMD or Intel it would look for anything related to that.. however if I remember my SQL well LIKE should do the trick.. I think I have also tried: '%@variable%' but I think this did not returned any results either . Thanks a lot for your help 
    Tuesday, October 01, 2013 4:41 AM
  • It sounds like you are taking an existing report and updating the query, right?

    http://www.enhansoft.com/

    • Marked as answer by JuanBurger Friday, October 04, 2013 8:44 PM
    Tuesday, October 01, 2013 10:26 AM
  • Correct that would be the case, it has worked before, I dont see why would not work now..
    Tuesday, October 01, 2013 1:49 PM
  • So are you re-freshing the query in RB and are you updating the columns in the table? If you don’t you will get no data in the report. And it has to be done in that order too.


    http://www.enhansoft.com/

    • Marked as answer by JuanBurger Friday, October 04, 2013 8:43 PM
    Tuesday, October 01, 2013 1:52 PM
  • Yes In Fact every time I change the report I do a re Upload of it to SCCM to make sure I'm playing with the report I have recently modified and I did this everytime.
    Thursday, October 03, 2013 6:41 PM
  • Uploading the report is not the same thing as re-freshing the query in RB.

    I have created the RDL.

    www.enhansoft.com/Downloads/Custom Reports/BurguerLand.zip


    http://www.enhansoft.com/



    • Edited by Garth JonesMVP Friday, October 04, 2013 8:23 PM typo
    • Marked as answer by JuanBurger Friday, October 04, 2013 8:43 PM
    Friday, October 04, 2013 1:20 AM
  • Thanks a lot! however for some reason that link seems down at the moment ?
    Friday, October 04, 2013 4:57 PM
  • It a MS thing. Copy the link into IE and it will download.

    http://www.enhansoft.com/

    • Marked as answer by JuanBurger Friday, October 04, 2013 8:43 PM
    Friday, October 04, 2013 6:09 PM
  • Thanks a lot! how can I give enough points ?
    Friday, October 04, 2013 8:43 PM
  • Thanks a lot! how can I give enough points ?

    lol, I like beer/whiskey better than points. :-)

    http://www.enhansoft.com/

    Friday, October 04, 2013 8:56 PM