Jump to content
scott.hamilton

Find PST Locations

Recommended Posts

Hey,

 

Borrowed and modified bits and pieces from the "Extract Email Account Details" by screwloose and some random VBS code someone posted somewhere on the internet hahah,

 

But anyway this basically gives you a tab that displays all PST file location (see screenshot). This is very useful to us as we've come across multiple clients saving archive.pst files on their local workstations or when we acquire new clients and need to figure out where their pop emails are being saved or if someone has access to pst files they shouldn't really have access too etc.

 

findpst_zps6fd700e7.png

 

There is an issue in that if you run the "Find PST Files" script it will just keep adding results (so if you perform 2 searches you'll have double up info, 3 searches triple up and so on).

 

So if anyone can provide me with an easy solution to the doubling up issue that'd be awesome :P I'll spend some more time figuring it out later.

 

Hope this helps someone out there.

pst.zip

Share this post


Link to post
Share on other sites

Chances are as the script is running it is creating duplicate sets inside the tables which causes it in the tab. At least that is what I can tell from your script. I am not sure how to do this yet, but maybe have something where it checks the file locations based upon what the table says and if they are listed, ignore those locations.

Share this post


Link to post
Share on other sites

Hey this is great Scott! Can I just go in to the table and drop all the columns to clear it out if I have a lot of dupes?

Share this post


Link to post
Share on other sites
I think qcromer is seeking a more detailed explanation or newly updated script that resolves the issue

 

Fine, make me work, lol. Import this sql statement instead of the one included in scotts package, if you have already imported his this will drop the table and recreate it so you will lose any already collected data. This will create the table with a unique index.

 

Also I have modified the script to INSERT ON DUPLICATE UPDATE so i'm attaching a new script as well. I also updated the vbs script to separate on ? instead of , because I found that quite a few people had commas in their pst file name and it was breaking the column sorting, the ? works better for me but Scotts original can be placed back in the scripts directory if desired after importing the script.

 

The custom tab I believe I didn't make any relevant changes to, just remember to update the script id after importing the new one.

 

I've only included updated versions of these files, you will still need Scott's original zip for a new implementation as I didn't want to steal any credit.

pstv2.zip

Share this post


Link to post
Share on other sites
I think qcromer is seeking a more detailed explanation or newly updated script that resolves the issue

 

Fine, make me work, lol. Import this sql statement instead of the one included in scotts package, if you have already imported his this will drop the table and recreate it so you will lose any already collected data. This will create the table with a unique index.

 

Also I have modified the script to INSERT ON DUPLICATE UPDATE so i'm attaching a new script as well. I also updated the vbs script to separate on ? instead of , because I found that quite a few people had commas in their pst file name and it was breaking the column sorting, the ? works better for me but Scotts original can be placed back in the scripts directory if desired after importing the script.

 

The custom tab I believe I didn't make any relevant changes to, just remember to update the script id after importing the new one.

 

I've only included updated versions of these files, you will still need Scott's original zip for a new implementation as I didn't want to steal any credit.

 

 

This worked great. Thanks for doing all the work.

Share this post


Link to post
Share on other sites

Hey excellent work! I haven't been on here in ages as been moved off onto other projects here in the office, but now have been put back onto labtech development so here I am but wow it's great to see others have benefited from this!

 

Excellent work rgreen I'll grab your changes and apply it to our environment when I get a chance, looking forward to seeing it work how I envisioned it from the start :)

Share this post


Link to post
Share on other sites

If i wanted to modify this to keep an eye on OST files as well would i need to modify anything except line 6 in the vbs? could i change the statemenet to

("Select * from CIM_DataFile Where Extension = 'pst' OR 'ost'")

Share this post


Link to post
Share on other sites

So it turns out the modified script I posted earlier did not work correctly, it would only ever return the first found pst file and not others if there were more than 1 on the machine. This is just an updated script, you will still need the previous files.

 

This script is a bit ugly to look at but I have tested it quite a bit and it is doing what it is supposed to now. To be able to use INSERT...ON DUPLICATE KEY UPDATE I had to change the script to use the split string function and loop through the inserts as the string replace wasn't capable of putting the values in the middle of the statement.

 

I also added a DELETE statement before the loop to remove previous rows for that computerid, this can be removed if desired by disabling lines 16 and 17. Having the delete somewhat negates the purpose of using the insert on duplicate but will get rid of stale data, if you want to keep track of any place that pst files have ever existed even if they dont now you can remove the delete.

 

With the delete removed and unique key on path and filenames columns this script will just update the modified date and filesize of the pst on subsequent runs.

Find PST Files.zip

Edited by Guest

Share this post


Link to post
Share on other sites

Script is running successfully, imported the 2 xml's and the SQL

Put the VBS in the share folder. everything appears to be working but no results are populated into a tab. Am i missing something?

Share this post


Link to post
Share on other sites

alatham if I had to guess i would say you don't have permissions to the table, try it from a super admin account or modify the table permissions directly in the db (labtech will undo this after a bit)

 

there is a shortcoming in labtech currently in that you have to write your own plugin to manage permissions if you create a custom db table

Share this post


Link to post
Share on other sites

So since i already imported it without super admin, would i need someone to grant certain permissions or do i have to reimport the files under super?

 

Also, this is a very silly question but what do i do with the 3 .png Files?

Share this post


Link to post
Share on other sites
alatham if I had to guess i would say you don't have permissions to the table, try it from a super admin account or modify the table permissions directly in the db (labtech will undo this after a bit)

 

there is a shortcoming in labtech currently in that you have to write your own plugin to manage permissions if you create a custom db table

 

Imported Everything under super admin, but still not seeing the results in the custom PST Tab.

I am logged in as a super admin at the moment, all work has been done with super. I can run the script and in the commands it shows succesfull i check the PST tab and no results are populated.

Share this post


Link to post
Share on other sites

Got it working!

 

Last thing which im sure is a simple fix, i changed the ID to the most current script but it still has to be ran manually and then it will import the results. However, within the PST tab > The Find PST button does not initiate the script (Minor Detail but still want to get it 100%)

Share this post


Link to post
Share on other sites

I just saw this post and wanted to try it.

Thank you Scott and rgreen83 for your work.

I would like to add one thing (very small thing comparing to your work) :

If you note that date format under "LastModified" is not beautiful :) so I had to edit the SQL table

