Power Shell convert from excel to csv

 $excelFile = "D:\Test\excelfile\file.xlsx"
    $E = New-Object -ComObject Excel.Application
    $E.Visible = $false
    $E.DisplayAlerts = $false

 $wb = $E.Workbooks.Open($excelFile)
    foreach ($ws in $wb.Worksheets)
    {
        $n = $excelFileName + "_" + $ws.Name
    }

   
Function ExportWSToCSV ($excelFileName, $csvLoc)
{
    $excelFile = "D:\Test\excelfile\" + $excelFileName + ".xlsx"
    $E = New-Object -ComObject Excel.Application
    $E.Visible = $false
    $E.DisplayAlerts = $false
    $wb = $E.Workbooks.Open($excelFile)
    foreach ($ws in $wb.Worksheets)
    {
        $n = $excelFileName + "_" + $ws.Name
        $ws.SaveAs($csvLoc + $n + ".csv", 6)
    }
    $E.Quit()
}
ExportWSToCSV -excelFileName "file" -csvLoc "D:\Test\csv file\"

stop-process -processname EXCEL

$ens = Get-ChildItem "D:\Test\excelfile\" -filter *.xlsx
foreach($e in $ens)
{
    ExportWSToCSV -excelFileName $e.BaseName -csvLoc "D:\Test\csv file\"
}

powershell send sharepoint site report to email

$enddate = (Get-Date).tostring("yyyyMMdd")
$filename = 'D:\report\' + $enddate + '_staffdocs.xml'
stsadm.exe -o enumsites -url https://abc.com  > "$filename"

$From = "mysite_report@abc.com"
$To = "ng_sin_peow@abc.com"
$Cc = "YourBoss@abc.com"
$Attachment = "$filename"
$Subject = "My Site Report (One Drive) "
$Body = "Report for my site , please open with excel"
$SMTPServer = "smtp.abc.com"
$SMTPPort = "25"
Send-MailMessage -From $From -to $To -Subject $Subject -Body $Body -SmtpServer $SMTPServer -port $SMTPPort -Attachments $Attachment

SharePoint Production farm shut down and boot up sequence

Shut down SharePoint server by following sequence

1.       Web front end server ( Can shut down all web front end server at the same time )

2.       Application Server ( Can shut down all application server at the same time )

3.       Database Server (Can shut down all database server  at the same time )

Boot up SharePoint server by following sequence

1.       Database Server (Can boot up all database server   at the same time)

2.       [ Need to wait all DB server completely up ] Application Server (Can boot up all application server at the same time)

3.       [ Need to wait all Application server completely up ] Web front end server ( Can boot up all web front end server at the same time )

About IM-8 Auditing ~ SSL RC4 Cipher Suites Supported

Recently we have some security audit is failed , so we need to fix something to pass the audit.  small part of the IM-8 we need to fix about the .

  SSL RC4 Cipher Suites Supported

 SSL Version 2 and 3 Protocol Detection

after ask around then found out need to change the server registry key to disable some other the RC4 , SSL V2 and V3 setting.

To make the task simple we have some simple tools for it :

first we need the scan tools , which is Nmap tools, you can download from internet.

after install and run following script :

nmap --script ssl-enum-ciphers 172.xx..yy.zz   -p 443

you will get the results look as following , as you see the SSlv3 is enable. 

Dowload NARTAC Software here : https://www.nartac.com/Products/IISCrypto/Download

After install then you can choice the setting you want , click Apply then Restart . Done.

OneDrive For Business ( On Premies ) issues

OneDrive For Business issues

• Unable to run SYNC with OneDrive client 
• Unable to Open With Explorer
• Unable to Edit Microsoft file with client office

All the standard checking at client and server for SharePoint setting is done but still unable to resolve the issues .  web client service ,  IE setting [Trusted site] and so on ...all checked.

Lastly find out is because of the  : ISAPI Filters  at IIS  have the URLSCAN cause it unable to open with explorer and edit document

Resolution : remove URLSCAN from ISAPI Filters  at IIS 

Install Solution powershell script

