Problem with report 92

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

Problem with report 92

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> Problem with report 92

Page: [1]

Message

<< Older Topic Newer Topic >>

Problem with report 92 - 6/9/2008 10:14:54 AM

mhughes2

Posts: 35
Score: 0
Joined: 3/7/2008
Status: offline

Report 92= Computers with specific software registered in Add Remove Programs

Whenever I attempt to run this report against my All Systems collection in an attempt to see how many machines have Microsoft Office Enterprise 2007 installed, it always ends up displaying two entries for each computer, thus doubling the amount in the results. Any ideas?

Post #: 1

RE: Problem with report 92 - 6/9/2008 2:18:39 PM

jnelson993

Posts: 724
Score: 89
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

Man, I'm having Deja Vu...we just had this conversation with someone...oh, looks like that one was never resolved.

Well, execute this code in SQL Management Studio (or Query Analyzer if you're using SQL 2000) against the CM database. Modify the SET statements in this code to put in the same displayname & collid that you've been putting into the report, and also put in the resourceID of a machine that you know is receiving dupes.

What you'll get are 3 result sets, one that shows the records that come back from v_R_System, one that comes back from v_Add_Remove_Programs, and one that comes back from v_FullCollectionMembership. To find out which view is giving you the duplicates, you look at the results for each of the result sets. If all of the fields all the way across are duplicated identically for each duplicate row, it's NOT the source of your dupes. If, however, one of the fields in a row is different than the same field in the other rows, then that is the source of duplicates. Let us know how it goes.

DECLARE @DisplayName VARCHAR(128)
DECLARE @CollID VARCHAR(8)
DECLARE @ResourceID INT

SET @DisplayName = 'Microsoft Office Enterprise 2007'
SET @CollID = 'xxxxxxxx'
SET @ResourceID = 00000000

SELECT
sys.*
FROM
v_R_System sys
JOIN v_Add_Remove_Programs arp
    ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm
    ON sys.ResourceID = fcm.ResourceID
WHERE DisplayName0 = @displayname
      AND fcm.CollectionID = @CollID
      AND sys.resourceID = @ResourceID

SELECT
arp.*
FROM
v_R_System sys
JOIN v_Add_Remove_Programs arp
    ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm
    ON sys.ResourceID = fcm.ResourceID
WHERE DisplayName0 = @displayname
      AND fcm.CollectionID = @CollID
      AND sys.resourceID = @ResourceID

SELECT
fcm.*
FROM
v_R_System sys
JOIN v_Add_Remove_Programs arp
    ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm
    ON sys.ResourceID = fcm.ResourceID
WHERE DisplayName0 = @displayname
      AND fcm.CollectionID = @CollID
      AND sys.resourceID = @ResourceID

< Message edited by jnelson993 -- 6/9/2008 2:20:01 PM >

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to mhughes2)

Post #: 2

RE: Problem with report 92 - 6/9/2008 2:22:58 PM

mhughes2

Posts: 35
Score: 0
Joined: 3/7/2008
Status: offline

Ugh, I just ran report 93 "Software Registered In Add Remove Programs on a specific machine" and it looks like Microsoft Office Enterprise 2007 is listed twice!
What the heck?

(in reply to mhughes2)

Post #: 3

RE: Problem with report 92 - 6/12/2008 4:57:00 PM

MhermanNBME

Posts: 62
Score: 3
Joined: 4/4/2007
Status: offline

I'm not seeing the issue, but if all 3 columns across are the same, you should be able to just change your "SELECT" to "SELECT DISTINCT", that will remove any duplicates in the results. If they're not the same, I would think you can just use a "GROUP BY" at the end of your query on display name, and all display names will only show up once.

(in reply to mhughes2)

Post #: 4