On the first day of ECHO, my admin gave to me, some notes about my SysRowID!
Relating Epicor Extended Tables via SysRowID
As part of an ERP implementation, it’s not uncommon for companies to find the need to store information that does not easily fit into the ERP system’s existing database schema. Fortunately, Epicor ERP offers a robust ability to extend an existing database table and add fields to store custom data, to better address the requirements of an implementing company, and The Estes Group’s Echo hosting platform completely supports these critical capabilities.
On the technical side, adding UD fields to an existing table actually creates a new table in the Epicor database, a table that is structured to relate back to the original “parent table.” While two tables exist at the database level, the data model at the application level presents the two tables as a single unified entity, which allows customers to interact with the standard and custom data through BPMs, BAQs and form customizations, as if they were working against a single table.
While this capability is of great utility, there are times when an ERP administrator may have a need to compare child and parent tables, for administrative purposes. As such, it is helpful to understand the architecture for relating standard Epicor tables to their UD children at the application and database level. The following example intends to serve this purpose.
Adding UD fields to the Epicor ERP application is done though the “User Defined Column Maintenance” form. In the following example, I added a UD field to the standard Epicor ERP Part table:
Once UD fields are added to one or more tables, the administrator needs to regenerate the data model from the Epicor Administration Console on the AppServer. In the example, I regenerated the data model as follows:
Performing the above tasks adds a “Part_UD” table to the Epicor database:
It also synchronizes the application’s data model with the database:
In the following example, I went on to query the Part UD table. Note the “ForeignSysRowID” field—as you can see, the key to a UD table is based on the SysRowID of its parent, since the records of a UD table exist in a 1-to-1 relationship with their parents:
As such, if you join the Part_UD table to its parent Part table where Erp.Part.SysRowID = Erp.Part_UD.ForeignSysRowID, you will receive results similar to the following:
Whether on premise or in a hosted solution such as ECHO by EstesGroup, Epicor’s database extension capabilities are of great use to implementation teams and end users. For ERP admins, an understanding of the underlying structure can serve to assist in managing the database and the application, should the need arise.
This Holiday Season, EstesGroup would like to give you “12 Days of ECHO” tips and tricks for those ERP System Admins out there.
We understand this time of year there is a lot going on for companies, such as: year end preparations, budgeting for the next year, personnel changes, and company shifts, etc. So that being the case, we wanted to bring a bit of humor and help to your holiday.
I just need to get this off my chest – so bear with me.
First off, I’ve been doing sysadmin work for scores of years now, and the idea of backups, business continuity, and “bad guys” isn’t new. However, this week it was brought to a new and interesting head for one small business.
Rewind the clock two years and we were in the conversation with this business about where they host their “golden nuggets” of their business, what servers did what, where were the users, how did the backups fare, state of malware, web filtering protection, etc. You know, all the “normal” stuff any qualified IT provider would ask a prospective customer. “We’re fine” was the answer – they had an in-house IT guru watching all that stuff. However, they did make a (wise) decision to host their ERP solution with us.
Last week, our monitoring went suspiciously quiet, it looked like the company went on vacation, or they had fallen asleep at the keyboard. I reached out to the company, and was informed that they had been the victim of the latest ransomware attack, and all their documents were encrypted and unusable. Thankfully, since they were hosting their ERP system with us, that was safe from the attack. All their ERP data was secure but everything else they controlled was locked. Backups proved unreliable or inaccessible, so the ransom was paid. The company got lucky and the recovery key worked and they got their documents back. What they didn’t get back was Active Directory. Ouch! Nobody could login, even though their documents were back on a server, nobody could access them.
A week later, a new domain, and new profiles on everyone’s desktop, new shares, new permissions, and they were back up and running. After everything, the company is back to doing business, but it could have been a much worse situation. A critical note: the ERP system was never at risk and no ERP data was lost since that was safely stored elsewhere.
Moral of the story:
Test your backups. Not just documents, but the whole server. How long does it take to get it back? It should not be more than a few hours.
Just because you can restore files doesn’t mean you can go out, buy a new server and restore your existing workload onto a new server.
If you can’t live without it, and you don’t have the in-house expertise to manage it – outsource it! Let the pros handle the critical IT while you do what you do best: making essential product and making your business grow.
We’ve all done it, at least once. Some of us maybe more than a couple of times, and I know there’s few that are repeat offenders. You know what I’m talking about – the bane of the security admin’s existence – default passwords.
Those are the usernames and passwords that come with every device. Even in this day and age, most systems don’t REQUIRE you to change the credentials that get you system admin rights. The bad guys know that and use it to their advantage.
When most of our business and personal systems are protected with just a name and a basic password (and maybe a trusted network range?), that’s pretty easy pickings for someone with a brute force tool or a sniffer to find out your secrets. And once the bad guys have your credentials, then what? Well after that is when the real dangers begin.
When’s the last time you changed your voicemail PIN from 0000? Perhaps your home router is still admin/password even though the FBI issued a warning for everyone to change it? And how many ERP users keep system admin “manager” around with the default password of… you guessed it. And those accounts open the door wide to anyone wanting to get in; good and bad.
If you have systems exposed to the bad guys (and we all do!) then this post is for you. STOP IT! Even if you told me “Well, none of those systems are internet exposed”, I’d ask “where are the bad actors in your network?”. If you said “outside the firewall”, I’d respond with something like “I dare you to create a share/folder called “payroll” and see how long some curious netizen (aka employee) fell into that folder looking for something juicy.
Imagine splaying your entire infrastructure wide open to someone who just happened to know that Netgear uses admin/password for all their routers? Or that your company name is NOT a good password?
So what’s a concerned system admin gonna do? It’s easy in theory and hard in practice. Here are some digital security tips that will create a stronger password security strategy:
1. Change the default username and change the default password.
2. Start using stronger passwords, not [email protected] We recommend pass phrases, or a sentence that you can remember but the bag guys will have a hard time guessing.
3. Enable account lockout so that if “x” bad passwords are guessed in a row, the account is locked FOREVER (not reset after 10 minutes, thank you Microsoft). Helpdesk notification of such a lockout will put you in the know.
4. Remove admin credentials from being used on untrusted networks. Yes, your users are untrusted! Create a management VLAN, or a specific set of IP’s that can RDP, or shutdown the access from outside devices altogether.
5. Enable multi-factor authentication. This can easily be enabled in Office 365 and Active Directory, and if your devices leverage that directory then they automatically get that 2FA protection as well.
6. Hack yourself! Run a network scanner, or hire an outsourced IT firm to investigate for you, find the unsecured devices and fix them before the bad guys do.
7. Let us help you! We can run an ethical scan IT Assessment Detective scan of your systems, attempt to break into your systems, and give you a full reporting of your IT weaknesses. As “they say” knowledge is power.
So, don’t let your next phone call to the EstesGroup be “help me, I got hacked!” And let our managed IT services company help you run your business better with a strong password security strategy – before the bad guys teach you a lesson.
Interested in Outsourcing your IT? Or have a question on data security? Ask us, we would love to chat.
Epicor ERP is a powerful platform with thousands of manufacturers using it to run their businesses. With power often comes complexity, and that’s been the case with earlier versions of the system. There is no perfect ERP system, and the ever-changing balance between functionality and usability is a constant series of trade-offs. Epicor ERP Version 9 often required multiple servers, performance tuning was critical, it had a Progress data base layer, even when running on SQL, and the user experience was challenging. A personalized Epicor ERP demo is the perfect beginning to your Epicor consulting journey.
Epicor invested $25M in Epicor ERP Version 10, developing a completely new platform. The system was written and optimized for Microsoft .NET Framework and the Microsoft Data Platform, including Microsoft SQL Server. Users will experience a big increase in performance (over Epicor 9) and find the system easier to manage.
What you’ll see in your Epicor ERP demo
According to Epicor, here are the Top 5 user ERP system experience enhancements for Epicor ERP 10.
Responsiveness – Performance has doubled and scalability has quadrupled across virtually all aspects of the system. ERP 10 is much more hardware efficient, which dramatically lowers hardware costs.
Simplicity – ERP 10 services are hosted purely using Microsoft Windows® components, including Internet Information Services (IIS) and Microsoft .NET. An all new management architecture makes deployment and migration much easier.
Mobility – Touch-enabled devices are now supported for a new navigation system and a re-architected Epicor Web Access (EWA) browser client.
Collaboration – Epicor Social Enterprise is included with ERP 10 and is a new way for ERP users to interact with each other and with ERP data.
Choice – ERP 10 can be deployed on premise, hosted, or access via subscription. It is also much easier to create a high-performing virtualized infrastructure.
The current version, Epicor 10.2, introduces some really exciting capabilities that you’ll see in your ERP demo, including Active Home Page and Epicor Data Discovery (EDD). Here are some highlights:
Developed using the latest web standard, which makes the system mobile-friendly and responsive.
Manufacturing role-based KPIs, examples: Percentage of Jobs without Scrap or Non Conformance, Manufacturing Hours and Indirect Hours.
Finance and Supply Chain role-based KPIs, including: Price Variance, Open PO Count and Amount, and Negative Inventory Items/Out of Stock.
Customization capabilities to modify out-of-the-box KPIs or create entirely new ones based on existing or newly created BAQs.
Epicor Consulting: How can we help?
What do you want to see? Our Epicor consultants will show it to you. The best way to get an in-depth look at the new Epicor 10.2 functionality is to experience it firsthand! Our Epicor consulting team can give you a demo of the full system, or one of our ERP specialists can walk you through a specific module. We can help you with project planning, including Epicor budgeting so you experience increased revenue at every step of your ERP upgrade. Your personalized Epicor ERP demo can even show you the newest managed ERP hosting capabilities.
As a small business owner, did you know there are several green IT practices you can do to help save on your electric bill? All this technology we use costs us not only to buy and maintain it, but it burns electricity and creates heat. Some of those things run 24x7x365, but others can be turned off when not in use, or at least turned down. Just as you (should) turn off the lights when you leave a room, so can your technology be ‘user-aware’ and save power when not in active use.
The good news is that the newer technology is greener than ever. Even the new networking hardware is energy aware. Check out D-Link’s page for lots of tips, not necessarily on their hardware. When it’s time for a hardware upgrade, think green! There’s good reference material on Vertatique.com for going green in the office.
Servers: The ‘iron’ itself runs all the time (except when it’s broken) and Windows servers default to a recommended power saving mode. The monitor turns off, drives spin down and CPU sockets/cores get disabled when not in use. The best way you can save money here is to make sure that the power settings are at Balanced (recommended). Beware, there are some exceptions. If your servers are virtual, then the power savings decisions actually fall on the hypervisor and NOT the VM. Actually, a green IT practice that is best way to save money on your servers (purchase, power and cooling) is to move them to the cloud! While enterprise data centers have their own challenges, a small business would do well to consider moving some operations to the cloud.
PCs: Since Windows machines default to a recommend power saving scheme, you should be OK. You can use Intel Power Management to actually force machines to hibernate (as opposed to sleep) if your infrastructure supports that. Of course, if you can turn your PC off at the end of the day and your IT department doesn’t complain, do that! Many companies do scans, patches and updates at night, so this might not be recommended. For example, our EstesCloud Client Care machines do that patching and scans on Monday night.
Laptops: Hibernate when you can, and follow the manufacturer’s recommendations for battery life. Often, just closing the lid will force the sleep/hibernate cycle and save juice.
Mobile: We all try to eke out every last minute from our smartphones. I recommend installing a power saving app that will stop background processes, turn off the screen and improve charging characteristics. I use the 360 Security app for this and more features.
Printers: Most printers now go into a power-saving mode, at the cost of a few moments warming back up when you go to print. You might consider replacing occasionally used ink-jet printers with low-cost lasers so the ink doesn’t keep drying out and needing to be replaced!
At home: If you have a remote-enabled device like a TV, DVR, DVD or stereo, it’s drawing power even when it’s “off”. I put my entertainment system on a surge-protector, and when we’re done for the night – I flip the power off on all those power-sucking vampire devices. I also put my Wifi router and cable modem on a light-timer that cycles off every night. This has multiple benefits – I save power when it’s off, my kids can’t easily get online late at night, and those home routers tend to be more stable when restarted on a regular basis. Win-win strategy!
In addition to doing our part to save the planet, another great advantage of eco-friendly IT is that you’ll save a few bucks!