using powershell ISE need to run as Administrator
=====================================


if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null)
{
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}
$WEB_APP = "http://xxxx"
$SITE_URL = "http://xxxx/sca"
$SolutionPath = "D:\Sharepoint Applications\xxxsca.wsp"
$Features = @("xxxsca")

Write-Host "Adding solution:" $SolutionPath
Add-SPSolution -LiteralPath $SolutionPath | Out-Null
Write-Host "Solution added successfully."
Write-Host

$SolutionPackage = Get-Item -LiteralPath $SolutionPath
Write-Host "Deploying solution:" $SolutionPackage.Name
Install-SPSolution -Identity $SolutionPackage.Name -GACDeployment -WebApplication $WEB_APP

$Solution = Get-SPSolution -Identity $SolutionPackage.Name
while ($Solution.JobExists) {
        Start-Sleep -Seconds 10
}

Write-Host "Solution deployed successfully."
Write-Host

foreach ($featureName in $Features)
{
        Write-Host "Enabling feature:" $featureName
        Enable-SPFeature -Identity $featureName -Url $SITE_URL
        Write-Host "Feature enabled successfully."
        Write-Host
}

Read-Host "Finished. Press Enter to exit"

SQL query : Shrink SQL log file

use content_database_name

ALTER DATABASE content_database_name SET RECOVERY SIMPLE

DBCC SHRINKFILE(content_database_name_log )

ALTER DATABASE content_database_name SET RECOVERY FULL

Sql query check instance name and sever name

SELECT @@servicename AS Servicename --returns instance name, even if this is the default (MSSQLSERVER)

SELECT SERVERPROPERTY ('InstanceName') AS InstanceName --null mean is defalut

select @@SERVERNAME AS SQLclusterName

SELECT   CONVERT(char(20), SERVERPROPERTY('servername'))AS WindowsClusterName --use this name to connect to db

SELECT @@SERVERNAME  AS pre_change_name, --returns servername\instance as far as this is not the default instance

 Serverproperty('Servername') AS current_server_name 

Put the today date to powershell for file name

https://social.technet.microsoft.com/Forums/scriptcenter/en-US/01085081-bbea-4cc1-b957-cb99c7f4fe4a/powershell-saving-a-file-with-the-date-in-the-filename?forum=ITCG

$enddate = (Get-Date).tostring("yyyyMMdd")
$filename = 'C:\Documents and Settings\User\Desktop\' + $enddate + '_VMReport.doc'
$filename

Error: This solution contains no resources scoped for a Web application and cannot be deployed to a particular Web application

copy from : this blog

Problem:

When I try to deploy my custom wsp solution to a specific web application, I got the error below:

This solution contains no resources scoped for a Web application and cannot be deployed to a particular Web application.

Analysis:

The error message itself explains why you cannot deploy the solution to a web application. However if you do not like to deploy the solution to all the web applications and only like to deploy your solution to a specific application , you need to change the solution settings Assembly Deployment Target from GlobalAssemblyCache to WebApplication.

From:

TO:

Solution:

After you change the Assembly Deployment Target and run the script again, you will have the solution deployed successfully.

How to check your server able to connect to SQL server or not ?

Just fast sharing on How to check your server able to connect to SQL server or not ?

Just create one txt file then rename it with Test_SQL.udl (any name with .udl extension ) then you will see the icon :


Double click on it then you will see the windows box at below : fill in the server name and select you window NT account , click on test connection then u will see the database on the drop down box

Host Named Site Collections with anonymous log in issues

Scenario :

We have a Claims Based Web Application which hosts various Host Named Site Collections.
One of these sites requires Anonymous Access. I have applied AA to the Web Application and activated it on the Site Collection in question. However, when users log in, when they navigate to another page they lose their login credentials (as if the cookies arent holding their data). Then they browse as Anonymous Users but suddenly they will appear logged in again?
Is this an error or a known issue? Can you use Anonymous Access on Claims Based Host Named Site Collections? Is their anything I can do to stop logged in users from suddenly being logged out and in?
Thanks >> copy from here

