Duplicate entries in SQL tables

[Raymond Kim]

Greetings . . .

I've looked at other postings concerning duplicate entries in R_ views with no adequate solution.

I'll use the R_Inventory view as an example: I discovered that the CIM_ComputerSystem table (one of the tables from which R_Inventory is derived) may have multiple entries for the same nodeID (which the view uses to join with other tables using the DeviceKey column). One thing that it picks up is differences in DeviceStatus, ServerRole, and ProductType -- just enough so that the rows are not unique. Another thing it seems to pick up are either virtual/alternate server names or names that no longer exist, i.e. a server's previous name.

Does anyone know why the CIM_ComputerSystem behaves in this manner? I have a separate database to keep track of our server environment that relies partially on CIM/SIM to populate it. It assumes the R_ tables/views are unique (or at least the ones that are supposed to be unique, such as R_Inventory). I didn't have this problem with CIM 7, but can't seem to get around it in SIM 4.

I'm working on a workaround by creating R_ views in a separate SQL DB. (I added a "distinct" keyword to the SQL code, which helps, but still doesn't completely solve the problem.) If someone could make my life in this respect easier, it'd be greately appreciated.

Thanks for your time . . .

[Rob Buxton]

All i've done is identify those Servers that have duplicate records, delete the Server and re-add it.

Here it was only Servers that have gone through some kind of change.
It's a pain as typically our servers are first built and left as workgroup and are added to the domain later. This typically seems to result in the duplicate entries.
The first does not have a FQDN and the second does.

I agree this did not seem to behave this way in CIM 7, I always thought the R_ Tables were refreshed. But with the change to views I'm no longer sure how the mechanism works.

[Raymond Kim]

Yeah, me neither, which is why I posted the question, and why I'm experimenting with the alternate database. I'm hoping to have an R_ view structure that mimics the behavior of CIM 7 as closely as possible.

If I make any progress, I'll definitely post it somewhere on this forum.

[Rob Buxton]

Of course the ideal solution is that this gets sorted in the next release!

I'm also ensuring the duplicates don't exist before we put Servers into production. That way we can keep the historical data intact.

For us the main problem area is where a new Server comes in and we do some Server renaming to preserve a name. It now looks as though we may lose that continuity here. Even worse, we may need to delete and re-add the Server name so may end up losing all historical data over time.

Any HP Responses?

[Raymond Kim]

To anyone else who happens to be reading this . . .

I was reading another (now closed) thread with symptoms related to this one. They discussed how disabling WBEM in SIM took care of the problem. Do the symptoms in this thread sound like the same thing?

Unfortunately, I don't have admin access to SIM, so I'll have to talk to the person who does in order to try it.

[Rob Buxton]

Raymond,
In my case no, I'd disabled WBEM. I think the issue for me was that the Server was originally built and discovered as a workgroup server and had a name that was not a FQDN.
It then got added to the domain, acquired its FQDN and from there we had multiple entries.

This doesn't match all the Servers that had duplicate entries. But once the entries were there, disabling WBEM didn't seem to make a difference.

In the old CIM, I was under the impression the Report Tables were recreated by the data collection run. Because of the changes I don't think this happens, so once you have a duplicate entry I think your stuck with it.

[Raymond Kim]

So in other words, if a server is built discovered, renamed, and rediscovered, then the old server name/info remains in the database? Crap.

I read your posting on the other thread, and you're right; deleting the record(s) is not an ideal solution.

Thanks for your help anyway. I'm going to continue experimenting with the views in a separate database. If you happen across a robust, workable solution to this issue, please post again; I'd really like an answer to this myself.

[Raymond Kim]

Rob (and everyone else):

I created a new database called SIMStage and created views in the DB (derived from the R_ reporting views in HPSIM). (I didn't want to build them in the HPSIM DB since I didn't want to alter anything that was used by HPSIM and potentially damage the app.)

This way, I was able to massage the R_ views to mimic more closely the original CIM 7 reporting tables without altering the HPSIM infrastructure. Not only that, I've also been able to make improvements to it. In my testing environment, I'm using this new SIM staging DB almost exclusively and am not touching the HPSIM tables or views at all.

I've managed to get around most (but not all) of the duplicate entry issues. I need to play around with the T-SQL code some more in order to iron out most of the other issues.

Obviously, this only applies to people (like me) who are (primarily) using data directly from the HPSIM back-end database, rather than using the web Java-based front-end interface.

[Rob Buxton]

It will be interesting to see what the changes in HPSIM 4.2 introduce.
There's been some comments that some of the duplication issues are addressed.

[Joel Rubenstein]

SIM 4.2 has significantly reduced the duplicate report data.