Find PST Locations

[scott.hamilton 0]

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.

 

 

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 I'll spend some more time figuring it out later.

 

Hope this helps someone out there.

pst.zip

[ins.jvernon 0]

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.

[rgreen83 2]

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?

[rgreen83 2]

Regarding the duplicates, it turns out the results were being duplicated in the table due to the lack of a unique index!

[qcomer 0]

Has the duplicate issue been resolved?

[rgreen83 2]

Has the duplicate issue been resolved?

:?: directly above your post

[mcclain.tim 0]

I think qcromer is seeking a more detailed explanation or newly updated script that resolves the issue

[rgreen83 2]

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

[mcclain.tim 0]

Thanks. I'll check it out soon.

[mcclain.tim 0]

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.

[rgreen83 2]

You're welcome, I did a lot less than Scott. And I got to learn a fair amount about sql along the way which makes it fun .

[scott.hamilton 0]

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

[dsinton44 0]

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'")

[rgreen83 2]

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 May 19, 2015 by Guest

[jrocker 0]

Nice Thanks, it fixed my issue. Back in business. Appreciate the follow through on it.

[alatham 0]

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?

[rgreen83 2]

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

[alatham 0]

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?

[alatham 0]

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.

[alatham 0]

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%)

[rgreen83 2]

put the script id # in the script field not the guid like it says

[deviousdh 0]

all fixed now thanks

[damadhatter 0]

Seen any issues with this and 10.5?

[rami 1]

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;

[rami 1]

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.