Simple query to show all my computers with or without xxx applications

[Kreg]

Hello everybody,

I'm a new happy user of LanSweeper Premium 🙂
I'm not really familiar with SQL but testing quickly, I see that I can not set up a query that will display (for example) all my machines WITH and WITHOUT 'Microsoft Office%'. > In the same query.
I think it is basic? But I do not.
I tested criteria: EXISTS 'Microsoft Office%' -> It returns all applications on all machines
I tested criteria: LIKE 'Microsoft Office%' AND NOT LIKE 'Microsoft Office%' -> This crashes my Lansweeper 🙂

NB : The goal is not to see only 'Microsoft Office%', but another application like 'Microsoft Lync'....

I need your help please ...

Thank you very much,
Kreg

[RCorbeil · Accepted solution]

It's not working for your LANSweeper 5.1 because the query was for LANSweeper 4.

Updating:

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 AS icon,
  tsysOS.OSName,
  tblAssets.SP,
  tblAssets.IPAddress,
  SoftwareCheck.softwareName,
  SoftwareCheck.softwareVersion,
  SoftwareCheck.SoftwarePublisher
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode
  LEFT JOIN ( SELECT
                tblSoftware.AssetID,
                tblSoftwareUni.softwareName,
                tblSoftwareUni.SoftwarePublisher,
                tblSoftware.softwareVersion
              FROM
                tblSoftware
                INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
              WHERE
                (tblSoftwareUni.softwareName LIKE 'Microsoft Office%')
                OR (tblSoftwareUni.softwareName LIKE '%lync%')
            ) AS SoftwareCheck ON SoftwareCheck.AssetID = tblAssets.AssetID
WHERE
  tblAssetCustom.State = 1
ORDER BY
  tblAssets.AssetName

From my inventory, there's more than just Microsoft Office and Lync being flagged because Microsoft makes "Microsoft Office" a common prefix to loads of components that aren't Microsoft Office.

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 AS icon,
  tsysOS.OSName,
  tblAssets.SP,
  tblAssets.IPAddress,
  SoftwareCheck.softwareName,
  SoftwareCheck.softwareVersion,
  SoftwareCheck.SoftwarePublisher
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode
  LEFT JOIN ( SELECT
                tblSoftware.AssetID,
                tblSoftwareUni.softwareName,
                tblSoftwareUni.SoftwarePublisher,
                tblSoftware.softwareVersion
              FROM
                tblSoftware
                INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
              WHERE
                ( (tblSoftwareUni.softwareName LIKE 'Microsoft Office%')
                  OR (tblSoftwareUni.softwareName LIKE '%lync%')
                )
                AND (tblSoftwareUni.softwareName NOT LIKE '%Viewer%')
                AND (tblSoftwareUni.softwareName NOT LIKE '%Add-in%')
                AND (tblSoftwareUni.softwareName NOT LIKE '%Interop%')
                AND (tblSoftwareUni.softwareName NOT LIKE '%Live Meeting%')
                AND (tblSoftwareUni.softwareName NOT LIKE '%Web Components%')
                AND (tblSoftwareUni.softwareName NOT LIKE '%Connector%')
            ) AS SoftwareCheck ON SoftwareCheck.AssetID = tblAssets.AssetID
WHERE
  tblAssetCustom.State = 1
ORDER BY
  tblAssets.AssetName

If you don't want to see Visio or Project ("Microsoft Office Visio blah" and "Microsoft Office Project blah"), you'll want to add exceptions for those to the list, too.

[Paranitharan]

Support team, I am trying utilize the above script to find out Lync installation status. But it returns to me error "Invalid SELECT statement. Unknown object name: "tblComputers".: Unexpected token "tblComputers" at line 0, column -1.

I am using Premium version 5.1.0.66. Please advise.

[Kreg]

Just perfect !
Thank you 🙂

[Kreg]

Thank you for you reply, but I wish to view All of my computers with and without Office.
To show in the same query my user with office 2007, 2010.... and user without office.
You think it's possible ?

And the second point, is, is it possible in adding other applications ?
All of my users + with or without Office + with or without Lync...

Thank you
Kreg

[Hemoco]

Kreg wrote:
To show in the same query my user with office 2007, 2010.... and user without office. You think it's possible ?

That's what the query we provided will list. It will list all of your computers and any Office installations present on those computers. Please try running the report and let us know if it requires any changes.

Kreg wrote:
And the second point, is, is it possible in adding other applications ?
All of my users + with or without Office + with or without Lync...

Use the report below.

Select Top 1000000 Web40OSName.Compimage As icon, tblComputers.Computername, tblComputers.Computer, tblComputers.Domain, Web40OSName.OSname As OS, Web40OSName.SP, tblComputers.LastknownIP As IP, SoftwareCheck.Software, SoftwareCheck.Version, SoftwareCheck.Publisher From tblComputers Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join (Select tblSoftware.ComputerName, tblSoftware.softwareName As Software, tblSoftware.softwareVersion As Version, tblSoftware.SoftwarePublisher As Publisher From tblSoftware Where tblSoftware.softwareName Like 'Microsoft Office%' Or tblSoftware.softwareName Like '%lync%') SoftwareCheck On SoftwareCheck.ComputerName = tblComputers.Computername Order By tblComputers.ComputerUnique

[Hemoco]

Kreg wrote:
I see that I can not set up a query that will display (for example) all my machines WITH and WITHOUT 'Microsoft Office%'. > In the same query.

Could you please clarify what you would like to do. Do you want to:
- List all computers in your network?
- Add a software column to the report that only lists Microsoft Office installations detected on these computers?

If so, please use the report below.

Select Top 1000000 Web40OSName.Compimage As icon, tblComputers.Computername, tblComputers.Computer, tblComputers.Domain, Web40OSName.OSname As OS, Web40OSName.SP, tblComputers.LastknownIP As IP, SoftwareCheck.Software, SoftwareCheck.Version, SoftwareCheck.Publisher From tblComputers Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join (Select tblSoftware.ComputerName, tblSoftware.softwareName As Software, tblSoftware.softwareVersion As Version, tblSoftware.SoftwarePublisher As Publisher From tblSoftware Where tblSoftware.softwareName Like 'Microsoft Office%') SoftwareCheck On SoftwareCheck.ComputerName = tblComputers.Computername Order By tblComputers.ComputerUnique

To use the specified report, do the following:
• Open the Lansweeper configuration console and select Reports & Alerts\Report Builder. Hit the “New” button.
• Copy the SQL code provided and paste it at the bottom of the newly created report, replacing the default SQL code.
• Click somewhere near tblComputers so the new code applies.
• Give the report a “View name” and a “Report name” and hit the “Save” button.
• Double-click on the report in the report list to see its results and export options.