→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Ismail
Engaged Sweeper II
Hello, I have this report:

SELECT DISTINCT 
dbo.tblSoftware.softwareName AS Software,
dbo.tblSoftware.ComputerName,
dbo.tblSoftware.Installdate,
dbo.tblSoftware.Lastchanged,
dbo.tblSoftware.softwareVersion,
tblComputers.LastknownIP,
tblCompCustom.Department
FROM
dbo.tblSoftware
INNER JOIN tblComputers ON (dbo.tblSoftware.ComputerName = tblComputers.Computername)
LEFT OUTER JOIN tblCompCustom ON (dbo.tblSoftware.ComputerName = tblCompCustom.Computername)
WHERE
dbo.tblSoftware.softwareName = 'Symantec Antivirus'



I would like to show how many each department I have Installed, something like the predefined "All installed Software"

Instead of the total of how many per software I would like how many per department.

This would be a secondary report to the original report attached where it would say:


HR DEPT: 20
IT DEPT 40
FINANCE: 50

etc...

It would be great if somehow it would show how many computers that dont have the symantec AV installed per dept as well.


Regards and many thanks.
6 REPLIES 6
Ismail
Engaged Sweeper II
Very nice indeed, thank you
Hemoco
Lansweeper Alumni
strange, works for me, can you try this one:

SELECT DISTINCT 
TOP 100 PERCENT dbo.tblCompCustom.Department, dbo.tblSoftware.softwareName, dbo.tblSoftware.softwareVersion,
COUNT(dbo.tblSoftware.ComputerName) AS Total
FROM dbo.tblSoftware FULL OUTER JOIN
dbo.tblCompCustom ON dbo.tblSoftware.ComputerName = dbo.tblCompCustom.Computername
GROUP BY dbo.tblSoftware.softwareName, dbo.tblCompCustom.Department, dbo.tblSoftware.softwareVersion
HAVING (dbo.tblSoftware.softwareName = 'Symantec Antivirus')
ORDER BY dbo.tblCompCustom.Department
Ismail
Engaged Sweeper II
It gives me an error report stating; Column 'dbo.tblSoftwareName' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause
Hemoco
Lansweeper Alumni
thy this:

SELECT DISTINCT TOP 100 PERCENT dbo.tblCompCustom.Department, dbo.tblSoftware.softwareVersion, COUNT(dbo.tblSoftware.ComputerName) AS ComputerName
FROM dbo.tblSoftware FULL OUTER JOIN dbo.tblCompCustom ON dbo.tblSoftware.ComputerName = dbo.tblCompCustom.Computername GROUP BY dbo.tblSoftware.softwareName, dbo.tblCompCustom.Department, dbo.tblSoftware.softwareVersion HAVING (dbo.tblSoftware.softwareName = 'Symantec Antivirus')
ORDER BY dbo.tblCompCustom.Department
Ismail
Engaged Sweeper II
Thanks for your input but sadly the report you have given me results in invalid syntax
EPiServer
Engaged Sweeper
Try this one


SELECT DISTINCT
TOP (100) PERCENT dbo.tblCompCustom.Department, dbo.tblSoftware.softwareVersion, COUNT(dbo.tblSoftware.ComputerName)
AS ComputerName
FROM dbo.tblSoftware FULL OUTER JOIN
dbo.tblCompCustom ON dbo.tblSoftware.ComputerName = dbo.tblCompCustom.Computername
GROUP BY dbo.tblSoftware.softwareName, dbo.tblCompCustom.Department, dbo.tblSoftware.softwareVersion
HAVING (dbo.tblSoftware.softwareName = N'Symantec Antivirus')
ORDER BY dbo.tblCompCustom.Department