Use Powershell to create a csv file with output from SQL query

We needed to transfer some data from Microsoft Dynamics AX to a old system running on DB2, to keep the data updated. 

This script runs daily and checks for changes made yesterday and creates a .csv file with the result, which can be imported/converted to most system.  See comments for explanation: 

This script needs to be run on a server with SQL installed, as it uses the SQL server snapin.
 
set-executionpolicy unrestricted

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

#Your SQL query
$SQLquery =@"

SELECT CUSTACCOUNT, FIRSTNAME + ' ' + MIDDLENAME as FIRSTMIDDLE,LASTNAME,STREET,ZIPCODE,CITY,PHONE,CELLULARPHONE,EMAIL,BIRTHDATE,CPR FROM [Your server].[dbo].[table]
where DATAAREAID = 'ID' AND
convert(varchar(10), CREATEDDATETIME, 102) = dateadd(day,datediff(day,0,getdate()),-1)


"@
#Run the query and export the result
$result = invoke-sqlcmd -query $SQLquery -serverinstance Server -database database
$result |export-csv c:\file.csv -notypeinformation -Encoding UTF8 -Delimiter ";"

#Give the file 5 sec to be created
Start-Sleep -s 5

#Remove all " from file - PowerShell adds them when using export-csv
(Get-Content c:\file.csv) | % {$_ -replace '"', ""} | out-file -FilePath c:\file.csv -Force

#Give the file 2 sec to be created
Start-Sleep -s 2

#Load the file and remove the headers - first line in csv
$csv = Get-Content c:\file.csv
$csv = $csv[1..($csv.count - 1)]
$csv > c:\file.csv

Daily health report by mail - SharePoint 2010

Found a script that sends a email with the SharePoint Health report. Its useful when you want the information delivered to your inbox instead of going to each SharePoint environment and checking the health report there. Put the script below, into a scheduled task and run it daily:

if ($PSVersionTable) {$Host.Runspace.ThreadOptions = 'ReuseThread'}

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

# get the health reports list

$ReportsList = [Microsoft.SharePoint.Administration.Health.SPHealthReportsList]::Local

$FormUrl = '{0}{1}?id=' -f $ReportsList.ParentWeb.Url, $ReportsList.Forms.List.DefaultDisplayFormUrl

$body = $ReportsList.Items | Where-Object {$_['Severity'] -ne '4 - Success'} | ForEach-Object {

    New-Object PSObject -Property @{

        Url = "$($_['Title'])"

        Severity = $_['Severity']

        Category = $_['Category']

        Explanation = $_['Explanation']

        Modified = $_['Modified']

        FailingServers = $_['Failing Servers']

        FailingServices = $_['Failing Services']

        Remedy = $_['Remedy']

    }

} | ConvertTo-Html | Out-String

# creating clickable HTML links

$body = $body -replace '<','<' -replace '>','>' -replace '"','"'

$params = @{

   To = 'you@domain.com','manager@domain.com'

   From = 'SPHealth@domain.com'

   Subject = 'Daily Health Analyzer report'

   SmtpServer = 'smtp1'

   Body = $body

   BodyAsHtml = $true

}

Send-MailMessage @params

Source: Shay Levy

Collection of SharePoint webparts - Sites and places

 ‎http://www.dundas.com - Primarily chart and KPI webparts  

http://www.amrein.com/sharepoint - A collection of various useful webpart, Image Rotator etc..

http://www.boostsolutions.com - Administration, BI, Collaboration, Content Management and Security

http://www.bamboosolutions.com - Many useful webparts and solutions available to 2007, 2010 and 2013

http://www.virtosoftware.com - Administration, BI, Collaboration, Content Management, Workflow, Bulk Operations and Project Management

http://www.pentalogic.net - A small collection of webparts you might find useful. Team Time - if you looking for a Punch Card/TimeSheets solution

http://www.kwizcom.com  - Many useful webparts and solutions available to 2007, 2010 and 2013

http://www.lightningtools.com

http://www.layer2.de

http://www.codeplex.com - Opensouce projects/webparts, with very different functionality, workflow add-ons, powershell scipts ect...

http://www.harepoint.com 

SharePoint, Show 2 or more webparts side by side

