1 2 3 Previous Next 32 Replies Latest reply on Sep 25, 2012 9:24 AM by Alec Murchie

    SCCM-CMDB Data Population

    Rakesh Jajper
      Share This:

      Hi All,


      We need help in integrating SCCM to CMDB.

      We have SCCM 2007 and CMDB 7.5. Has anyone done it before/any idea.


      We want to populate Hardware and Software inventory in CMDB through AIE.

      So please suggest which table/view we have to refer for the same.




        • 1. Re: SCCM-CMDB Data Population
          Carey Walker
          I would suggest you take a look at the SMS2CMDB open source solution. While it is still targeted for SMS and CMDB V2.x (not SCCM or CMDB 7.5), it is still a great source of ideas and which SMS/SCCM views you need to go to in order to find the hardware and software details you need. Take a look at the definitions of the various EIE/AIE exchanges that are part of the solution, plus the suggested reconciliation approaches.
          • 2. Re: SCCM-CMDB Data Population

            I've just done it for a customer.


            You couldn't use the SMS2CMDB tool. It's not work with SCCM , because tables structures change and ...

            In this kit (SMS2CMDB) you have lots of things:

            AIE mapping

            RE rules

            Custom forms to add Product in DSL



            and that is not necessary that your customer needs.


            I think that the best way for you is to create some custom view on the SCCM DB.

            You have certainly some SCCM expert close to you.


            We have done by this way.

            • 3. SCCM-CMDB Data Population

              I used the SMS2CMDB document to get an idea of what tables to use.


              For Computers.

              The basic info is in GS_COMPUTER_SYSTEM.

              But it's not enough to fill out the fields.

              So I created another CI mapping to the other tables



              I used the ResouceID to relate the different mappings.


              Software is something i'm trying to figure out.

              Hopefully someone in Communities can lead me in the right direction.

              1 of 1 people found this helpful
              • 4. Re: SCCM-CMDB Data Population

                Found the following site @ MickeySoft which might help.  http://www.myitforum.com/forums/Data-Dictionary-for-SCCM-database-m186482.aspx


                Basically, there is an msi installer, CreatingCustomReportsByUsingSQLViews.msi, which

                provides information that will help you to create custom reports, determine which Configuration Manager SQL views contain the information you need for your reports, and identify a path between the necessary SQL views and determine what columns can be used to join them.


                From my Android phone on T-Mobile. The first nationwide 4G network.

                • 5. SCCM-CMDB Data Population

                  This post is out of date but here is some more information...


                  We have found that the SMS2CMDB app contains enough information to import from SCCM2007 R2


                  Unzip the SMS2CMDB package to a PC that has the version 7 Administrator tools loaded ( not 7.6 as we couldn't get this to work )


                  Don't load the 'Remedy link' application as Atrium 7.5 has the AIE engine already loaded.


                  Unzip the files and then use the installationintegration-nonassetinstall.bat ( Atrium Only ) we haven't tested this with the Asset module loaded, just Atrium. This will load all the EIE/AIE exchanges and prefix them with  'SMS.'


                  Use the cmdbdriver application to import a modifed machine-add-fields.in file to add in the required extra CMDBattributes.


                  With this in place you should be able to extract from SCCM to the CMDB using the following SCCM views.
















                  If these views are not available on the SCCM server check that you have the reporting extentions/server loaded.


                  These exchanges will give you a head start on setting up the Config Items as you want them to be..



                  1 of 1 people found this helpful
                  • 6. Re: SCCM-CMDB Data Population


                    Can you help me? I'm looking for someone that has actually done some sort of integration between SCCM 2007 and CMDB (using AIE).

                    I'm trying to transfer Deskops and related software to the CMDB 7.5.


                    I really would like some help with the Data mappings, in particular how to schedule the associated exchanges so that you pick all the Desktop devices that SCCM has detected and then all the related Software applications for each detected Desktop?

                    Also, how do you schedule and then connect the ComputerSystem CI to the related processor/OS/Disk/enclosure CIs?

                    Appreciate any help possible?


                    Many thanks,


                    • 7. Re: SCCM-CMDB Data Population
                      Carey Walker



                      try this for software


                      <code sample removed>

                      Please take a look at the Microsoft support pages for SCCM where you will find many examples of the types of queries you can use to capture software inventories.



                      Enjoy. If this or previous posts help, please mark as Correct or Helpful!

                      • 8. Re: SCCM-CMDB Data Population


                        Thankyou very much for this. Very Helpful!

                        I'm not an SQL guru so please excuse the Qs.

                        Will both methods achieve the same result?

                        It looks like this will also extract each of the related attributes (Disk, CPU, OS etc.)

                        I do have over 10,000 desktops so will be creating allot of CIs, I'll test on a much smaller scale though!


                        So presumably this would create a view in SCCM which I would then include in AIE by creating multiple Mappings and Exchanges? - I think I'm maybe missing something as this seems a bit too simple to be true???




                        • 9. Re: SCCM-CMDB Data Population



                          After further looking I now see what they both do - first is sw and 2nd is related components!

                          If I can get a DBA to create/run these queries is the idea to save them in SCCM as new views?

                          This would mean the data would be available for me to use in an AIE Data Mapping and Exchanges.


                          I'd then create a Mapping and Exchange for each BMC Class that needs to be populated enabling me to also add manual entries for eg. Category/Type/Item/Status fields?


                          Does this make sense or am I way off?




                          • 10. Re: SCCM-CMDB Data Population
                            Carey Walker

                            OK, to explain better than I did before ...


                            The second query will give you a row back for each workstation, with a bunch of useful attributes. The resource id on each row is a foreign key for many other views in the SCCM world. In particular, the first query will give you a big list of all software installed on all workstations, and you can work out which workstation by matching resource ids.


                            So, if you can get your SCCM or SQL people to build views for these queries, you can then define the views as the data sources for AIE jobs and use AIE to pull in the data. The workstation query will go into Computer System class and the software query will go into the Product class. You can then build an AIE job to create relationships between the two by defining the resource id as the primary key for the relationship with C/System class and the parent/source class and the Product class as the child/destination class. Relationship class would be Hosted System Components.


                            So you are 99% there already!

                            1 of 1 people found this helpful
                            • 11. Re: SCCM-CMDB Data Population

                              careyw, that's great info.


                              with one of my projects, the SCCM people don't know how to create new views.

                              so i had to create multiple AIE jobs and push the data to the CI class. 

                              each job would modify a CI based on resourceID.

                              thankfully the client has a relatively simple CMDB.


                              for the software part, i haven't worked it out fully yet.

                              a straight push to BMC_Product from SCCM will yield a lot of useless products.

                              a relatively small number of PC (8000) produces 250,000 records.

                              one suggestion was to create a staging form that you can push all the data to.

                              one that staging form is a check box that marks a software title to be pushed to the CMDB.

                              but i haven't spent much time on it yet.

                              • 12. Re: SCCM-CMDB Data Population
                                Carey Walker



                                yes, the installed software queries, as noted earlier in these posts, generate huge volumes of data, much of which you won't want in the CMDB. We're working on our approach for this now but will be filtering the values we pull in based on an analysis of the data currently under way. Your idea of a staging form is also a good idea as it can be fine tuned over time, however the volumes of data in that staging form will often become unmanageable.

                                • 13. Re: SCCM-CMDB Data Population


                                  Thanks, so I think I got most of it right and am beginning to understand better!


                                  On the relationship bit, do you need any other fields passed across to CMDB (other than ResourceID) to tie the software to the Computer System eg. TokenId or InstanceId?

                                  I preseume the .ResourceID in SCCM is sent to .TokenID in the CMDB?


                                  I just can't picture the iterations you need to go through eg. to link 80 Software applications (eg. app1-80 ) to 5 separate Workstations called PC1-PC5???


                                  Many thanks,


                                  • 14. Re: SCCM-CMDB Data Population
                                    Carey Walker

                                    When you create the software CIs, you will need to also store the resource id AND group id. In combination, these define a unique instance of a software product on a compute system (refer back to the queries earlier).


                                    So TokenId for example could be used to hold a concatenation of these values (maybe resourceid:group id).


                                    Assuming you have also used TokenId for computer systems to hold the resource id, you can then define a simple AIE relationship builder job, to create a relationship between any computer system CI that has a given resource id value in its TokenId and any product CIs that have the same value before the colon in their token ids.


                                    i.e C/System token id = resource id = 100

                                    Product token id = resourceId:groupid = 100:2


                                    This means that the product 100:2 is installed on computer system 100.

                                    1 of 1 people found this helpful
                                    1 2 3 Previous Next