14 Replies Latest reply on Jul 27, 2010 7:59 AM by Adam Bowen

    Host Retrieval from BL Database

      In the absence of a database schema diagram I am trying to retrieve a list of hosts that have a specific property "PRODUCTION_OWNER" matching a specific value, and to return a bunch of other data for each host, namely "ENVIRONMENT".


      I have looked at the query generated by BL and stored for smart_groups, however this refers to bl_value_ref numbers, also suggesting a normalised DB.


      However regardless of what I try I can not retrieve this very basic information. Essentially;


      device details from table "device"

      Where the property  "PRODUCTION_OWNER" has a value which I know is stored in table primitive_bl_value

      and to also return "ENVIRONMENT" which is stored also table primitive_bl_value


      table property_reference_property appears to have references for properties I can find no direct relationship between the tables.  I have managed to get 192000 rows returned for 500 servers. i am guessing that some of these tables may be self referencing (parent child) and I am fairly proficient at SQL, however without a Database Schema Diagram I am lost.


      The nearest i have come, if this helps is as follows;





      pbv.bl_value_id, p.property_id, prp.bl_value_id, p.name, pbv.value



      property_reference_property prp, [property] p, primitive_bl_value pbv




      prp.bl_value_id = pbv.bl_value_id AND


      .property_id = prp.property_id AND


      .property_id = 2000014 AND


      .bl_value_id in (select pr.bl_value_id from primitive_bl_value pr where value = 'dev')



      However it seems there are no bl_value_id entries in primitive_bl_value that match any in property_reference_property suggesting there is another table(s) and the relationship is not as straight forward as I had hoped, or expected.


      2000014 is the bl_value_id of the entry in primitive_bl_value for property PRODUCTION_OWNER.


      Anyone have any ideas ?





        • 1. Re: Host Retrieval from BL Database

          At this time we still do not share our schema data. Sorry.


          You do not need schema information for you list below though. You can use the blcli to list all servers and to get a specific property value. Refer to the blcli help documentation.

          • 2. Re: Host Retrieval from BL Database
            Bill Robinson

            You should not use the db to do this.


            Create a smart group that contains all servers w/ the PRODUCTION_OWNER property to what you want.  Then use some blcli to list all the servers in the group and then pull the ENVIRONMENT property for that object.

            • 3. Re: Host Retrieval from BL Database

              I already created a smart group, I don't see how to display a property not shown in the smart group view even with the blcli.


              Can you use the blcli to populate a table in another SQL database with the output ?  Essentially, BL is the golden source, but i need another system to have access to this server list.


              Also being  new to BL, is the 'how to' for this suggestion in the user guide, or in a different guide ?





              • 4. Re: Host Retrieval from BL Database
                Bill Robinson

                Create your smart group in the gui.

                Write a nsh script that calls the blcli commands

                First list all the servers in your smart group

                Then loop through each server and run a blcli command against the server to display the value of the desired property.


                Then do whatever you want w/ that data.


                The blcli only makes connections to the appserver to pull data.  It doesn't talk to anything else.  But, in your nsh script you can call sqlplus or sqlcmd (if it's installed on the system running the blcli/nsh) and do whatever you want w/ the data.

                • 5. Re: Host Retrieval from BL Database

                  Found the blcli help guide.


                  Just need to automate this so the data is imported directly in to another database.  I was hoping to just use a SP, ie not go via some convoluted csv export import process.





                  • 6. Re: Host Retrieval from BL Database

                    Great - will look in to sqlcmd to do the job.


                    Many thanks for the quick response.



                    • 7. Re: Host Retrieval from BL Database
                      Bill Robinson

                      Sqlcmd is to get it into the other db, that's part of the MSSQL client tools install.  or you'd need sqlplus if you're using oracle.

                      • 8. Re: Host Retrieval from BL Database

                        Hi Bill,


                        Someone seems to think this doesn't work ?




                        Can you confirm ?



                        • 9. Re: Host Retrieval from BL Database

                          ok, figured out nearly all of this with the exception of;


                          >> First list all the servers in your smart group


                          How ?  I have the findGroupByQualifiedName working, but I already know the name so not sure what use that is.


                          There appears to be no option to list the name of each server in a smart group from nsh which puts me back to square  1.


                          As the smart group members are listed on the fly from the SQL in the smart_group_query table I don't see how this can work anyway, unless i populate another table with the hosts from running that SQL directly and use Jython to query that new temp table and pass each host name to the nsh script to get the extra property value.


                          Surely there is a neater way than this ?





                          • 10. Re: Host Retrieval from BL Database

                            ok, its in the "Server"  object.


                            The docs could be a little more helpful as this it not overly intuitive



                            • 11. Re: Host Retrieval from BL Database

                              haha, how funny. There's a view, v_server_property - has all servers and their properties in it.


                              Nice one

                              • 12. Re: Host Retrieval from BL Database
                                Bill Robinson

                                i'd really encourage you to go through the blcli, because who knows what will happen w/ the db, that view may change or get removed.  the blcli should be more stable.  though using the blcli won't be as simple as a select statement..

                                • 13. Re: Host Retrieval from BL Database

                                  I doubt the DB schema will change that dramatically unless a major overhaul occurs.  Knowing this large products that

                                  is unlikely to happen, so I'll make a note of the SQL and if the view goes the SQL will likely need little work to make oit



                                  Tasks such as this are very common place, it would be nice if there was a mechanism in place to enable other in house systems to query data directly via views, rather than the cumbersome blcli which is also pretty slow in comparison.


                                  I will look at a blcli solution at a later date though, when I have more time to learn how to wrap it in JYthon and find a box that has sqlcmd installed.


                                  Thanks for the advice and assistance though - taken on board.



                                  • 14. Re: Host Retrieval from BL Database

                                    Jbew, good luck.


                                    For anyone else who may be come across this thread, I would really advise sticking with the supported methods. Directly manipulating your database is not supported and any issues you cause with your environment by doing so will likely render your entire installation as unsupported. The blcli is not hard to learn, and is the correct way to get the information requested below.