Root cause : suspected is because our HNSC web application root is not empty for port 80 , we have assign host header for it.

Workaround :  using power shell command to assign https to AAM then the https access will not facing this issues.
example of the power shell command
Set-SPSiteUrl (Get-SPSite 'http://portal.contoso.com') -Url 'https://portal.contoso.com' -Zone Internet

SharePoint 2013 RBS setup for second content database

Normally you can get the information from internet for first time RBS setup step , example : https://blogs.technet.microsoft.com/bogdang/2014/12/04/install-and-configure-rbs-with-sharepoint-2013-and-sql-server-2012/

just sharing some command for setup on the following content DB after first RBS content DB is enable ,

http://go.microsoft.com/fwlink/?LinkID=188395&clcid=0x409 >> download for RBS.msi x64 package  which is 5287KB file size is the correct version to support second content DB. if u downloaded the previous version then mind be not working.

SQL Query
==========
use wss_content
if not exists (select * from sys.symmetric_keys where name = N'##MS_DatabaseMasterKey##') create master key encryptionby password = N'Admin Key Password !2#4'

use wss_content
if not exists (select groupname from sysfilegroups where groupname=N'RBSFilestreamProvider')alter database wss_content add filegroup RBSFilestreamProvider

contains filestream

use wss_content
alter database wss_content add file (name = RBSFilestreamFile, filename = 'F:\Blobstore') to filegroup RBSFilestreamProvider


first content db enabel rbs , run at SQL server
===================================================

msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY DBNAME="WSS_Content_RBS" DBINSTANCE="DBPRD02" FILESTREAMFILEGROUP=RBSFilestreamProvider FILESTREAMSTORENAME=FilestreamProvider_1

first content db enabel rbs , run at application and web front end server
=====================================================
msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi DBNAME="WSS_Content" DBINSTANCE="DBInstance Name" ADDLOCAL="Client,Docs,Maintainer,ServerScript,FilestreamClient,FilestreamServer"


second content db :need to enabe RBS ( run at sql or applicatin server)
=================================================================
msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi REMOTEBLOBENABLE=1 FILESTREAMPROVIDERENABLE=1 FILESTREAMSTORENAME=FilestreamProvider_1 DBNAME="WSS_Content" ADDLOCAL="EnableRBS,FilestreamRunScript" DBINSTANCE="DBPRD02"


second content db : wfe install for rbs second content db
==========================================================
msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY DBNAME="WSS_Content" DBINSTANCE="DBPRD02" FILESTREAMFILEGROUP=RBSFilestreamProvider FILESTREAMSTORENAME=FilestreamProvider_1

powershell enable RBS
====================
$cdb = Get-SPContentDatabase WSS_Content
$rbss = $cdb.RemoteBlobStorageSettings
$rbss.Installed()
$rbss.Enable()
$rbss.SetActiveProviderName($rbss.GetProviderNames()[0])
$rbss

powershell migrate sql data to RBS
=================================
$cdb = Get-SPContentDatabase WSS_Content
$rbss = $cdb.RemoteBlobStorageSettings
$rbss.SetActiveProviderName($rbss.GetProviderNames()[0])
$rbss.Migrate()

Last Step :
========
Need to add application pool service account to the content DB for DB owner role.

*if not granted db role for application pool account, when you upload file will see error message . File name can't contain the following characters : &?<>#{}%~/\.


more information :
http://blogs.technet.com/b/pramodbalusu/archive/2011/07/09/rbs-and-sharepoint-2010.aspx

Why Normally HNSC is using the port 80 with blank host header setting for web application ?

Copy from : http://www.jeremytaylor.net/2013/03/04/host-named-site-collections-sharepoint-2013/

Note – HNSC can be created on a non default Web Application but you’ll need to add bindings in IIS for your HNSC as the Web Application isn’t listening to all port 80 requests and would need to know about the HNSC it hosts. 

Thats why a lot of blogs usually specify HNSCs on default web apps, because adding IIS bindings can get messy. 

=======================================================================

