Yurbi Connect can also be used for this purpose and solves the difficult part of building the SQL.
Yurbi comes with an out of the box connector to BMC and any customizations can be made to our out of the box integration, or you can build a Yurbi App from scratch using our AnyDB technology. - http://www.yurbi.com/solutions/bmc-itsm-remedy/ Using Yurbi you can create all your data extracts based on the extraction rules you like. - Video here of that process - http://yurbi.tv/index.php?p=216 Then you can pass that report/data into our Yurbi Connect process which can allow you to schedule it and insert it into any ODBC datasource for archiving or data mart building. No video of the data mart building process but you can see how easy the Yurbi Connect workflow is by looking at this email to Service Desk example - http://yurbi.tv/index.php?p=138 The benefit is instead of hardcoding a lot of scripting you've got a solution that is easy to modify or add when you change or add forms. thanks, David On Mon, Jan 30, 2012 at 6:07 AM, Coleman, Gavin < gavin.cole...@computacenter.com> wrote: > ** > > We implemented a data-driven archive functionality for ITSP. This was > written by Abydos. This is roughly the strategy we followed:**** > > ** ** > > **1. **Determine which module to set up the archiving for (we > started with Incident Management)**** > > ** ** > > **2. **Determine which associated forms (WorkLogs, Attachments etc) > to carry over**** > > ** ** > > **3. **Determine the archive criteria (Number of days after closure > no associations for example)**** > > ** ** > > **4. **New field added CC_Chk_ArchiveFlag**** > > ** ** > > **5. **Archive process enabled on the server and on the relevant > forms (Qualification CC_Chk_ArchiveFlag = “Yes”)**** > > ** ** > > **6. **Table qualifications on the parent archive form are amended > to point to new archive forms (e.g. WorkLog table now points to > ARC_HPD:WorkLog)**** > > ** ** > > ** ** > > Our data driven solution works in the following way:**** > > ** ** > > **1. **All the matching parent records are updated using an > escalation that reads a SQL script and updates the CC_Chk_ArchiveFlag field > **** > > ** ** > > Example Script:**** > > ** ** > > UPDATE HPD_Help_Desk SET CC_Chk_ArchiveFlag = 0 WHERE (NOT EXISTS (SELECT > chg.Request_ID01 FROM CHG_Associations chg **** > > WHERE chg.Request_ID01 = Incident_Number AND chg.Status < 5) **** > > AND**** > > NOT EXISTS (SELECT hpd.Request_ID01 FROM HPD_Associations hpd **** > > WHERE hpd.Request_ID01 = Incident_Number AND hpd.Status < 5)**** > > AND**** > > NOT EXISTS (SELECT pke.Request_ID01 FROM PBM_Known_Error_Associations pke* > *** > > WHERE pke.Request_ID01 = Incident_Number AND pke.Status < 5)**** > > AND**** > > NOT EXISTS (SELECT pia.Request_ID01 FROM PBM_Investigation_Associations pia > **** > > WHERE pia.Request_ID01 = Incident_Number AND pia.Status < 5)**** > > AND**** > > NOT EXISTS (SELECT srm.Request_ID01 FROM SRM_Associations srm**** > > WHERE srm.Request_ID01 = Incident_Number AND srm.Status < 5)) AND Status = > 5 AND Closed_Date < 1321920000**** > > ** ** > > ** ** > > **2. **All the related child records are updated using an > escalation that reads a SQL script and updates the records.**** > > ** ** > > Example Script: **** > > ** ** > > UPDATE (SELECT secondaryform.CC_Chk_ArchiveFlag as archiveFlag FROM > HPD_Help_Desk primaryform, HPD_WorkLog secondaryform WHERE > secondaryform.Incident_Number = primaryform.Incident_Number AND > primaryform.CC_Chk_ArchiveFlag = 0) SET archiveFlag=0**** > > ** ** > > **3. **Once all the parent and child records have the > CC_Chk_ArchiveFlag set, the normal archive process (defined on the server) > takes over and archives the child records first and then the parent > records. The process is performed in this order to ensure that the normal > HPD:Help Desk filters that fire on delete do not delete the child records. > **** > > ** ** > > ** ** > > The above solution is easily extendible. The difficult bit is working out > the SQL! It works well, and we are looking to roll out to other modules.** > ** > > ** ** > > Hope this helps. I can give more information off list if required.**** > > ** ** > > *Gavin Coleman* > > *Senior Analyst/Programmer * > > *Computacenter (UK) Ltd***** > > Services & Solutions**** > > Hatfield Avenue**** > > Hatfield, Hertfordshire, AL10 9TW, United Kingdom**** > > T: +44 (0) 1707 631662**** > > E: *gavin.cole...@computacenter.com***** > > W: *www.computacenter.com* **** > > ** ** > > *From:* Action Request System discussion list(ARSList) [mailto: > arslist@ARSLIST.ORG] *On Behalf Of *Matthew Kunkel > *Sent:* 26 January 2012 22:18 > *To:* arslist@ARSLIST.ORG > *Subject:* Re: Archiving strategy on ITSM 7.6 +**** > > ** ** > > ** **** > > Two options we have seen from BMC are DSO and custom scripts, both require > custom mappings for each of ~1000 regular forms. Additionally, structure > changes would need to be maintained in custom mappings when customizations, > patches, and upgrades are installed. Since BMC no longer includes the > change details in ITSM patch release documentation, both solutions are > problematic.**** > > ** ** > > There are database level products that allow some mining of the redo logs > and should give a hybrid replication/archive functionality. One strategy > under development is strip purge job deletes from the redo logs and > propagate all other data/structure changes. Two products that we have > encountered are Oracle Golden Gate and IBM Change Data Capture (neither is > in use yet and this is not an endorsement or recommendation). **** > > ** ** > > ** ** > ** > > *Matthew Kunkel Systems Engineer***** > > Phone 479.277.3392**** > > mtku...@wal-mart.com**** > > ** ** > > Walmart**** > > ****805 Moberly Lane****, MS32 **** > > ****Bentonville**, **AR** **72716******** > > *Save money. Live better.* > > ** ** > ** > > ** ** > > ** ** > > ** ** > > *From:* Action Request System discussion list(ARSList) [mailto: > arslist@ARSLIST.ORG] *On Behalf Of *patchsk > *Sent:* Tuesday, January 24, 2012 2:19 PM > *To:* arslist@ARSLIST.ORG > *Subject:* Archiving strategy on ITSM 7.6 +**** > > ** ** > > ** **** > > We just started to looking at archiving our production data.**** > > We are currently in ITSM 7.6.03.**** > > With so many related forms to main forms, it would be tricky and take > significant effort to properly draw the archiving plan like**** > > which forms need to archived in which order so that relationships are > carried over so that a closed incident will be moved to a different server > or a different form **** > > with all of its relationships in tact.**** > > Anyone done this already, if so can you provide a few details on > the strategy you followed.**** > > DSO is another option we are looking at it but have not made decision yet. > **** > > We can not use DB level replication because if we delete a record in > production then it will delete it from replicated database also.**** > > ** ** > > Seems like from BMC knowledge base it is not supported and cannot provide > any guidelines.**** > > But we cannot just keep storing the data in production.**** > > ** ** > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_**** > ------------------------------ > > *This email and any files transmitted with it are confidential and > intended solely for the individual or entity to whom they are addressed. If > you have received this email in error destroy it immediately. * > > **** Walmart Confidential ******** > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ **** > > > ********************************************************************** > COMPUTACENTER PLC is registered in England and Wales with the registered > number 03110569. Its registered office is at Hatfield Business Park, > Hatfield Avenue, Hatfield, Hertfordshire AL10 9TW > COMPUTACENTER (UK) Limited is registered in England and Wales with the > registered number 01584718. Its registered office is at Hatfield Business > Park, Hatfield Avenue, Hatfield, Hertfordshire AL10 9TW > COMPUTACENTER (Mid-Market) Limited is registered in England and Wales with > the registered number 3434654. Its registered office is at Hatfield > Business Park, Hatfield Avenue, Hatfield, Hertfordshire AL10 9TW > COMPUTACENTER (FMS) Limited is registered in England and Wales with the > registered number 3798091. Its registered office is at Hatfield Business > Park, Hatfield Avenue, Hatfield, Hertfordshire AL10 9TW > > The contents of this email are intended for the named addressee only. > It contains information which may be confidential and which may also be > privileged. > Unless you are the named addressee (or authorised to receive mail for the > addressee) you may not copy or use it, or disclose it to anyone else. > If you receive it in error please notify us immediately and then destroy > it. > Computacenter information is available from: http://www.computacenter.com > ********************************************************************** > > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > -- *David A. Ferguson *Yurbi® - Bring Your Data to Life <http://www.yurbi.com>* * Work: 571-354-0515 Fax: 703-991-5935 Email: david.fergu...@5kfish.com http://www.linkedin.com/in/davferg http://www.twitter.com/davferg5000 5000fish, Inc. <http://www.5kfish.com> 2850 W. Horizon Ridge Parkway Suite 200 Henderson, NV 89052 <http://www.linkedin.com/in/davferg> _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
