Notification

Icon
Error

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

Posted: Tuesday, November 20, 2012 7:39:55 PM(UTC)
Kreg

Kreg

Member Original PosterPosts: 26
0
Like
This issue has been solved! Click here to view the solution
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
Lansweeper
#1Lansweeper Member Posts: 13,442  
posted: 11/21/2012 11:32:18 AM(UTC)
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.
Quote:
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.
Kreg
#2Kreg Member Original PosterPosts: 26  
posted: 11/21/2012 1:02:11 PM(UTC)
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
Lansweeper
#3Lansweeper Member Posts: 13,442  
posted: 11/21/2012 6:04:57 PM(UTC)
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.
Quote:
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

Kreg
#4Kreg Member Original PosterPosts: 26  
posted: 11/23/2012 11:37:12 AM(UTC)
Just perfect !
Thank you :)
Paranitharan
#5Paranitharan Member Posts: 1  
posted: 11/10/2014 9:46:56 AM(UTC)
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.
RC62N
#6RC62N Member Posts: 441  
posted: 11/12/2014 10:41:05 PM(UTC)
It's not working for your LANSweeper 5.1 because the query was for LANSweeper 4.

Updating:
Code:
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.
Code:
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.

Active Discussions

Lansweeper "add cc user" doesn't show the correct listings
by  JLPingree   Go to last post Go to first unread
Last post: Yesterday at 8:12:44 PM(UTC)
Lansweeper Default User Date Format
by  RickW99456  
Go to last post Go to first unread
Last post: Yesterday at 5:00:51 PM(UTC)
Lansweeper Duplicate assets (Servers)
by  FrankSc   Go to last post Go to first unread
Last post: Yesterday at 3:20:28 PM(UTC)
Lansweeper Lansweeper load the disk subsystem
by  Alexey Gorbachev  
Go to last post Go to first unread
Last post: Yesterday at 3:13:17 PM(UTC)
Lansweeper Lost Configuration tab (Admin rights)
by  kspap   Go to last post Go to first unread
Last post: Yesterday at 10:30:12 AM(UTC)
Lansweeper Deployment with different user rights
by  Jupiter_IT  
Go to last post Go to first unread
Last post: Yesterday at 9:39:20 AM(UTC)
Lansweeper cisco fuji device not linking with connected devices
by  char   Go to last post Go to first unread
Last post: 7/5/2020 9:12:07 AM(UTC)
Lansweeper Microsoft CVE-2020-1425
by  Richard_B  
Go to last post Go to first unread
Last post: 7/3/2020 4:29:41 PM(UTC)