It can be very useful to be able to place 2 webparts side by side in SharePoint, especially if you don't have the option to add a custom page template or don't know how to. Also in SharePoint online, you possibilities can be limited, and this might be a good solution. Just add a table to you page and place your webparts inside the table columns:

Taken from the source link below:

How do we do it?

• enter in Edit Mode;
• choose a location for the table;
• insert a table with n columns and n rows (depends on the number of web parts);

If you already have some web parts in the page just drag & drop them in the table.
If not, click in a column and then insert the desired web part.
  

Source: Alexandru Dionisie

Count all documents in SharePoint 2010 with powershell

Runs trough sharepoint and counts all items in "Documents" library.

cls

$total = 0
$totalembers = 0;

foreach($site in Get-SPSite -Limit all)
{
    foreach($web in $site.AllWebs)
    {
        $totalembers = $totalembers + 1
       
        $list = $web.Lists["Documents"];
       
        $listCount = $list.ItemCount
        #$listCount = $list.Item.Count #more accurate but slow
       
        $total = $total + $list.ItemCount
        echo $web
        echo $total
    }
}

$logMsg = "Total Members " + $totalembers + "; Total Documents " + $total + ";"
write $logMsg

Upload file to multiple document libraries in SharePoint 2010 with PowerShell

We needed to publish a file to all document libraries, with the same name, in a site collection. To achieve that, the following powershell script was made, by bits and pieces from different blogs. The script runs trough the site collection and uploads the file to the "Documents" folder.

clear
$DocLibName = "Documents"
$FilePath = "d:\file.pdf"


################## NUMBER OF MEMBERSITES COUNT ##################
$webapplication = Get-SPWebApplication "Web"
$icount = 0

foreach($sitecount in $webapplication.Sites){
       $icount++
}
$icount = ($icount-81)
echo $icount
################## NUMBER OF MEMBERSITES COUNT END ###############

################## Upload to ALL MEMBERSITES ##################

$i = 0
$numberarray = 0..$icount

echo ($nowStart = Get-Date)

    foreach($i in $numberarray) {
#We have different site collections, so we need to run trough them all
    $siteurl = "URL/"+$i+"/"
    $site = Get-SPSite($siteurl)

            foreach($web in $site.AllWebs) {
            #Get the Web & Lists to upload the file
            $List = $Web.GetFolder($DocLibName)
            
            #Get the Files collection
            $Files = $List.Files
            
            #Get File Name from Path
            $FileName = $FilePath.Substring($FilePath.LastIndexOf("\")+1)
            
            #Get the File
            $File= Get-ChildItem $FilePath
            
            #Add File to the collection
            $Files.Add($DocLibName +"/" + $FileName,$File.OpenRead(),$false)
            echo "Uploader til "$web.Url
            #Dispose the objects
            $web.Dispose()
            }
    $i++
    }
echo "Start:"$nowStart
echo "Slut:"($nowEnd = Get-Date)
################## Upload to MEMBERSITES END #################
  
It should also work with multiple files in the upload folder.

Update listview on multiple list in SharePoint 2010

Our member system is a SharePoint 2010, where each member has its own site. Each site has a document library to handle the member documents. The default view needed some extra columns and information. Here is the powershell script to create a new view and make it default:

Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
$webapplication = Get-SPWebApplication "Web"
foreach($site in $webapplication.Sites){
    foreach($web in $site.AllWebs){
     
$listCounter = $web.Lists.Count
for($i=0;$i -le $listCounter;$i++) {

    $list = $web.Lists[$i]
    if($list.BaseType -eq "DocumentLibrary") {
    $newList = $web.Lists.item($list.ID);


  $viewfields = New-Object System.Collections.Specialized.StringCollection
  $viewfields.Add("Type")
  $viewfields.Add("Navn")
  $viewfields.Add("Emne")
  $viewfields.Add("Medlemsid")
  $viewfields.Add("Oprettet")
  $viewfields.Add("Sagsid")
  

  [void]$newList.Views.Add("Filer", $viewfields, "", 100, $true, $true)
  $newList.Update();

  $view=$newList.Views["Filer"]
  $view.DefaultView = $true
  $view.Update()
}
}

$web.Dispose();
}
}
$site.Dispose();