1 2 Previous Next 17 Replies Latest reply: Jun 11, 2012 2:32 PM by talean RSS

How do I combine and export data from related classes?

Kevin K

Hi,

 

I'm trying to figure out how to export data - from two or more related classes - into a single SQL Server database table.

 

I know very well how to explort data form one class into a SQL Server database - I've done it plenty of times.

 

What I don't know how to do is to take data from a class *and form a related class* and export it into a single SQL Server table.  Specifically, I want to export records from BMC_DataBase (SQL Server database names) plus the parent SQL Server names (found in BMC_SoftwareServer) and the Windows hostnames (found in BMC_COMPUTERSYSTEM) - all into a single SQL Server table that looks roughly like this:

          database_name

          sql_server_instance_name

          windows_host_name

 

All this information is available in my CMDB.  How do I either:

     1. Combine the data using AIE and then export it

     2. Export the data into three separate SQL Server tables, then relate it.

 

I know my way aroung SQL and how to set up relationships.  If I need to use option #2 above, how do I export the relationship information from CMDB?

 

Thanks,

 

Kevin

  • 1. Re: How do I combine and export data from related classes?
    Kevin K

    Is it possible to export CMDB class relationships from CMDB to an external SQL Server database via the Atrium Integration Engine?

     

    Why can't I select BMC.CORE:BMC_DEPENDENCY to export?

     

    Where do I export the class that contains ImpactSourceId and ImpactSourceId?

     

    Does what I'm saying make sense?

  • 2. Re: How do I combine and export data from related classes?
    careyw

    Hi Kevin

     

    A couple of possible ideas for you.

     

    1. To export details of relationships, try using the AR System Form Mappings option instead, and nominate BMC.CORE:BMC_Dependency (the join form) or BMC.CORE:BMC_Dependency_ (the base form) which will give you attributes of the relationship, along with the source and destination CIs (their classes, instance and recon ids etc). From there you can probably do some smarts with Target SQL mappings in AIE to pull in data for names etc. I haven't built one of these data exchanges myself, but looking at it quickly now, it would seem possible.

     

    2. AIE has a concept of main data mappings and secondary data mappings. When you are defining the mappings for a data exchange, you will see that there is a column heading in the box that records the selected data mapping (on the Data Mappings tab), that says 'Main Mapping' and will usually say 'Yes' for your single selected mapping. You can however, also add additional mappings, and when you do this (the heading will say 'No' for these), AIE will attempt to do a join of sorts using what needs to be defined as a common key between the two mappings. Again, I've never built one of these from scratch, but I have worked with an existing data exchange built by someone else that used this mechanism and worked fine. Suggest you take a look at the AIE documentation for an explanation of how this main/secondary mapping feature actually works to see if it can be leveraged here. In the example I had seen, the main mapping was reading a SQL table that had a column that was used as a foreign key to another table, and the secondary mapping was reading that other SQL table. At run time AIE would get the main record and map attributes from that, and would then use the foreign key to get the other table record and map some attributes from there. So provided you have data that support this primary/foreign key relationship, it is technically possible to get AIE to do the join and mapping, in a single AIE exchange. It's pretty hard to explain, but hopefully it is covered in the documentation. If not, the AIE support folks at BMC should be able to get you started.

  • 3. Re: How do I combine and export data from related classes?
    Kevin K

    careyw,

     

    --- Update ---

    I reset the AIE admin password and now I'm able to submit the form.  Will post an update once I get the join working.

    ---

     

    Thanks for the help.  When I attemp to save the AR Mapping, I receive the below error:

     

    A failure occurred in the process used by the Set Fields action. :

    Not AR Admin (ARERR 9281)

     

     

    Any idea as to what is going on?

     

    I *am* definitely logged in as an administrator.

     

    Thanks,

     

    Kevin

  • 4. Re: How do I combine and export data from related classes?
    Gustavo del Gerbo

    Hi Kevin,

     

    The best solution for you here is to setup multiple mappings in one exchange. As Carey mentioned it will let you relate data from multiple sources into one.

    Regarding the not AR Admin message, check that the AIE user and password setup in the AIE console matches the one setup in the AIE.cfg.

  • 5. How do I combine and export data from related classes?
    Vaibhav Dhainje

    When you verifyData Exchange, a GetResult button is enabled, click on Get Result,

    If every thing goes fine you receive a message for successful Data Exchange.

    If not :

    A] .if you are facing any issuewith AIE service, Create new AIE instance from AIEConsole-Configuration-      

      Instances-New and use thisinstance.

    B] .on AIE console-CI Class Mappings-createnew CI class mapping. (say TestCiMapping)

    C] .on AIE console-DataExchanges-Createnew Data Exchange and 'TestCiMapping' into it.

    You can refer sample class mappingsand exchanges for more info.

    Also try recycling remedyservices.

  • 6. Re: How do I combine and export data from related classes?
    talean

    If I am right you talked here about AIE options because I can not see "AR System Form Mappings" in new version of AI 7.6.04.

     

    How this could be done with new version of AIE named AI (7.6.04)?

     

    I must export information from cmdb in flatfile from different classes. I have to use relation class table and other classes to pull information out of that.

     

    Can someone give me steps how to do this.

     

    Thanks!

  • 7. Re: How do I combine and export data from related classes?
    Gustavo del Gerbo

    Hi Kayand,

     

    I recommend you check the AI user's guide and the Spoon User's Guide to understand how to use Spoon to create jobs and modify existing jobs. http://wiki.pentaho.com/display/EAI/Spoon+User+Guide

     

    As a general idea I would say you would have 3 CMDBInput steps, to read frmo each class and the rel-class.

    Or maybe you could use a CMDBLookup to lookup for values that you are interested in.

    You would combine all that, and map it to your target (DB, flatfile or whatever you choose).

     

    Regards,

    Gustavo.

  • 8. Re: How do I combine and export data from related classes?
    careyw

    You're right, I was talking about AIE not AI. AI (aka Pentaho) is not just a new version of AIE, it's a totally new tool. Since this new tool is the way forward (and AIE will eventually be withdrawn) suggest you do as Gustavo recommends above and spend some time looking at AI. It promises to do everything AIE can do plus more, and using an industry standard/open source platform.

  • 9. Re: How do I combine and export data from related classes?
    talean

    I will use AIE for this. So question is the following:

     

    - how I can export attributes values from different classes in one csv file?

     

    Can I do this with AIE mappings / option configuration or...

    I must do join forms with Developer to get one form with all information in it?

     

    I need some guide about this because AIE user guide is very poor regarding this.

     

    Thanks!

  • 10. Re: How do I combine and export data from related classes?
    Gustavo del Gerbo

    To export from more than 1 class you need to use more than 1 mapping, and combine those mappings in the same Data Exchange.

    Otherwise you could create a join form with the field you need, it is a good idea to keep the AIE job simpler.

     

    Regards,

    Gustavo.

  • 11. Re: How do I combine and export data from related classes?
    talean

    Can you please give me some hint how to combine mappings in AIE in a single job?

     

    Thx

  • 12. Re: How do I combine and export data from related classes?
    talean

    "To export from more than 1 class you need to use more than 1 mapping, and combine those mappings in the same Data Exchange."

     

    Please tell me how I can do "join" with mapping as you describet. As far as I know it could not be possile to combine data like you mention for csv export.

     

    Thx.

  • 13. Re: How do I combine and export data from related classes?
    Gustavo del Gerbo

    The AIE user's Guide has more detail on the Mappings combination.

    Basically you create a Data Exchange and in the Mappings tab you would put two mappings. One will be the primary (the one used to compare the kay), and the other one is the secondary (the one that will add more data to the results).

  • 14. Re: How do I combine and export data from related classes?
    Gustavo del Gerbo

    Even then I think it's better to do a join form with the field you need and then use that one to do the export with AIE. It will be much less complex.

1 2 Previous Next