Problem with License and product key and product id's

[Ismail]

Hello,


I created a custom report that gives me all "MS office" products with their product id's and keys.

SELECT DISTINCT
dbo.tblSoftware.softwareName AS Software,
dbo.tblSoftware.ComputerName,
dbo.tblSoftware.Installdate,
dbo.tblSoftware.Lastchanged,
tblComputers.LastknownIP,
tblSerialnumber.ProductKey,
tblSerialnumber.ProductID
FROM
dbo.tblSoftware
INNER JOIN tblComputers ON (dbo.tblSoftware.ComputerName = tblComputers.Computername)
INNER JOIN tblSerialnumber ON (dbo.tblSoftware.ComputerName = tblSerialnumber.Computername)
WHERE
dbo.tblSoftware.softwareName LIKE '%Microsoft Office%'

The report for some reason gives me duplicate product ID and product Key.


Here are a few examples:

Software ComputerName Installdate Lastchanged LastknownIP ProductKey ProductID

Microsoft Office XP Professional with FrontPage CARS-ACCTS4 20030731 29-04-09 10:27 192.168.1.127 KV*********B 5***-OEM-*****-*****
Microsoft Office XP Professional with FrontPage CARS-ACCTS4 20030731 29-04-09 10:27 192.168.1.127 R**********Y 5***-820-*****_*****


My question is, how come I am getting 2 product ID's and 2 product Keys for the same computer and the same software ?


Regards,

[neosys]

Thanks for this SQL Script.

I did a little tiddy update with :
- more MS Office products so other LS users may use it
- also removed the MS Office product entries that were in the list twice !
- sort in alpha order also help...

CODE:
SELECT DISTINCT
dbo.tblSoftware.softwareName AS Software,
dbo.tblSoftware.ComputerName,
dbo.tblSoftware.Installdate,
tblComputers.LastknownIP,
tblSerialnumber.ProductKey
FROM
dbo.tblSoftware
INNER JOIN tblComputers ON (dbo.tblSoftware.ComputerName = tblComputers.Computername)
INNER JOIN tblSerialnumber ON (tblComputers.Computername = tblSerialnumber.Computername)
WHERE
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Premium' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Professional' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 SR-1 Professional' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 SR-1 Standard' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Standard' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 97, Professional Edition' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Access 2003 Runtime' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Access Runtime (English) 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Accounting 2009' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Enterprise 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office FrontPage 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office OneNote 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Professional Edition 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Professional Plus 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Project Professional 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Project Professional 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Project Standard 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Project Standard 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Small Business Edition 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Small Business Edition 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Standard 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Visio Standard 2003'
dbo.tblSoftware.softwareName = 'Microsoft Office Visio Standard 2007'
dbo.tblSoftware.softwareName = 'Microsoft Office Web Components' OR
dbo.tblSoftware.softwareName = 'Microsoft Office XP Professional' OR
dbo.tblSoftware.softwareName = 'Microsoft Office XP Professional with FrontPage'

[Hemoco]

Don't bother looking, the problem is your query.

For example if a user has both "Onenote" and "Visio" you will have duplicate entries in your view.

[Ismail]

I cleaned up the code and got better results. I am still getting duplicate Product Keys for the same product. I will connect remotely to some of the computers registry and check manually.



SELECT DISTINCT
dbo.tblSoftware.softwareName AS Software,
dbo.tblSoftware.ComputerName,
dbo.tblSoftware.Installdate,
dbo.tblSoftware.Lastchanged,
tblComputers.LastknownIP,
tblSerialnumber.ProductKey
FROM
dbo.tblSoftware
INNER JOIN tblComputers ON (dbo.tblSoftware.ComputerName = tblComputers.Computername)
INNER JOIN tblSerialnumber ON (tblComputers.Computername = tblSerialnumber.Computername)
WHERE
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Standard' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Professional' OR
dbo.tblSoftware.softwareName = 'Microsoft Office XP Professional with FrontPage' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Professional Edition 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 SR-1 Professional' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Enterprise 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Project Professional 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 SR-1 Standard' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Premium' OR
dbo.tblSoftware.softwareName = 'Microsoft Office 97, Professional Edition' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Professional Edition 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office FrontPage 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Project Professional 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Web Components' OR
dbo.tblSoftware.softwareName = 'Microsoft Office XP Professional' OR
dbo.tblSoftware.softwareName = 'Microsoft Office OneNote 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office XP Professional with FrontPage' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Access 2003 Runtime' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Access Runtime (English) 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Accounting 2009' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Enterprise 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office OneNote 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Professional Edition 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Project Professional 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Project Standard 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Small Business Edition 2003' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Standard 2007' OR
dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2007'

[Ismail]

Table dbo.tblSerialnumber column Product does not have all the information Table dbo.tblSoftware column Softwarename has.

When I run your Query I only get Computers with:

Microsoft Office Professional Edition 2003

[Hemoco]

What about something like this?

SELECT DISTINCT 
                      dbo.tblComputers.Computername, dbo.tblComputers.LastknownIP, dbo.tblSerialnumber.ProductKey, dbo.tblSerialnumber.ProductID, 
                      dbo.tblSerialnumber.Product
FROM         dbo.tblComputers LEFT OUTER JOIN
                      dbo.tblSerialnumber ON dbo.tblComputers.Computername = dbo.tblSerialnumber.Computername
WHERE     (dbo.tblSerialnumber.Product LIKE '%office%')

[Ismail]

I'm trying to find out who has MS Office installed and alongside with that I am trying to find out the product key and the product ID of every MS office version installed on every workstation on the network.

I put %Microsoft office% since there are too many variants of MS office installed on our network.


Microsoft Office 2000 Standard
Microsoft Office 2000 Professional
Microsoft Office XP Professional with FrontPage
Microsoft Office Professional Edition 2003
Microsoft Office 2000 SR-1 Professional
Microsoft Office 2000 Standard
Microsoft Office Visio Professional 2003
Microsoft Office Enterprise 2007
Microsoft Office Project Professional 2003
Microsoft Office 2000 SR-1 Standard
Microsoft Office 2000 Premium
Microsoft Office 97, Professional Edition
Microsoft Office Professional Edition 2003
Microsoft Office FrontPage 2003
Microsoft Office Project Professional 2003
Microsoft Office Visio Professional 2003
Microsoft Office Visio Professional 2007
Microsoft Office Web Components
Microsoft Office XP Professional
Microsoft Office OneNote 2003
Microsoft Office XP Professional with FrontPage
Microsoft Office XP Professional with FrontPage
Microsoft Office 2000 Professional
Microsoft Office 2000 SR-1 Standard
Microsoft Office Access 2003 Runtime
Microsoft Office Access Runtime (English) 2007
Microsoft Office Accounting 2009
Microsoft Office Enterprise 2007
Microsoft Office OneNote 2003
Microsoft Office Professional Edition 2003
Microsoft Office Project Professional 2007
Microsoft Office Project Standard 2007
Microsoft Office Small Business Edition 2003
Microsoft Office Standard 2007
Microsoft Office Visio Professional 2007
Microsoft Office Word Viewer 2003
Microsoft Office XP Media Content
Microsoft Office XP Professional

That many that is.

[Hemoco]

Your query is wrong.

I'm not sure what you are trying to do but for example my pc has 3 items like '%microsoft office' and only one serial in the other table (which is normal).

Software
Microsoft Office 2003 Web Components
Microsoft Office Professional Edition 2003
Microsoft Office Visio Standard 2003