SCCM Query regarding Onedrive versions installed at user laptops.

Hello,

Does the below give you what you need?  It was a tad slow to run on our system but gets there eventually!

select SMS_R_System.Name, SMS_G_System_SoftwareProduct.ProductName, SMS_G_System_SoftwareProduct.ProductVersion from  SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SoftwareProduct on SMS_G_System_SoftwareProduct.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("8","9","10","14") and SMS_G_System_SoftwareProduct.ProductName like "%OneDrive%"

I think i tried this script, we are looking to get the report from sccm regarding onedrive, how many systems are having new version and how many systems are having older version installed.

this is little difficult because New OneDrive is not included in add/remove program list, so our query will not find anything.
New OneDrive was installed to certain Windows logon user’s profile folder, path %localappdata%\Microsoft\OneDrive.

configuration baseline is one approach, but it is not giving correct reporting data in sccm. Please suggest if any other way to generate this report, thanks.

as per my opinion there is only 2 way, either from SQL query or from configuration baseline. Please suggest someone, this is quite urgent. thanks.

What version of OneDrive do you have? I'm running 17.3.6998.0830 and this appears within Add / Remove Programs and appears in the query results.

we also having 17.3 version of windows 10 version, did you run same query which you shown above, please confirm.

and what about older version which is 16.x (GROOVE.EXE) and it is coming with MS office version so it will not show in add & remove programs.

Yes, we are running Windows 10 also and running the query above does return the 17.3 version of OneDrive.  

Unfortunately I don't know about the older version as we currently only have version 17.x on our estate.

You could try including an addition 'where' clause to include the executable name:

where SMS_G_System_INSTALLED_EXECUTABLE.ExecutableName like "%groove.exe%"

this script can be execute from below path please confirm. just reconfirming because I tried multiple script and non of them work.

sccm console, -- assets and compliance -- queries.

SCCM Console -> Monitoring -> Queries

got it, thank you so much, let me test and confirm to you.

I don't know why but it is not running.  I want to know what below link doing. ChassisTypes in ("8","9","10","14"). kindly help.

There are a number of chassis types that can be used when writing queries to distinguish between different types of hardware. The ones in the query above refer to Portable, notebook, laptop and sub-notebook.

There are a number of others - see the link below for what they are!

https://blogs.technet.microsoft.com/breben/2009/07/21/chassis-type-values-in-sccm/

Ok then let me make only laptop, others i can remove.

no it is not running.

If you change %onedrive% to something else, say %office% does that return any results?

no it is not running.  now can i make any changes to below office query which is running successfully and use it for onedrive.

select SMS_R_System.Name, SMS_R_System.Client, SMS_G_System_INSTALLED_SOFTWARE.ProductName, SMS_G_System_OPERATING_SYSTEM.Caption, SMS_R_System.LastLogonUserName, SMS_R_System.SystemOUName, SMS_G_System_CH_ClientSummary.ClientActiveStatus, SMS_R_System.LastLogonTimestamp, SMS_G_System_WORKSTATION_STATUS.LastHardwareScan from  SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "Microsoft Office Professional%" or SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC"

or lets use below Visio query and we can use it for onedrive and test.

select SMS_R_System.Name, SMS_R_System.Client, SMS_G_System_INSTALLED_SOFTWARE.ProductName, SMS_G_System_OPERATING_SYSTEM.Caption, SMS_R_System.LastLogonUserName, SMS_R_System.SystemOUName, SMS_G_System_CH_ClientSummary.ClientActiveStatus, SMS_R_System.LastLogonTimestamp, SMS_G_System_WORKSTATION_STATUS.LastHardwareScan from  SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%Visio%"

If the above query is working, I would think that changing

where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "Microsoft Office Professional%" or SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC"

To

where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%onedrive%" or SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC"

Should work.  You could potentially delete ‘or SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC" as well.

let me try quickly,

ok now, this query result showing only microsoft onedrive for business 2013 installed. i think this is old version which is coming with MS office and which has MS office 2013 installed in there machines which is groove.exe

attached is onedrive for business which is coming with MS office and new one is coming with windows 10 OS.

for you what result is coming while running this script

someone please give suggestion if possible.

Below WMI query which gives output of add & remove programs, not giving detail of microsoft onedrive installation, thats why with SCCM console query result is blank, any solution for this problem, please suggest.

Get-WmiObject -Class Win32Reg_AddRemovePrograms

If you run the below it should bring back a list of all executables that have been collected.  Do you see onedrive.exe or groove.exe listed?

select ie.executablename  from  SMS_G_System_INSTALLED_EXECUTABLE IE

If I run the below command in powershell then onedrive and groove.exe is not listed.

Get-WmiObject -Class Win32Reg_AddRemovePrograms

The above query isn't searching add/remove programs, it is searching for executables even if they aren't in add/remove programs.

how to run below command, which version of powershell required to run this command. I am using below.
select ie.executablename  from  SMS_G_System_INSTALLED_EXECUTABLE IE

Name             : ConsoleHost
Version          : 5.1.14393.1715
InstanceId     : b2346c63-f503-492c-bb58-6fa8b90aa81d
UI                    : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture   : en-US
CurrentUICulture : en-US
PrivateData      : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy
DebuggerEnabled  : True
IsRunspacePushed : False
Runspace         : System.Management.Automation.Runspaces.LocalRunspace

Sorry, should have said, this is within SCCM Queries.

Please explain again which command need to run in sccm queries.

Create a new query with the following:

select ie.executablename  from  SMS_G_System_INSTALLED_EXECUTABLE IE

This should return all executable that have been scanned by sccm.  Hopefully, in the results you should see onedrive.exe.  If so, we can then expand the query to get version number etc.

Ok, i got 1220 result, command run sccessfully in SCCM.

Result: I can see GROOVE.EXE but i can't found onedrive.exe in search result. Please advice.

Ok at least I am getting GROOVE.EXE, can you please suggest script to run report for GROOVE.EXE at least.

any other way to get onedrive.exe executable in sccm console, please suggest, thanks.

Ok, so we can get the Groove file versions using the following SCCM query:

select ie.executablename, ie.fileversion  from  SMS_G_System_INSTALLED_EXECUTABLE IE where ie.executablename like "%groove%"

As to why you aren't getting any results for Onedrive, I don't know I'm afraid!

this script giving 1 version of groove.exe, 15.0, we have 16.0 also in our network, my system has 16.0 groove.exe installed which comes with MS office.  don't know what is hapenning and how to enable it or what is the problem.

Please check and help on this issue, this is urgent, thank you.

Can some one please help to get SCCM Query regarding One drive versions installed at user laptops.

I tried several method and scripts but no result. any quick help would be appreciated. thanks.