scott.hamilton 0 Posted June 19, 2014 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 Share this post Link to post Share on other sites
ins.jvernon 0 Posted June 24, 2014 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
rgreen83 2 Posted July 11, 2014 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
rgreen83 2 Posted August 16, 2014 Regarding the duplicates, it turns out the results were being duplicated in the table due to the lack of a unique index! Share this post Link to post Share on other sites
qcomer 0 Posted September 3, 2014 Has the duplicate issue been resolved? Share this post Link to post Share on other sites
rgreen83 2 Posted September 4, 2014 Has the duplicate issue been resolved? :?: directly above your post Share this post Link to post Share on other sites
mcclain.tim 0 Posted September 4, 2014 I think qcromer is seeking a more detailed explanation or newly updated script that resolves the issue Share this post Link to post Share on other sites
rgreen83 2 Posted September 5, 2014 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
mcclain.tim 0 Posted September 5, 2014 Thanks. I'll check it out soon. Share this post Link to post Share on other sites
mcclain.tim 0 Posted September 5, 2014 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
rgreen83 2 Posted September 6, 2014 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 . Share this post Link to post Share on other sites
scott.hamilton 0 Posted October 23, 2014 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
dsinton44 0 Posted December 1, 2014 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
rgreen83 2 Posted April 17, 2015 (edited) 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 Share this post Link to post Share on other sites
jrocker 0 Posted May 18, 2015 Nice Thanks, it fixed my issue. Back in business. Appreciate the follow through on it. Share this post Link to post Share on other sites
alatham 0 Posted September 9, 2015 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
rgreen83 2 Posted September 9, 2015 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
alatham 0 Posted September 10, 2015 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 0 Posted September 18, 2015 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
alatham 0 Posted September 18, 2015 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
rgreen83 2 Posted September 25, 2015 put the script id # in the script field not the guid like it says Share this post Link to post Share on other sites
deviousdh 0 Posted November 11, 2015 all fixed now thanks Share this post Link to post Share on other sites
damadhatter 0 Posted December 17, 2015 Seen any issues with this and 10.5? Share this post Link to post Share on other sites
rami 1 Posted January 29, 2016 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; Share this post Link to post Share on other sites
rami 1 Posted February 4, 2016 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. Share this post Link to post Share on other sites