Get SharePoint Publishing Pages with PowerShell

Posted on Updated on

This code is to get all publishing pages that is created in the last 30 days.
It is compatible will all On-Prem SharePoint 2007, 2010, 2013 and 2016
Please open PowerShell-ISE and put the code in it. then click Run


$ErrorActionPreference = "SilentlyContinue"
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Publishing")
#Start-SPAssignment -Global
$newline = [Environment]::NewLine
$webAppURL="https://yoursharepointwebapplication.com"
$currentWebApplication = [Microsoft.SharePoint.Administration.SPWebApplication]::Lookup($webAppURL)
$sites = $currentWebApplication.Sites
foreach($Site in $sites)
{
if ($Site.ServerRelativeUrl.Contains("market")){
#$Site = New-Object Microsoft.SharePoint.SPSite($SiteUrl)
$PubSite = New-Object Microsoft.SharePoint.Publishing.PublishingSite($Site)
$PubPageCount = 0
$newPubPageCount = 0
$ImageCount = 0
$DocCount = 0
foreach ($Web in $Site.AllWebs) {
$PubWeb = [Microsoft.SharePoint.Publishing.PublishingWeb]::GetPublishingWeb($Web)
$Pages = $PubWeb.GetPublishingPages($PubWeb)
foreach ($Page in $Pages)
{
if($Page.CreatedDate -GT (Get-Date).AddDays(-30))
{
$newPubPageCount += 1
}
}
$PageCount = $Pages.Count
$PubPageCount = $PubPageCount + $PageCount
#$Images = $PubWeb.ImagesLibrary.ItemCount
#$ImageCount = $ImageCount + $Images
#$Documents = $PubWeb.DocumentsLibrary.ItemCount
#$DocCount = $DocCount + $Documents
$Web.Dispose()
}
$OutputList += $Site.Url + "," + $Site.Allwebs.Count + "," + $PubPageCount + "," + $newPubPageCount +";"
$Site.Dispose()
#Stop-SPAssignment -Global
}
}
$OutputList

Advertisements

Convert Text to Excel using PowerShell

Posted on Updated on

In this post, we will convert text file to Excel using PowerShell.

Pre-requisite : you should have Microsoft Office Excel installed on your machine

1. This is a sample of the data to be converted, If you want to try it your self please Copy paste the below box to a file named sourceFile.txt

2. Now, open the PowerShell_ISE and paste the below script in it

#This script is to convert text files to formalized excel sheet.
#Please make sure txt file is applied in this format
#Header1, Header2, Header3, Header4
#X1, X2, X3, X4
#Y1, Y2, Y3, Y4
#Z1, Z2, Z3, Z4
#Constants
$SourcePath = "C:\temp\sourceFile.txt"
$DestinationPath = "C:\temp\destinationPath"
$deleimter= ","; #You can replace it by any other delimeter ';' or '/'#You can leave this part as it is
$SourceTxt = Get-Content $SourcePath
$xlsxFile = $DestinationPath + ".xlsx"if (Test-Path ($xlsxFile))
{
Remove-Item $xlsxFile
}
#$SourceTxt >> $txtFile
[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$xl=New-Object -ComObject "Excel.Application"
$wb=$xl.Workbooks.Add()
$ws=$wb.ActiveSheet
$xl.Visible=$True
$cells=$ws.Cells
$Content = Get-Content $SourcePath
$numOfRows = $Content.Length
$numOfColumns = $Content[0].split($deleimter).Count
for ($i=0; $i -lt $numOfRows ;$i++)
{
$rowData = $Content[$i].split($deleimter)
for ($j=0; $j -lt $numOfColumns; $j++)
{
$cellData = $rowData[$j]
$cells.item($i+1,$j+1) = $cellData
}
}
#Apply some Format for Excel header
$xl.Cells.EntireColumn.AutoFit()
$xl.Cells.EntireColumn.AutoFilter()
$xl.Rows.Item(1).Font.Bold = $True
$xl.Rows.Item(1).Interior.ColorIndex = 44
#Save Sheet
$wb.SaveAs($xlsxFile)
$wb.Close()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)
$xl.Quit()
#Clean up
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
#Remove-Variable -Name xl
#[gc]::Collect()
#[gc]::WaitForPendingFinalizers()

