2.0.0.74 - Issues with powershell scripts

[joe.mccall]

Hey, great plugin! I found a couple of bugs in the Powershell on the current build, one is a really simple one, the other seems a little more involved. I also ran into a weird GUI glitch where it kept data incorrectly when switching between clients.

No Sharepoint data is being collected due to a bug in the INSERT command. I tried running the Debug output manually through SQLYog and found the typo; changing it to WebCount fixed the query and it inserted the data correctly.

bug-Sharepoint powershell-Webscount query result.JPG (21.51 KiB) Viewed 22914 times

bug-Sharepoint powershell-Webscount instead of Webcount.JPG (28.72 KiB) Viewed 22914 times

GUI Bug -- the mariat@ user is not from this client, but keeps showing up on mailboxes that do not have any rights granted to other users.

bug-phantom mailbox permissions.JPG (40.23 KiB) Viewed 22914 times

[joe.mccall]

The trickier Powershell bug I found is with escaping single quotes. I had one client that was not populating any informtion at all, but when I manually ran the powershell script in ISE, it came back with good results. Once I ran the plugin in Debug mode, I figured out what the problem was.

In Powershell, Chris O'Neil comes through correctly as the accent character, so it does not break the quotes. Paste this output into SQLyog and it runs no problem.

bug-escaped single quote-powershell ISE output.JPG (30.81 KiB) Viewed 22913 times

In Automate, it is breaking the query output and it is not being correctly escaped -- I saw where your script is performing the string replace, but it's missing this one somehow. When you paste the output into SQLyog, you have a bunch of broken strings until you escape the single quotes.

bug-escaped single quote-plugin debug output.JPG (10.18 KiB) Viewed 22913 times

[joe.mccall]

This will fix your MSOLUSERS script. The character being returned is ’ and not ' which is why it is not being escaped.

Code: Select all

$strFirstName = if ($objUser.FirstName){if($objUser.FirstName -contains '"){$objUser.FirstName.Replace("'","''")}else{$objUser.FirstName.Replace("’","''")}}else{$objUser.FirstName}

$strLastName = if ($objUser.LastName){if($objUser.LastName -contains "'"){$objUser.LastName.Replace("'","''")}else{$objUser.LastName.Replace("’","''")}}else{$objUser.LastName}

$strDisplayName = if ($objUser.DisplayName){if($objUser.DisplayName -contains "'"){$objUser.DisplayName.Replace("'","''")}else{$objUser.DisplayName.Replace("’","''")}}else{$objUser.DisplayName}

Upon further review of the Sharepoint script, the "typo" of WebsCount looks like it's actually with the database table creation, as everywhere else in the script the variables are also WebsCount but the column in the database was created as WebCount. I tested renaming the column in the database to WebsCount, and I can copy and paste the debug output into SQLyog and the queries execute successfully, but running the client scan is still not populating the information.

[Cubert]

Awesome post!

Could ask for a better bug find and self fix. I have reviewed all the reported issues and have now updated Office365 to 2.0.0.75 which should fix all the issues reported.

You will need to self "DROP TABLE plugin_sw_office365forlabtech_sharepoint_sites" followed by a DBagent restart for the new table to be created with correct column for those who haven't edited the table.

If you have any issues finding the download links in you accounts here it is for reference

https://delivery.shopifyapps.com/-/67bc ... 82c312973b


Enjoy!

[joe.mccall]

Gave the updated version a try this morning. It looks like there's an encoding error and missing double quote in the MSOLUSERS powershell script that is causing the line to break.

Code: Select all

$strUserPrincipalName = if ($objUser.UserPrincipalName){if($objUser.UserPrincipalName -contains '"){$objUser.UserPrincipalName.Replace("'","''")}else{$objUser.UserPrincipalName.Replace("’","''")}}else{$objUser.UserPrincipalName}

$strFirstName = if ($objUser.FirstName){if($objUser.FirstName -contains '"){$objUser.FirstName.Replace("'","''")}else{$objUser.FirstName.Replace("’","''")}}else{$objUser.FirstName}

I will PM you the working changes that I saved, with the character showing correctly.

[Cubert]

Here is the updated plugin file

https://delivery.shopifyapps.com/-/c1fa ... ba94825b32

[Cubert]

Looks like we are still having similar issues. with a user that has a "O'Mally" so are testing the following code for build 2.0.0.77 Will make a release if this resolves issues.

Code: Select all

if ($objUser.UserPrincipalName){		
        $strUserPrincipalName = $objUser.UserPrincipalName
        $strUserPrincipalName.Replace("'","''")
        $strUserPrincipalName.Replace("’","''")}
if ($objUser.FirstName){
		$strFirstName = $objUser.FirstName
        $strFirstName.Replace("'","''")
        $strFirstUser.Replace("’","''")}
if ($objUser.LastName){
        $strLastName = $objUser.LastName
        $strLastUser.LastName.Replace("'","''")
        $strLastName.Replace("’","''")}
if ($objUser.DisplayName){
        $strDisplayName = $objUser.DisplayName
        $strDisplayName.Replace("'","''")
        $strDisplayName.Replace("’","''")}

[Cubert]

Anyone wanting .77 pre release here it is...

2.0.0.77

Office365ForLabTech.zip

(258.38 KiB) Downloaded 942 times

[Cubert]

2.0.0.77 had similar issues that seem to be with the MSOLUSERS being encoded as UTF-16 which changes 1 char in the script from a normal

Code: Select all

'

to a something else which then we can not match against. This cause "sometimes" users that have a " ' " in their names to not get escaped out. This then breaks the SQL that inserts the data collected.


We are still working on this issue but we have crafted a work around for the current issue.

We now test for the existence of the MSOLUSERS on the probe and if it's there and the correct version we do not try and overwrite it.
You can replace that file with this file on the probe which should be encoded in UTF-8 and will pickup those chars correctly and process the SQL as desired.

Make sure your updated the plugin to build => 2.0.0.78 so the new tests are in place.
https://delivery.shopifyapps.com/-/cfb0 ... 91a08e3f70

Copy this zip down to your probe and extract it.

MSOLUSERS-2.0.3.zip

(2.91 KiB) Downloaded 918 times

Place MSOLUSERS.ps1 here on probe system.

Code: Select all

%windir%\temp\Office365\MSOLUSERS.ps1