Open the email_pst_details.sql in notepad, Code, or SQLyog etc. and change line 26:

`LastModified` varchar(25) NOT NULL,

into:

`LastModified` DATETIME NOT NULL,

Then import\Execute it.

And here is the result:

 

 

Who already have this tab, all you need to do is:

Open SQLyog, then

- drop the table:

DROP TABLE IF EXISTS `email_pst_details`;

- Create the table:

CREATE TABLE `email_pst_details` (
 `ID` INT(11) NOT NULL AUTO_INCREMENT,
 `ClientID` INT(11) NOT NULL DEFAULT '0',
 `Path` VARCHAR(255) NOT NULL,
 `Filename` VARCHAR(150) NOT NULL,
 `LastModified` DATETIME NOT NULL,
 `FileSize` VARCHAR(15) NOT NULL,
 `ComputerID` INT(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`ID`),
 UNIQUE KEY `Path_2` (`Path`,`Filename`)
) ENGINE=INNODB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;

Capture.JPG.e7f605181bb58bfc7629f716028b7700.JPG

Share this post


Link to post
Share on other sites

Make me work too :)

I'm still 6 months in LT and very newbie in SQL, but I would like to add another addition if you are interested,

I inserted into the email_pst_details table new column "ProfileName"

 

ALTER TABLE email_pst_details ADD ProfileName VARCHAR (255) NOT NULL;

Then I created script to get the default profile name through powershell command:

$outlookApplication = New-Object -ComObject Outlook.Application
$outlookApplication.Application.DefaultProfileName 

 

Or through getting the registry key value by the following command:

Powershell (Get-ItemProperty -Path 'HKCU:\Software\Microsoft\Office\\Outlook').DefaultProfile

Where is one of the following:

Office XP – 10.0

Office 2003 – 11.0

Office 2007 – 12.0

Office 2010 – 14.0

Office 2013 – 15.0

 

%shellresult% = @ProfileName@

Then insert that to table:

UPDATE email_pst_details
SET profileName = '@ProfileName@'
WHERE ComputerID=%ComputerID%

 

Note: Using UPDATE function will replace the old value with the mentioned new one.

 

And in custom tab adding this column as well

 

Here is the result:

 

 

I stoped here... BUT the main idea of getting the default profile name is to use it in command line to Add, Delete, and Get the PST file into the outlook, using MS small tools from here:

https://support.microsoft.com/en-us/kb/3089746

I'm thinking about adding that in custom tab as couple scripts run by right click on any raw, The Path, FileName, and Profile name will be taken as variables from the columns into those scripts.

It's not done sure, i'm working on that on my time (it may take weeks), but just want to share the idea.

Capture.JPG.6bbaac8acc9d42d9116fcaf7ea1a032f.JPG

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...