If you using the port 80 with empty host header then you are no need do manually do IIS binding for your site collection HNSC . else you need to do IIS binding for each site collection created by HNSC.

Why cannot user SharePoint Farm Account for application pool ?

As i understand if application pool account using the SharePoint Farm Account  is over privilege but today i just notice we can extract the service account password from the application pool .

with the following script then we can get the password then just understand using the SharePoint Farm Service Account for application is very risky for the security leaking.

 PowerShell

try{

Import-Module WebAdministration

Get-WebApplication

$webapps = Get-WebApplication

$list = @()

foreach ($webapp in get-childitem IIS:\AppPools\)

{

$name = "IIS:\AppPools\" + $webapp.name

$item = @{}

$item.WebAppName = $webapp.name

$item.Version = (Get-ItemProperty $name managedRuntimeVersion).Value

$item.State = (Get-WebAppPoolState -Name $webapp.name).Value

$item.UserIdentityType = $webapp.processModel.identityType

$item.Username = $webapp.processModel.userName

$item.Password = $webapp.processModel.password

$obj = New-Object PSObject -Property $item

$list += $obj

}

$list | Format-Table -a -Property "WebAppName", "Version", "State", "UserIdentityType", "Username", "Password"

}catch

{

$ExceptionMessage = "Error in Line: " + $_.Exception.Line + ". " + $_.Exception.GetType().FullName + ": " + $_.Exception.Message + " Stacktrace: " + $_.Exception.StackTrace

$ExceptionMessage

}

copy from this site

Query to check which backup task is running at back end

SELECT r.session_id,command,

            s.text,

            start_time,

            percent_complete,

            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '

                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '

                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,

            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '

                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '

                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,

            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

Kill the process

Kill

KILL 1866 WITH STATUSONLY >> check the status on this progress

Configure cache settings for a web application in SharePoint Server 2013

when need to setup blob cache  ?  one of the reasons is you want keep the file (document , video) at web front end , so no need very access need to extract from SQL server.

https://technet.microsoft.com/en-us/library/cc770229.aspx

To configure BLOB cache settings

1. Verify that you have the following administrative credentials: You must be a member of the Administrators group on the local computer to configure the BLOB cache settings.
2. Click Start, point to Administrative Tools, and then click Internet Information Services (IIS) Manager.
3. In Internet Information Services (IIS) Manager, in the Connections pane, click the plus sign (+) next to the server name that contains the web application, and then click the plus sign next to Sites to view the web application or applications that have been created.
4. Right-click the name of the web application for which you want to configure the disk-based cache, and then click Explore. Windows Explorer opens, with the directories for the selected web application listed.
5. Right-click web.config, and then click Open.
6. If the Windows dialog box appears, select Select a program from a list of installed programs, and then click OK.
7. In the Open With dialog box, click Notepad, and then click OK.
8. In the web.config Notepad file, find the following line: 
9. In this line, change the location attribute to specify a directory that has enough space to accommodate the cache size.

   Note:
   We strongly recommend that you specify a directory that is not on the same drive as where either the server operating system swap files or server log files are stored.

10. To add or remove file types from the list of file types to be cached, for the path attribute, modify the regular expression to include or remove the appropriate file extension. If you add file extensions, make sure to separate each file type with a pipe (|), as shown in this line of code.
11. To change the size of the cache, type a new number for maxSize. The size is expressed in gigabytes (GB), and 10 GB is the default.

    Important:
    It is recommended that you not set the cache size smaller than 10 GB. When you set the cache size, make sure to specify a number large enough to provide a buffer at least 20 percent bigger than the estimated size of the content that will be stored in the cache.

12. To enable the BLOB cache, change the enabled attribute, from "false" to "true".
13. Save the Notepad file, and then close it.

PowerShell script to reapply Quota Template to all Mysite

$SPWebApp = Get-SPWebApplication http://portal

foreach ($SPSite in $SPWebApp.Sites)
{
    if ($SPSite -ne $null)
    {
        Set-SPSite -Identity $SPSite.url -QuotaTemplate ""
        $SPSite.Dispose()
    }
}