→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
tessymay
Engaged Sweeper
I already have reports to break down the computers by our departments. Now I'm trying to apply that with getting a totaled number of what Office versions they have within each department. This is what I have so far. I have another report that totals the numbers correctly but this one just says 1 for each... Can you help me out? I need it to come out with 2010 Pro there is Xnumber, 2003 Pro there is Xnumber, etc.

Select Top 100 Percent dbo.tblSoftware.softwareName As Software,
dbo.tblSoftware.SoftwarePublisher As Publisher,
Count(dbo.tblSoftware.ComputerName) As Total, dbo.tblComputers.Computername
From dbo.tblSoftware Inner Join
dbo.tblComputers On dbo.tblSoftware.ComputerName =
dbo.tblComputers.Computername
Where (dbo.tblSoftware.softwareName Like 'Microsoft Office 2000 Professional'
And dbo.tblComputers.Computer Like 'it%') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Professional Edition 2003')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Professional Plus 2007 ') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office XP Professional ') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office XP Standard ') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Professional 2010 ') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Standard Edition 2003 ') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office XP Small Business ')
Group By dbo.tblSoftware.softwareName, dbo.tblSoftware.SoftwarePublisher,
dbo.tblComputers.Computername, dbo.tblComputers.ComputerUnique
Order By Count(dbo.tblSoftware.ComputerName)
3 REPLIES 3
JasonG
Engaged Sweeper
It seems I was mistaken, LS does store the Department attribute. The site I was working from doesn't set the Department on computer accounts, only Users.
SELECT
softwareName AS [Software]
,department AS [Department]
,COUNT(*) AS [Installed]
FROM tblSoftware
LEFT JOIN tblCompCustom
ON tblSoftware.Computername = tblCompCustom.Computername
WHERE
softwareName LIKE 'Microsoft Office%'
AND softwareName NOT LIKE '%Communications Server%'
AND softwareName NOT LIKE '%Live Meeting%'
AND softwareName NOT LIKE '%Viewer%'
AND softwareName NOT LIKE '%Communicator%'
AND softwareName NOT LIKE '%Web Components%'
AND softwareName NOT LIKE '%Resource Kit%'
GROUP BY softwareName, Department
ORDER BY SoftwareName, Department

JasonG
Engaged Sweeper
!!! Ignore this, correction below

As far as I can see, grouping by the AD Department attribute is not possible as LS does not store it anywhere for computers. It is stored for users, but that doesn't help us here. It is rather easy to do by OU. Consider the following:
SELECT
softwareName AS [Software]
,OU AS [OU]
,COUNT(*) AS [Installed]
FROM tblSoftware
LEFT JOIN tblADComputers
ON tblSoftware.Computername = tblADComputers.Computername
WHERE
softwareName LIKE 'Microsoft Office%'
AND softwareName NOT LIKE '%Communications Server%'
AND softwareName NOT LIKE '%Live Meeting%'
AND softwareName NOT LIKE '%Viewer%'
AND softwareName NOT LIKE '%Communicator%'
AND softwareName NOT LIKE '%Web Components%'
AND softwareName NOT LIKE '%Resource Kit%'
GROUP BY softwareName, OU
ORDER BY SoftwareName, OU
Hemoco
Lansweeper Alumni
most likely a problem with the and/or

try this:

Select Top 100 Percent dbo.tblSoftware.softwareName As Software,
dbo.tblSoftware.SoftwarePublisher As Publisher,
Count(dbo.tblSoftware.ComputerName) As Total, dbo.tblComputers.Computername
From dbo.tblSoftware Inner Join
dbo.tblComputers On dbo.tblSoftware.ComputerName =
dbo.tblComputers.Computername
Where ((dbo.tblSoftware.softwareName Like 'Microsoft Office 2000 Professional')
Or
(dbo.tblSoftware.softwareName =
'Microsoft Office Professional Edition 2003') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Professional Plus 2007 ')
Or (dbo.tblSoftware.softwareName = 'Microsoft Office XP Professional ') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office XP Standard ') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Professional 2010 ') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Standard Edition 2003 ')
Or (dbo.tblSoftware.softwareName = 'Microsoft Office XP Small Business '))
And dbo.tblComputers.Computer Like 'it%'
Group By dbo.tblSoftware.softwareName, dbo.tblSoftware.SoftwarePublisher,
dbo.tblComputers.Computername, dbo.tblComputers.ComputerUnique
Order By Count(dbo.tblSoftware.ComputerName)