3. Output should be something like this:

Get All Datbases in your SQL Server

Posted on Updated on

This SQL script is used to get all databases in your SQL Server with details as follow (Thanks to Tibor Karaszi):

  1. Database name
  2. Data allocated
  3. Dat used
  4. Log allocated
  5. Log used
  6. recovery model
  7. Instance name

To get please do the following:

1. Run the below in a new SQL query

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_dbinfo] Script Date: 07/28/2017 12:24:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROC [dbo].[sp_dbinfo]
@sort char(1) = 'n'
,@include_instance_name char(1) = 'n'
AS
/***************************************************************************
@sort accept 4 values: 'n' (default), 'd', 'l' and 'r'.
@include_server_name accept 2 values, 'y' and 'n'.
It specifies the sort order (name, data allocated, log allocated, rollup only).Written by Tibor Karaszi 2009-12-29
Modified 2010-01-19, fixed data type for db name. Thanks csm!
Modified 2010-05-24, added support for offline databases. Thanks Per-Ivan N?und.
Modified 2011-07-21, SQL Server 11, use sysperfinfo instead of DBCC SQLPERF.
Modified 2011-09-23, master instead of MASTER, also qualified sysperfinfo.
Modified 2011-12-28, renamed to sp_dbinfo, added rollup option.
Modified 2013-02-19, added recovery model and option for instance name.
***************************************************************************/
SET NOCOUNT ON
DECLARE
@sql nvarchar(2000)
,@db_name sysname
,@recovery_model varchar(12)
,@crlf char(2)SET @crlf = CHAR(13) + CHAR(10)--Create tables to hold space usage stats from commands
CREATE TABLE #logspace
(
database_name sysname NOT NULL
,log_size real NOT NULL
,log_percentage_used real NOT NULL
)
CREATE TABLE #dbcc_showfilestats
(
database_name sysname NULL
,file_id_ int NOT NULL
,file_group int NOT NULL
,total_extents bigint NOT NULL
,used_extents bigint NOT NULL
,name_ sysname NOT NULL
,file_name_ nvarchar(3000) NOT NULL
)
--Create table to hold final output
CREATE TABLE #final_output
(
database_name sysname
,data_allocated int
,data_used int
,log_allocated int
,log_used int
,is_sum bit
)
--Populate log space usage
INSERT INTO #logspace(database_name, log_size, log_percentage_used)
SELECT
instance_name AS 'Database Name'
,MAX(CASE
WHEN counter_name = 'Log File(s) Size (KB)' THEN cntr_value / 1024.
ELSE 0
END) AS 'Log Size (MB)'
,MAX(CASE
WHEN counter_name = 'Percent Log Used' THEN cntr_value
ELSE 0
END) AS 'Log Space Used (%)'
FROM master..sysperfinfo
WHERE counter_name IN('Log File(s) Size (KB)', 'Percent Log Used')
AND instance_name != '_total'
GROUP BY instance_name
----Populate data space usage
DECLARE db CURSOR FOR SELECT name FROM sys.databases WHERE state_desc = 'ONLINE'
OPEN db
WHILE 1 = 1
BEGIN
FETCH NEXT FROM db INTO @db_name
IF @@FETCH_STATUS 0
BREAK
SET @sql = 'USE ' + QUOTENAME(@db_name) + ' DBCC SHOWFILESTATS WITH NO_INFOMSGS'
INSERT INTO #dbcc_showfilestats(file_id_, file_group, total_extents, used_extents, name_, file_name_)
EXEC (@sql)
UPDATE #dbcc_showfilestats SET database_name = @db_name WHERE database_name IS NULL
END
CLOSE db
DEALLOCATE db
--Result into final table
INSERT INTO #final_output(database_name, data_allocated, data_used, log_allocated, log_used, is_sum)
SELECT
CASE WHEN d.database_name IS NOT NULL THEN d.database_name ELSE '[ALL]' END AS database_name
,ROUND(SUM(CAST((d.data_alloc * 64.00) / 1024 AS DECIMAL(18,2))), 0) AS data_allocated
,ROUND(SUM(CAST((d.data_used * 64.00) / 1024 AS DECIMAL(18,2))), 0) AS data_used
,ROUND(SUM(CAST(log_size AS numeric(18,2))), 0) AS log_allocated
,ROUND(SUM(CAST(log_percentage_used * 0.01 * log_size AS numeric(18,2))), 0) AS log_used
,GROUPING(d.database_name) AS is_sum
FROM
(
SELECT database_name, SUM(total_extents) AS data_alloc, SUM(used_extents) AS data_used
FROM #dbcc_showfilestats
GROUP BY database_name
) AS d
INNER JOIN #logspace AS l ON d.database_name = l.database_name
INNER JOIN sys.databases AS sd ON d.database_name = sd.name
GROUP BY d.database_name WITH ROLLUP
--Output result
SET @sql = '
SELECT f.database_name, f.data_allocated, f.data_used, f.log_allocated, f.log_used, d.recovery_model_desc' +
CASE @include_instance_name WHEN 'y' THEN ', @@SERVERNAME AS instance_name' ELSE '' END + @crlf +
'FROM #final_output AS f LEFT OUTER JOIN sys.databases AS d ON f.database_name = d.name' + @crlf +
CASE WHEN @sort = 'r' THEN 'WHERE f.database_name = ''[ALL]''' ELSE '' END + @crlf +
'ORDER BY is_sum' + @crlf +
CASE
WHEN @sort = 'n' THEN ', database_name'
WHEN @sort = 'd' THEN ', data_allocated DESC'
WHEN @sort = 'l' THEN ', log_allocated DESC'
ELSE ''
END
--PRINT @sql
EXEC(@sql)

