File Type Aggregation

%3CLINGO-SUB%20id%3D%22lingo-sub-287606%22%20slang%3D%22en-US%22%3EFile%20Type%20Aggregation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287606%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Day%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20general%20question%20hoping%20in%20getting%20ideas%2Fguidance.%26nbsp%3B%20Part%20of%20our%20business%20or%20user%20based%20asked%20if%20there%20is%20a%20way%20to%20somehow%20aggregate%20the%20document%20file%20type%20that%20is%20stored%20in%20OneDrives.%26nbsp%3B%20In%20other%20words%20our%20clients%20are%20looking%20to%20find%20out%20how%20many%20excel%20files%2C%20pdf%20files%2C%20onenote%20files%2C%20etc%20are%20being%20hosted%20on%20OneDrives.%26nbsp%3B%20The%20are%20some%20good%20out%20of%20the%20box%20reports%20in%20the%20tenant%20admin%20center%20for%20OneDrive%20showing%20metrics%20like%20number%20of%20files%2C%20active%20files%2C%20storage%20used%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20get%20the%20aggregated%20count%20of%20file%20types%2C%20is%20this%20something%20that%20could%20potentially%20be%20captured%20through%20an%20API%20or%20would%20we%20need%20for%20example%20in%20creating%20columns%20that%20can%20capture%20file%20type%20details%20so%20that%20we%20can%20then%20get%20a%20count%20doing%20this%20programmatically.%26nbsp%3BThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-287606%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOneDrive%20for%20Business%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-290430%22%20slang%3D%22en-US%22%3ERe%3A%20File%20Type%20Aggregation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290430%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20recommend%20to%20use%20PowerShell%20as%20the%20best%20solution.%3CBR%20%2F%3Epaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288924%22%20slang%3D%22en-US%22%3ERe%3A%20File%20Type%20Aggregation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288924%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20all%20for%20the%20feeback%2C%20I%20really%20appreciate%20it.%20The%20goal%20would%20be%20in%20targeting%20several%20OneDrives%20(a%20slice%20covering%20one%20division%20not%20for%20the%20whole%20company).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288824%22%20slang%3D%22en-US%22%3ERe%3A%20File%20Type%20Aggregation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288824%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Juan%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20looking%20for%20a%26nbsp%3Bsolution%20to%20analyse%20multiple%20OneDrive%20for%20Business%20libraries%20or%20do%20you%20want%20individual%20users%20to%20be%20able%20to%20generate%20such%20a%20report%20for%20their%20own%20environment%20only%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPaul%20%7C%20SLIM%20Applications%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288570%22%20slang%3D%22en-US%22%3ERe%3A%20File%20Type%20Aggregation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288570%22%20slang%3D%22en-US%22%3E%3CP%3EThere%20isn't%20a%20specific%20API%20call%20that%20does%20this%20Our%20of%20the%20Box.%26nbsp%3B%20There%20are%20a%20couple%20of%20potential%20approaches%20depending%20on%20if%20you%20need%20hard%20numbers%20or%20just%20estimates.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20a%20rough%20estimate%20is%20OK%2C%20then%20I%20would%20suggest%20setting%20up%20a%20search%20scope%20that%20only%20searches%20OneDrive%20locations%20and%20then%20you%20can%20use%20file%20type%20as%20a%20filter%20and%20get%20a%20rough%20count.%26nbsp%3B%20It%20won't%20be%20perfect%2C%20but%20it%20should%20give%20you%20an%20order%20of%20magnitude%20for%20the%20tenant.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20approach%20would%20be%20to%20write%20some%20PowerShell%20to%20walk%20the%20Documents%20DocLib%20in%20each%20users%20OneDrive%20and%20then%20count%20the%20totals%20by%20file%20extension.%26nbsp%3B%20This%20will%20be%20slow%2C%20but%20accurate.%26nbsp%3B%20I%20wrote%20up%20a%20set%20of%20PowerShell%20that%20will%20pull%20the%20usage%20of%20all%20user's%20OneDrive%20for%20Business.%26nbsp%3B%20What%20you%20would%20need%20to%20do%20is%20change%20the%20Function%20GetODUsage%20to%20walk%20the%20files%20in%20the%20Documents%20and%20Total%20by%20File%20Type.%26nbsp%3B%20Here%20is%20the%20link%20to%20the%20article%20where%20I%20talk%20about%20how%20to%20pull%20all%20of%20the%20OneDrive%20usage%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fblogs.catapultsystems.com%2Fdbroussard%2Farchive%2F2015%2F10%2F20%2Fpull-onedrive-for-business-usage-using-powershell%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fblogs.catapultsystems.com%2Fdbroussard%2Farchive%2F2015%2F10%2F20%2Fpull-onedrive-for-business-usage-using-powershell%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20found%20an%20article%20where%20a%20person%20has%20the%20code%20to%20pull%20all%20files%20in%20a%20DocLib%20and%20write%20out%20their%20names.%26nbsp%3B%20You%20should%20be%20able%20to%20make%20just%20a%20small%20change%20to%20pull%20counts%20instead.%26nbsp%3B%20Here%20is%20that%20link%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.morgantechspace.com%2F2017%2F06%2Fget-all-list-items-in-library-using-powershell.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.morgantechspace.com%2F2017%2F06%2Fget-all-list-items-in-library-using-powershell.html%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20use%20my%20code%20to%20get%20the%20URLs%20for%20everyone%2C%20then%20use%20this%20block%20to%20open%20it%20using%20CSOM%20then%20you%20should%20be%20able%20to%20get%20everything%20that%20you%20need.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Regular Contributor

