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: 455  
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 Email to close ticket
by  wgonzalez_hope   Go to last post Go to first unread
Last post: Yesterday at 3:54:01 PM(UTC)
Lansweeper Mass update assets through csv
by  B.L.  
Go to last post Go to first unread
Last post: Yesterday at 3:30:21 PM(UTC)
Lansweeper Restrict the access to the knowledgebase
by  ABEAL   Go to last post Go to first unread
Last post: Yesterday at 1:38:25 PM(UTC)
Lansweeper Wake on Lan Issues
by  Christophe  
Go to last post Go to first unread
Last post: Yesterday at 1:09:12 PM(UTC)
Lansweeper Reason: No email address found for the following user
by  Juha Otava   Go to last post Go to first unread
Last post: Yesterday at 9:59:41 AM(UTC)
Lansweeper Windows 10 Upgrade to 2004
by  CyberCitizen  
Go to last post Go to first unread
Last post: Yesterday at 8:47:16 AM(UTC)
Lansweeper Scanning despite exclusion
by  pskup   Go to last post Go to first unread
Last post: Yesterday at 7:27:08 AM(UTC)
Lansweeper Export ticket to CSV
by  KeithBecker  
Go to last post Go to first unread
Last post: 8/13/2020 5:56:10 PM(UTC)