question

Kody-2675 avatar image
0 Votes"
Kody-2675 asked Kody-2675 answered

Find Office Professional 2019 not installed on a large device collection

Hello,

I'm trying to figure out how to query for devices that does not have Microsoft Office Professional Plus 2019 installed. When I use this query,

InstalledSoftware | where (ProductName == 'Microsoft Office Professional Plus 2019 - en-us')

It finds all of the devices that have Office 2019 installed. But, when I try using something like,

InstalledSoftware | where (ProductName != 'Microsoft Office Professional Plus 2019 - en-us')

It lists all of the software that's not Office 2019 in each devices. What's the query syntax that shows me devices that does NOT have office 2019 installed?

mem-cm-cmpivot
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Amandayou-MSFT avatar image
1 Vote"
Amandayou-MSFT answered

Hi,

Thanks for your reply.

Based on my research, cmpivot may not support nested queries. I check the situation in my environment, it is the same as yours. Actually, we could use cmpivot to query for devices that have Microsoft Office Professional Plus 2019 installed, but figure out do not have specific software, it maybe difficult to achieve it.

To simple it, we could query for devices that have Microsoft Office Professional Plus 2019 installed, paste it in Excel, and then paste the collection in it, filter out devices that do not have the software.

Thanks and have a nice day.



If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Amandayou-MSFT avatar image
0 Votes"
Amandayou-MSFT answered Amandayou-MSFT commented

Hi@Kody-2675,

What's the query syntax that shows me devices that does NOT have office 2019 installed?

We could try to use the following query to achieve it:

InstalledSoftware | where (ProductName != 'Microsoft Office Professional Plus 2019 - en-us')
| distinct Device


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

May we know the current status of the question? If there is any other assistance we can provide, please feel free to let us know, we will do our best to help you.

Thanks and regards,
Amanda

0 Votes 0 ·
Kody-2675 avatar image
0 Votes"
Kody-2675 answered Amandayou-MSFT commented

Hi @Amandayou-MSFT,

Thank you. It seems to work. I wanted to make sure that it only finds devices does not have Office 2019 installed. So, I tested it a few times and it seems to find the the devices the might have Office 2019, I think. I decided to test on a smaller collection using the same Query but for a different 'InstalledSoftware'.

On this device collection, all of the devices have Microsoft's Local Administrator Password Solution (LAPS). My goal is to verify that the query does NOT find any devices with LAPS.

Here's my first attempt
InstalledSoftware
| where (ProductName == 'Local Administrator Password Solution')
174528-cmpivot-installedsoftware-or-not-01.png


Same attempt with the distinct Device
InstalledSoftware
| where (ProductName == 'Local Administrator Password Solution')
| distinct Device
174547-cmpivot-installedsoftware-or-not-02.png

Attempt finding a device that does NOT have Microsoft LAPS. This query finds all software that's NOT Local Administrator Password Solution
InstalledSoftware
| where (ProductName != 'Local Administrator Password Solution')
174573-cmpivot-installedsoftware-or-not-03.png


Same attempt with the distinct Device of looking for devices not having Microsoft LAPS
InstalledSoftware
| where (ProductName != 'Local Administrator Password Solution')
| distinct Device
174556-cmpivot-installedsoftware-or-not-04.png

Am I not thinking this right? My end goal is to only show devices that doesn't have LAPS installed. In this test, it shouldn't show me any devices. But, it's shows the 13 devices on the 4th screenshot. I think I was getting the same results with the query below.
InstalledSoftware
| where (ProductName != 'Microsoft Office Professional Plus 2019 - en-us')
| distinct Device

Is it possible to show only devices that does NOT have the software installed or not show any?



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Thanks for your update.

I run the query in my environment, the tab of device shows the following result. The screenshot you provided shows the result, but the tab of device is empty, so we should check the reason the tab is empty, not the query's correction.

175279-217.png

Best regards,
Amanda


0 Votes 0 ·
217.png (8.9 KiB)
Kody-2675 avatar image
0 Votes"
Kody-2675 answered

Hi @Amandayou-MSFT,

To answer your question "The screenshot you provided shows the result, but the tab of device is empty" I edited the screenshot and blocked out the server names for security reasons.

I created a new device collection with 10 devices. Two have Office 2019s and the other 8 with Office 2016s, Note I've renamed the devices as either Office 2016 or Office 2019.

First query is to verify that it can find the office 2019 devices
175952-cmpivot-installedsoftware-office-professional-plus.png

Second query is to search for the 8 Office 2016 devices that do not have Office 2019
175963-cmpivot-installedsoftware-office-professional-plus.png

As you can see it found all 10 devices including the 2 devices that have office 2019. Shouldn't the query only find the 8 Office 2016 devices?

Thanks,
Kody




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Kody-2675 avatar image
0 Votes"
Kody-2675 answered

Thanks, @Amandayou-MSFT

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.