Gaps in Office Telemetry Data

[Jeremy Dahl]

Hi folks,

I'm running into a weird issue that I'm hoping someone might be able to help me shed some light on - I have Office Telemetry installed in a multi-geo company (about 12 sites in 10 different countries). We've been running Telemetry from almost a year now, and we have some very strange gaps in our reporting data that I can't figure out how to resolve. 

For example, I have a lot of computers on my list that do not report the Office Version back into Telemetry - like so: 

I used this computer as an example to show that it has the Telemetry agent installed, and has gathered the Office installation from other computers in the site - I have massive gaps like this with over a thousand computers reporting everything back to the Telemetry server, but not reporting their Office version. For the life of me, I cannot figure this one out!

The second thing that's really bothering me with this deployment is that the Agents tab in the Telemetry dashboard has multiple duplicates - I can see why they're there, as Telemetry is reporting back each user logon to that computer. However, when Telemetry is reporting back multiple Office installs, how can I go back and have any certainty on my numbers that are showing up on the Deployments tab? I can't figure out any way of clearing out this stale data, and I have to way for the default 90 days for that old data to drop off, but it doesn't leave me with a lot of confidence in the Telemetry reports.

Finally, I don't really see where the deployment numbers are coming from - I've been monitoring that dashboard for the last few months as we've started deploying ProPlus, and while the ProPlus numbers have gone up, so have the Office 2010 numbers! I'm at a loss to explain that to my project team, and we're really losing confidence in the data.

I know I've got a lot going on here, but I'm hoping someone can shed some light into how I can manage and control my data in order to make sure I'm actually getting some usable insights. Thanks!

[#2]

I understand you are having trouble with Office telemetry reporting. Sorry about that.

1st question-
w.r.t office version not reported-
Please see if there is a pattern that you could find in what cases the Office Version is missing. This should help us identify and fix the issue. 

How to find it?

If possible, try contacting few users of those records and remote login/see in their actual machine:

1. Is it Office 2010 with Agent installed as MSI OR ProPlus C2R with inbuilt Agent
2. What actual version is installed? Are they all pointing to a specific version/atleast a set of minor versions?
   There was a bug in Telemetry Agent which caused incorrect version to be reported in certain cases (and was fixed in 16.0.8023.1000) but this looks different.
   Still, from the agent version, it looks its ProPlus 16.0.6925.* so if feasible can you see if migrating those users to latest ProPlus (> 16.0.8023.1000) helps fix this issue.
   for e.g. i see 16.0.8201.* reporting Office version correctly in snapshot you shared; worth trying to upgrade to that atleast.
3. If 2. doesn't help, though unlikely it may sound we can't rule out below scenarios. I understand its thousands of computers but still check few users and see if we not missing anything obvious:
   • Is the installation corrupt - agent gathers data mostly from registry so if office is corrupted or unsuccessfully uninstalled, then lingering registry is getting uploaded but not office version.
   • does the user and hence the Telemetry Agent running in Task scheduler face some sort of permission issue where its unable to scan certain HKLM office registy paths/mso.dll in Office installation paths to capture office version.
     

     HKLM\Software\Microsoft\Office\ClickToRun\propertyBag\version (pre Aug 2016 builds)
     HKLM\Software\Microsoft\Office\ClickToRun\Configuration\VersionToReport (post Aug 2016 builds)

2nd, 3rd questions-
How deployment numbers are calculated?
Deployments are calculated based on office installation (OFFICE_SKU) as detected by telemetry agent from data gathered from last scan of agents (within last 90 days window). So a machine shut-downed or disconnected but its data if we got 10 days back (i.e. <90 days) then its still taken into account.
You can search for SQL stored procedure in the Office Telemetry SQL database in the name of ‘GetOfficeInstallationStatus’ to see the exact logic of this.

Interesting snippet:

/* We count an Office install only if it was detected during the most recent scan,
and if that was sometime within the last 90 days. */
and SI.LastSeenTime >= UOC.LastScanTime
and SI.LastSeenTime >= dateadd(day, -90, getutcdate())

Multiple versions of office in same computer is supported and captured and again its reported based on last scan of agent in that computer (and if that scan had happened within last 90 days).

Now as you might have guessed, you can remove that

and SI.LastSeenTime >= dateadd(day, -90, getutcdate())

and manually run the stored procedure to get *current* (based on latest scan data of agent) deployment status. I think this is what you are calling as stale data. Note, obviously, this can still show you some stale data if agent has not run and uploaded data since the Office uninstallation.

I currently dont have a precise answer on why it was designed this way but i guess its to be consistent with few other calculations which are computed for last 90 days window.

w.r.t Office 2010 numbers gone up:

Its difficult to comment without understanding your enterprise setup; but if you are saying you uninstalled Office 2010 but still they show up, its because of last 90 days window. Atleast it should have remained constant and it should not go up; Removing 90 days window should give you with those numbers removed. If you are saying, it has really *gone up* then its something else, you could compare the users list of earlier report with current and see if there are *new users/computers* with Office 2010.

Hope this helps,

Hari

[#3]

Thanks for this, Hari - that actually helps a lot! Looking back over my data gaps, I see that every affected computer has the agent version 16.0.6925.1049, so it looks most likely that there is either a corrupt agent install, or potentially just the bug that you've mentioned. I'll explore further and see if updating the agent on one of the affected computers makes it report back correctly. Regardless, once the computers are upgraded to ProPlus (with of course, a newer Telemetry agent), they start to report back to the Telemetry server - so that is at least a step in the right direction.

I also noticed another strange gap when looking for these patterns - here is a computer that has had multiple logons, but the Office version not captured each time:

Is that by design, or is there something else going on here? If it's by design, am I able to create a view that basically does something like "find me the latest reported logon to this computer, and the associated Office version"?

Secondly, I went in and updated the stored procedure that you mentioned, and re-ran it. I noticed that I finally have what looks like a much more accurate count of the systems in my environment:

Prior to updating the stored procedure, the number of 2010 installs was around 570, and so I was missing more than half of the total number of PCs across my org (around 2000 in total). I'm unsure if this is something I'll have to do again, or whether or not the stored procedure should be left as it was. If you have any suggestions as to a best practice there, that'd be great.

Thanks for stepping in and providing some answers - this has been driving me crazy for a while now!