2. This will create a Procedure with the name dbo.sp_dbinfo.
To confirmed please Check Databases > System Datbases > master > Programmabiltiy > Stored Procedures > dbo.sp_dbinfo

3. Now run this command

USE [master]
GO
EXEC sp_dbinfo 'd', 'y'

you should see result like the below

you can also pick one of those commands for different output

--Test execution
/*
EXEC sp_dbinfo
EXEC sp_dbinfo 'n'
EXEC sp_dbinfo 'd'
EXEC sp_dbinfo 'l'
EXEC sp_dbinfo 'r'
EXEC sp_dbinfo 'n', 'y'
EXEC sp_dbinfo 'd', 'y'
*/

Good Luck !

PowerShell for Show documents in Delve and in the Discover view in OneDrive for Business

Posted on Updated on

Delve is relatively still young baby for Microsoft.

Recently we have a requirement to enable the option of Show documents in Delve and in the Discover view in OneDrive for Business.

This can be done easily via a GUI like below

Go to App lancher and pick Delve –> Select Feature settings –> then enable Show documents in Delve and in the Discover view in OneDrive for Business

 

to do this via script, please copy and paste this in your PowerShell ISE and run it.

Pre-requistes

  1. Windows Internet Explorer
  2. You have to login at least 1 time through portal.office.com because the script will use your existing credentials


