Find Office Professional 2019 not installed on a large device collection

Question

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?

Answer 1

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.

Answer 2

Hi@Kody ,

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.

Answer 3

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')

Same attempt with the distinct Device
InstalledSoftware
| where (ProductName == 'Local Administrator Password Solution')
| distinct Device

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')

Same attempt with the distinct Device of looking for devices not having Microsoft LAPS
InstalledSoftware
| where (ProductName != 'Local Administrator Password Solution')
| distinct Device

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?

Answer 4

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

Second query is to search for the 8 Office 2016 devices that do not have Office 2019

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

Answer 5

Thanks, @Amandayou-MSFT