Good Day

 

This is a general question hoping in getting ideas/guidance.  Part of our business or user based asked if there is a way to somehow aggregate the document file type that is stored in OneDrives.  In other words our clients are looking to find out how many excel files, pdf files, onenote files, etc are being hosted on OneDrives.  The are some good out of the box reports in the tenant admin center for OneDrive showing metrics like number of files, active files, storage used etc.

 

To get the aggregated count of file types, is this something that could potentially be captured through an API or would we need for example in creating columns that can capture file type details so that we can then get a count doing this programmatically. Thank you!

4 Replies
Highlighted

There isn't a specific API call that does this Our of the Box.  There are a couple of potential approaches depending on if you need hard numbers or just estimates.

 

If a rough estimate is OK, then I would suggest setting up a search scope that only searches OneDrive locations and then you can use file type as a filter and get a rough count.  It won't be perfect, but it should give you an order of magnitude for the tenant.

 

Another approach would be to write some PowerShell to walk the Documents DocLib in each users OneDrive and then count the totals by file extension.  This will be slow, but accurate.  I wrote up a set of PowerShell that will pull the usage of all user's OneDrive for Business.  What you would need to do is change the Function GetODUsage to walk the files in the Documents and Total by File Type.  Here is the link to the article where I talk about how to pull all of the OneDrive usage  http://blogs.catapultsystems.com/dbroussard/archive/2015/10/20/pull-onedrive-for-business-usage-usin...

 

I found an article where a person has the code to pull all files in a DocLib and write out their names.  You should be able to make just a small change to pull counts instead.  Here is that link: https://www.morgantechspace.com/2017/06/get-all-list-items-in-library-using-powershell.html

 

If you use my code to get the URLs for everyone, then use this block to open it using CSOM then you should be able to get everything that you need.

Highlighted

Hi Juan,

 

Are you looking for a solution to analyse multiple OneDrive for Business libraries or do you want individual users to be able to generate such a report for their own environment only?

 

Paul | SLIM Applications

Highlighted

Thank you all for the feeback, I really appreciate it. The goal would be in targeting several OneDrives (a slice covering one division not for the whole company).

Highlighted

I would recommend to use PowerShell as the best solution.
paul