Report to list machines without certain software

[eaga]

Hi,

We are rolling out a piece of software throughout our business and require a report to show which machines do not have the software installed.

Ideally the output would only display the machine number (no duplicates), username and last seen date.

Has anyone managed to configure such a report? I'd be grateful for any advice.

Thanks in advance.

Darren

[quintinh]

Here's how I do it ..

1) Find a registry entry that identifies your software and add that to be scanned in your config. (seeing Lansweeper's example of using the software table above makes me wonder if that could be used instead).

2) Write a report that lists all the computers that have it installed.

3) Write another report using the computers table that finds all computers that are in the computer's table but not in the table that was created as part of the report in (2).

Example ... Finding all computers that do not have TrendMicro Antivirus installed:

1) Registry entry = HKEY_LOCAL_MACHINE\SOFTWARE\TrendMicro\PC-cillinNTCorp\CurrentVersion

2) report that finds all computers where TrendMicro is installed. Named TrendMicroIsInstalled

Select tblRegistry.Computername 
From tblRegistry 
Where tblRegistry.Regkey Like 'HKEY_LOCAL_MACHINE\SOFTWARE\TrendMicro\PC-cillinNTCorp\CurrentVersion' 
And Not tblRegistry.Value Is Null

3) Report that uses above to find all in Computers that are not in above. Named TrendMicroIsNotInstalled

Select dbo.tblComputers.Computername, dbo.tblComputers.Username, dbo.tblComputers.Lastseen, dbo.tblComputers.Lasttriggered, dbo.tblComputers.LastScheduled, dbo.tblComputers.LastActiveScan, dbo.tblComputers.LastknownIP 
From dbo.tblComputers 
Where dbo.tblComputers.Computername Not In (Select dbo.web30repTrendMicroIsInstalled.Computername From dbo.web30repTrendMicroIsInstalled)

Notes:
You could skip the first "IsInstalled" report and just embed that select statement in the In clause of the "NotInstalled" report. I like having both, though, in case I ever want to look and verify that a specific computer is registered as having it installed.

As stated above, the software table may be a more efficient method than finding a registry key to scan for.

A pair of these reports would have to be written for each software package you want to monitor for.

[eaga]

Hi,

We managed to figure this out eventually. Using the 'Not In' command will show what is required however this will show multiple instances of the same machine.

Adding the 'Distinct' option at the start of the SQL statement will show only one instance of the machine name

We use the following statement to list all machines without PGP Desktop installed.

Select Distinct tblSoftware.ComputerName From tblSoftware Where tblSoftware.ComputerName Not In (Select tblsoftware.ComputerName From tblsoftware Where tblsoftware.softwareName = 'PGP Desktop')

Hope this helps!

Daz

[SPAMHATER007]

Below is the SQL Code to display what you need: Paste into the SQL window, then the tables and links will appear above, modify as needed.

Select tblADComputers.Computername, tblADComputers.Description,
tblADComputers.OperatingSystem, tblADComputers.OU, Query.Software
From tblADComputers Left Join
(Select Top 100 Percent tblADComputers.OU, tblADComputers.Computername,
dbo.tblSoftware.softwareName As Software, dbo.tblSoftware.softwareVersion
As Version, dbo.tblSoftware.SoftwarePublisher As Publisher
From dbo.tblSoftware Right Join
tblComputers On tblComputers.Computername = dbo.tblSoftware.ComputerName
Right Join
tblADComputers On tblADComputers.Computername = tblComputers.Computername
Group By tblADComputers.OU, tblADComputers.Computername,
dbo.tblSoftware.softwareName, dbo.tblSoftware.softwareVersion,
dbo.tblSoftware.SoftwarePublisher
Having dbo.tblSoftware.softwareName Like 'symantec %') Query On
tblADComputers.Computername = Query.Computername
Where Query.Software Is Null

[Hemoco]

it could be simple like this:

select computername from tblsoftware where computername not in (select computername from tblsoftware where softwarename = 'yoursoftware')

[SPAMHATER007]

Lansweeper wrote:
it could be simple like this:

select computername from tblsoftware where computername not in (select computername from tblsoftware where softwarename = 'yoursoftware')

The problem with this code is, it will display a computer names several dozen times for each software that is NOT equal = 'yoursoftware'. It also will not display computers that are listed in AD but have not been scanned yet.

[SPAMHATER007]

I figured it out: Just follow these steps (can only work for one software package, too complex for multiple, unless you a SQL expert)
1) Create report that lists the computers and software package, save report
2) Create report that Lists the computers and information you want to output, save report
3) Copy the entire SQL select statement from the first report
4) Open 2nd report and left-click in the main tab and select "Add Derived Table", you will now see a 2nd tab name Query
5) Select the query tab of the 2nd report, go to bottom of screen in SQL window, paste the SQL statement from report 1 within the () of sql code (Select *) you overwrite ONLY the "Select *"
6) On 2nd report Link the Tables based on ComputerName field and select the software field of the derived table and enter "is null" in the criteria field.

I will post SQL specific info shortly.

[SPAMHATER007]

Yes, I'm looking for the same thing. I see built-in reports for system without .net installed, but haven't been able to duplicate.