SQL Query for SMS report

Does anyone have a SQL Query that I can use to pull the size of PST files in different file locations for a custom SMS report?
OWilliamsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

matrixnzCommented:
Hi OWilliams

Unfortunately PST files aren't part of the standard applications that SMS reports on, it only does .exe, .com etc..  The only way to have it added is to create a custom .mif file deploy it to all your systems and than wait for the information to be captured.  However this would be quite difficult as from your post it seems that the PST files are in different locations.  The only other way I could think of as a quick fix was to create a package that runs for all staff which then updated a csv file or spreadsheet, with the PST information.

Cheers
0
OWilliamsAuthor Commented:
Would you know of any third party utilities that can located PST files and the size of them?
0
stirrinCommented:
Hi,

By default, Only the .exe files are queried on the local computer. This includes all the fixed drives on the computer. So I guess locations wouldn't be a problem as long as the computer is a SMS client.

You can enable the Software Inventory for PST's by
 Site Database->Site Hierarchy-><Site Name>->Site Settings->Client Agents
Right click on Software  Inventory Client Agent->Properties
On the Inventory collection tab, Add the .pst file as a new entry below the .exe entry.
Once the client computer reports back the inventory data according to the schedule, You should be able to see all the inventoried PST files.


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
matrixnzCommented:
I'm assuming you wish to associate it to the user?  Although you can capture all PSTs on computers/servers it's difficult to associate it to a specific user, especially since the default names are usually outlook.pst.  My thoughts are that you capture it within the users session, then append the user information with the psts found, in theory the pst files captured will depend on the users privileges, although it wouldn't help if the user was a Local Administrator.  Unless all the PSTs reside within the users profile in which case stirrins' suggestion above should work.

To find the path of Users PSTs' within registry is rather difficult as the paths are written in hex making it difficult for scripts to decipher.

Cheers

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Server OS

From novice to tech pro — start learning today.