#Deactivate Delve Simulator
$ie = New-Object -COMObject InternetExplorer.Application
$jsonlink = "https://eur.delve.office.com/"
try{
#You can replace your the variable below with a static name, but her it logs with your current windows credentials
$searcher = [adsisearcher]"(samaccountname=$env:USERNAME)"
$mail = $searcher.FindOne().Properties.mail
$userName =$mail.replace("@","_").replace(".","_")
#Navigate to your download file/location
Write-host "Processing Request for: " $user.UPN
$ie.visible = $true
$ie.Navigate($jsonlink)
while($ie.Busy){Sleep 4}
while($ie.Document.location.href -like '*login.microsoftonline.com/*'){
(
$ie.document.IHTMLDocument3_getElementsByTagName("table") | Where-Object{$_.Id -eq $userName} | select -First 1).click()
Sleep 4
}
Write-Host "Ensure you are in the Delve Screem ..." -ForegroundColor Yellow
#[void](Read-Host 'Press Enter to continue after login ' -ErrorAction SilentlyContinue)
#Navigate to Properties
$ie.document.IHTMLDocument3_getElementById("O365_MainLink_Settings").click()
Sleep 4
#Open Settings
($ie.document.IHTMLDocument3_getElementsByTagName("a") | Where-Object{$_.className -eq "o365cs-settings-deeplink wf-size-x12 ms-fcl-ns o365button"} | select -First 1).click()
Sleep 4
##Switch Value
$checkBox =$ie.document.IHTMLDocument3_getElementById("enableDocumentsCheckbox")
$oldValue = $checkBox.value
if($oldValue -eq "on" -or $oldValue -eq "off")
{
$checked = $checkBox.click()
}
Sleep 2
#$newValue = $ie.document.IHTMLDocument3_getElementById("enableDocumentsCheckbox").value
##Submit
Sleep 1
($ie.document.IHTMLDocument3_getElementsByTagName("button") | Where-Object{$_.className -eq "SharingActivityPanel-module_button_delve"} | select -First 1).click()
##Refresh
Sleep 4
$ie.document.location = $ie.document.location
write-host "Done Successfully Delve Flag Switched"
}
catch [System.Exception]{
write-host "Failed, Try Again" $_.Exception
}
finally {
while($ie.Busy){Sleep 1}
$ie.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($ie)
}

How to Invoke Web Services from Microsoft System Center Orchestrator 2012?

Posted on

In this blog we are going to describe How to Call Web Service from System Center Orchestartor 2012, using the Invoke Webservices Activity

image

  1. We will elect a certain Webservice for test: http://www.ezzylearning.com/services/CountryInformationService.asmx
  2. Navigate to the URL to be sure it is working fine.
    Note : If you find this WebServices retired; please add your own or add a new one
    Example : http://www.webservicex.net/CurrencyConvertor.asmx?WSDL
    All next steps should be the same
  3. Now we need to get the WSDL file from this webservice, so, either to click on the Service Description link or Navigate directly to
    http://www.ezzylearning.com/services/CountryInformationService.asmx?WSDL
    image
  4. Save this page to your hard drive
    image
  5. It is time now to call it from Orchestrator, In your runbook, Drag-Drop the Invoke Web Service Activity from the Utilities.
    image
  6. Set the properties of the Activity as follow:
    WSDL: Write the Path of the wsdl file that you have just save.
    Method: Select the required Web method, in this example we will use GetContients.
    Format Hint: it is Optional, sometimes it gives a result when you click it, depending on the web method it self, in our case, no Fomrat Hint is avaliable.image
  7. On the Advanced Tab, Check on the Address URL, and write the webservice full URL, http://www.ezzylearning.com/services/CountryInformationService.asmximage
  8. In the Security Tab, if the webservice have a HTTP Authentication, write it, other wise, leave it as it is, in our case, we will leave it as it is.
    image
  9. Add the activity of Append Line as shown, as we are going to display some output there
    image
  10. configure the Append line as shown.
    image
  11. Run your Runbook

You can parse the XML Response by Powershell script, or any other language to get your needed data.

Regards,

Cancel Nintex workflow stuck Instance using PowerShell

Posted on Updated on

In this post we are going to apply a PowerShell script to cancel all stucked workflow in Nintex

This script is used to run for SharePoint 2010, SQL Server 2008 R2, but it still can be used for other versions after running it on Stage environment

Take a copy from the below script and run it on the SharePoint server that contains Nintex workflows

if( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
{
Add-PsSnapin Microsoft.SharePoint.PowerShell
}
$web = Get-SPWeb "https://Microsoft.com/Sites/YourSite";
$web.AllowUnsafeUpdates = $true;
$list = $web.Lists["Your WorkFlow Configuration list"];
foreach ($item in $list.Items)
{
foreach ($wf in $item.Workflows)
{
if ($wf.InternalState -eq "Completed")
{
# Nothing To Do
}
else
{
# Writes Workflow Status
Write-Host $wf.InternalState
#Cancel Workflow
[Microsoft.SharePoint.Workflow.SPWorkflowManager]::CancelWorkflow($wf);
}
}
}
$web.Dispose();