A while back we installed Zen Asset Inventory (but not Asset Management) since it came with our Novell bundle, and inventory is a nice thing to have. At the beginning of this quarter it started to crash while inventorying certain workstations. After sending the logs to Novell, it turned out to be crashing on a lot of workstations.
Novell said that the reason for the crashes was excessive duplicate workstations. ZAM is supposed to handle this, but it seems 2 years of quarterly lab reimaging seems to have finally overwhelmed the de-dup process. The fix is fairly straight forward, but very labor intensive:
Cleaning out the database proved to be more complicated than I thought. At first I thought I just had to delete all the workstations from the Manager tool. But that would be wrong. Actually looking at the database tables showed a LOT of data in a supposedly clean database.
The very first thing I tried was to remove all the workstations from the database by way of the manager, and restart inventory. The theory here is that this would eliminate all the duplicate entries, so we'd just start the clock ticking again until the imaging caught us out. Since I had modified our imaging procedures, this shouldn't happen again any way. Tada!
Only the inventory process started crashing. Crap.
The second thing I tried was to strobe through the Lab workstations with the WorkstationOID-reset script I worked up in PowerShell (this is not something I could have done without an Active Directory domain, by the way). These are the stations with the most images, and getting them reset should clear the problem. Couple that with a clearing of the database by way of the Manager, and we should be good!
Only the inventory process started crashing. It took a bit longer, but it still crashed pretty quickly.
Try number three... run the powershell script across the ENTIRE DOMAIN. This took close to four days. Empty the database via Manager again, restart.
It crashed. It took until the second day to crash, but it still crashed.
As I had reset the WorkstationOID on all domained machines (or at least a very large percentage of them), the remaining dups were probably in the non-domained labs I have no control over. So why the heck was I still getting duplication problems with a supposedly clean database? So I went into SQL Studio to look at the database tables themselves. The NC_Workstation table itself had over 15,000 workstations in it. Whaaa?
However, this would explain the duplication problems I'd been having! If it had been doing the de-dup processing on historical data that included a freighter full of duplicates already, it was going to crash. Riiiiight. So. How do I clean out the tables? Due to foreign key references and full tables elsewhere, I had to build a script that would purge leaf tables, then core tables. The leaf tables (things like NC_BIOS) could be Truncated, handy when a table contains over a million rows. Core tables (NC_Component) have to be deleted line-by-line, which for the 2.7 million row NC_Component table took close to 24 hours to fully delete and reindex.
With a squeaky clean database, and the large majority of WorkstationOID values reset enterprise wide, I have restarted the inventory process. The Zenworks database is growing at a great pace as the Component tables repopulate. This morning we have 3,750 workstations and growing. We inventoried close to 3,300 stations yesterday and didn't get a single inventory crash. This MAY have fixed it!
I'm keeping these SQL scripts for later use if I need 'em.
They key learning here? Removing the workstations from the Manager doesn't actually purge the workstation from the database itself.
Novell said that the reason for the crashes was excessive duplicate workstations. ZAM is supposed to handle this, but it seems 2 years of quarterly lab reimaging seems to have finally overwhelmed the de-dup process. The fix is fairly straight forward, but very labor intensive:
- Clean out the Zenworks database
- Force a WorkstationOID change on all workstations
- Stop the Collection Client service
- Delete a specific registry key
- Start the Collection Client service
Cleaning out the database proved to be more complicated than I thought. At first I thought I just had to delete all the workstations from the Manager tool. But that would be wrong. Actually looking at the database tables showed a LOT of data in a supposedly clean database.
The very first thing I tried was to remove all the workstations from the database by way of the manager, and restart inventory. The theory here is that this would eliminate all the duplicate entries, so we'd just start the clock ticking again until the imaging caught us out. Since I had modified our imaging procedures, this shouldn't happen again any way. Tada!
Only the inventory process started crashing. Crap.
The second thing I tried was to strobe through the Lab workstations with the WorkstationOID-reset script I worked up in PowerShell (this is not something I could have done without an Active Directory domain, by the way). These are the stations with the most images, and getting them reset should clear the problem. Couple that with a clearing of the database by way of the Manager, and we should be good!
Only the inventory process started crashing. It took a bit longer, but it still crashed pretty quickly.
Try number three... run the powershell script across the ENTIRE DOMAIN. This took close to four days. Empty the database via Manager again, restart.
It crashed. It took until the second day to crash, but it still crashed.
As I had reset the WorkstationOID on all domained machines (or at least a very large percentage of them), the remaining dups were probably in the non-domained labs I have no control over. So why the heck was I still getting duplication problems with a supposedly clean database? So I went into SQL Studio to look at the database tables themselves. The NC_Workstation table itself had over 15,000 workstations in it. Whaaa?
However, this would explain the duplication problems I'd been having! If it had been doing the de-dup processing on historical data that included a freighter full of duplicates already, it was going to crash. Riiiiight. So. How do I clean out the tables? Due to foreign key references and full tables elsewhere, I had to build a script that would purge leaf tables, then core tables. The leaf tables (things like NC_BIOS) could be Truncated, handy when a table contains over a million rows. Core tables (NC_Component) have to be deleted line-by-line, which for the 2.7 million row NC_Component table took close to 24 hours to fully delete and reindex.
With a squeaky clean database, and the large majority of WorkstationOID values reset enterprise wide, I have restarted the inventory process. The Zenworks database is growing at a great pace as the Component tables repopulate. This morning we have 3,750 workstations and growing. We inventoried close to 3,300 stations yesterday and didn't get a single inventory crash. This MAY have fixed it!
I'm keeping these SQL scripts for later use if I need 'em.
They key learning here? Removing the workstations from the Manager doesn't actually purge the workstation from the database itself.
Wow, just wow. This may explain the near exact same problem we have been experiencing with ZENworks Asset Inventory. I cannot believe Novell is leaving it up to the customer to cleanup these leftover workstation records. At the least, there should be some Novell-provided database cleanup scripts.