Drill down in custom report

[rvgfox]

I make a report to get all the Antivirus software instaled.

Here's the code:

SELECT     TOP (1000000) tblSoftware.softwareName AS Software, COUNT(tblcomputers.ComputerUnique) AS Equipos
FROM         tblcomputers INNER JOIN
                      tblSoftware ON tblcomputers.Computername = tblSoftware.ComputerName CROSS JOIN
                      tsysantivirus
WHERE     (tblSoftware.softwareName LIKE tsysantivirus.Software)
GROUP BY tblSoftware.softwareName

My problem it's that I cannot make drill down in the Software field.

What's wrong?

[Norgenator]

Rvgfox wanted to create a report listing anti-virus software installed in his network and have the anti-virus names link to lists of computers with any version of that software. This is not currently possible. You can only link to specific versions of a software package.

Oh I see. I apologize, I miss understood. That would be quite efficient.

[Norgenator]

This report worked great! I added it to keep track of active computers that did not have the latest AV installed.

Thanks!

[Hemoco]

No, this is not currently possible unfortunately. You can only drill down to computers that have a specific version of a software package installed.

[rvgfox]

Ok it works, but I get a record by version.

Really I'm interesting in the software name, not in the version.

Is it possible make drill down in that way?

[Norgenator]

rvgfox wrote:
Ok it works, but I get a record by version.

Really I'm interesting in the software name, not in the version.

Is it possible make drill down in that way?

This worked for me.. shows the SW Name.

I included UserName and Domain.

Removed: tbl.computers.ComputerUnique, "Equipos"

Also, reordered columns to show: Username, Computer, Domain, Software, Software Version.


Select Top (1000000) tblComputers.Username, tblComputers.ComputerUnique, tblComputers.Computername, tblComputers.Domain, tblSoftware.softwareName As Software, tblSoftware.softwareVersion As Version From tblComputers Inner Join tblSoftware On tblComputers.Computername = tblSoftware.ComputerName, tsysantivirus Where tblSoftware.softwareName Like tsysantivirus.Software Group By tblComputers.Username, tblComputers.ComputerUnique, tblComputers.Computername, tblComputers.Domain, tblSoftware.softwareName, tblSoftware.softwareVersion Order By Count(tblComputers.ComputerUnique) Desc

[Hemoco]

Norgenator wrote:

rvgfox wrote:
Ok it works, but I get a record by version.

Really I'm interesting in the software name, not in the version.

Is it possible make drill down in that way?

This worked for me.. shows the SW Name.

Rvgfox wanted to create a report listing anti-virus software installed in his network and have the anti-virus names link to lists of computers with any version of that software. This is not currently possible. You can only link to specific versions of a software package.

[Hemoco · Accepted solution]

You must add the tblSoftware.softwareVersion field to your report and assign it the alias "Version".

Modified report:

Select Top (1000000) tblSoftware.softwareName As Software, tblSoftware.softwareVersion As Version, Count(tblComputers.ComputerUnique) As Equipos From tblComputers Inner Join tblSoftware On tblComputers.Computername = tblSoftware.ComputerName, tsysantivirus Where tblSoftware.softwareName Like tsysantivirus.Software Group By tblSoftware.softwareName, tblSoftware.softwareVersion Order By Count(tblComputers.ComputerUnique) Desc