→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
rvgfox
Engaged Sweeper
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?

1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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

View solution in original post

7 REPLIES 7
Norgenator
Engaged Sweeper
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
Engaged Sweeper
This report worked great! I added it to keep track of active computers that did not have the latest AV installed.

Thanks!

Hemoco
Lansweeper Alumni
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
Engaged Sweeper
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?

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
Lansweeper Alumni
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
Lansweeper Alumni
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