API tools faq
paste
Advertisement
tankcr

Untitled

tankcr
Apr 30th, 2014
393
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PowerShell 9.21 KB | None | 0 0
raw download clone embed print report
  1. $SPOCred = (GCI "C:\Projects\SPOCreds.PS1").fullname
  2. invoke-expression -Command $SPOCred -ErrorAction SilentlyContinue
  3. $Pat = "(.*)(CRM.*_?)(_.*)"
  4. $GetSites = (Get-SPOSite -Detailed -ErrorAction SilentlyContinue -limit all)
  5. $Sites = $GetSites|sort -Descending -Property {$_.URL -replace $Pat,'$2'},{try{@{e={[INT]($_.StorageUsageCurrent)/[INT]($_.StorageQuota)}}} catch{0}}
  6. $Number = $Sites.Count
  7. $File = "C:\Temp\Usage.XML"
  8. $File2 = "C:\Temp\SiteCollectionUsage.XLSX"
  9. $NetLoc = "\\srvlabspweb02\c$\temp\"
  10. (
  11.  '<?xml version="1.0"?>
  12. <?mso-application progid="Excel.Sheet"?>
  13. <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  14. xmlns:o="urn:schemas-microsoft-com:office:office"
  15. xmlns:x="urn:schemas-microsoft-com:office:excel"
  16. xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
  17. xmlns:html="http://www.w3.org/TR/REC-html40"
  18. xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml"
  19. xmlns:udc="http://schemas.microsoft.com/data/udc"
  20. xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  21. xmlns:udcxf="http://schemas.microsoft.com/data/udc/xmlfile">
  22. <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  23.  <Author>Kristopher Roy</Author>
  24.  <LastAuthor>'+$env:USERNAME+'</LastAuthor>
  25.  <Created>'+(get-date)+'</Created>
  26.   <Version>15.00</Version>
  27. </DocumentProperties>
  28. <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  29.  <AllowPNG/>
  30. </OfficeDocumentSettings>
  31. <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  32.  <WindowHeight>12435</WindowHeight>
  33.  <WindowWidth>25500</WindowWidth>
  34.  <WindowTopX>0</WindowTopX>
  35.  <WindowTopY>0</WindowTopY>
  36.  <ProtectStructure>False</ProtectStructure>
  37.  <ProtectWindows>False</ProtectWindows>
  38. </ExcelWorkbook>
  39. <Styles>
  40.  <Style ss:ID="Default" ss:Name="Normal">
  41.   <Alignment ss:Vertical="Bottom"/>
  42.   <Borders/>
  43.   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
  44.   <Interior/>
  45.   <NumberFormat/>
  46.   <Protection/>
  47.  </Style>
  48.  <Style ss:ID="s27" ss:Name="Bad">
  49.   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#800000"/>
  50.   <Interior ss:Color="#FFC7CE" ss:Pattern="Solid"/>
  51.  </Style>
  52.  <Style ss:ID="s26" ss:Name="Good">
  53.   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#008000"/>
  54.   <Interior ss:Color="#C6EFCE" ss:Pattern="Solid"/>
  55.  </Style>
  56.  <Style ss:ID="s62" ss:Name="Hyperlink">
  57.   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#0066CC"
  58.    ss:Underline="Single"/>
  59.  </Style>
  60.  <Style ss:ID="s28" ss:Name="Neutral">
  61.   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#993300"/>
  62.   <Interior ss:Color="#FFEB9C" ss:Pattern="Solid"/>
  63.  </Style>
  64.  <Style ss:ID="s63">
  65.   <Borders>
  66.    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"/>
  67.    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2"/>
  68.    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2"/>
  69.    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2"/>
  70.   </Borders>
  71.   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
  72.    ss:Bold="1"/>
  73.   <Interior ss:Color="#D0CECE" ss:Pattern="Solid"/>
  74.  </Style>
  75.  <Style ss:ID="s64">
  76.   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
  77.  </Style>
  78.  <Style ss:ID="s66" ss:Parent="s62">
  79.   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
  80.  </Style>
  81.  <Style ss:ID="s68" ss:Parent="s26">
  82.   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
  83.   <NumberFormat ss:Format="0%"/>
  84.  </Style>
  85.  <Style ss:ID="s70" ss:Parent="s28">
  86.   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
  87.   <NumberFormat ss:Format="0%"/>
  88.  </Style>
  89.  <Style ss:ID="s72" ss:Parent="s27">
  90.   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
  91.   <NumberFormat ss:Format="0%"/>
  92.  </Style>
  93.    <Style ss:ID="s90" ss:Parent="s62">
  94.   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
  95.   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#0066CC"
  96.    ss:Bold="1" ss:Underline="Single"/>
  97.   <Interior ss:Color="#FFE699" ss:Pattern="Solid"/>
  98.  </Style>
  99. </Styles>
  100. <Worksheet ss:Name="Site Quotas">
  101.  <Table ss:ExpandedColumnCount="9" ss:ExpandedRowCount="'+($Number+1)+'" x:FullColumns="1"
  102.   x:FullRows="1" ss:DefaultRowHeight="15">
  103.   <Column ss:AutoFitWidth="0" ss:Width="100.5"/>
  104.   <Column ss:Width="441.75"/>
  105.   <Column ss:Width="247.5"/>
  106.   <Column ss:Width="33.75"/>
  107.   <Column ss:Width="41.25"/>
  108.   <Column ss:Width="73.5"/>
  109.   <Column ss:Width="77.25"/>
  110.   <Column ss:Width="117.75"/>
  111.   <Column ss:Width="105"/>
  112.   <Row ss:AutoFitHeight="0">
  113.    <Cell ss:StyleID="s63"><Data ss:Type="String">Title</Data></Cell>
  114.    <Cell ss:StyleID="s63"><Data ss:Type="String">URL</Data></Cell>
  115.    <Cell ss:StyleID="s63"><Data ss:Type="String">Owner</Data></Cell>
  116.    <Cell ss:StyleID="s63"><Data ss:Type="String">Quota</Data></Cell>
  117.    <Cell ss:StyleID="s63"><Data ss:Type="String">Percent</Data></Cell>
  118.    <Cell ss:StyleID="s63"><Data ss:Type="String">QuotaWarning</Data></Cell>
  119.    <Cell ss:StyleID="s63"><Data ss:Type="String">ResourceQuota</Data></Cell>
  120.    <Cell ss:StyleID="s63"><Data ss:Type="String">ResourceQuotaWarning</Data></Cell>
  121.    <Cell ss:StyleID="s63"><Data ss:Type="String">StorageUsageCurrent</Data></Cell>
  122.   </Row>')> $file
  123.  
  124.  
  125. FOREACH ($Site in $Sites){
  126. $Owner = if(($Site.Owner -eq $NULL) -or ($site.Owner -eq "")){$Owner = "Farm"}ELSE{$Site.Owner}
  127. $Pat = "(https?.\/\/)(\w*-?\w*)(.*)?"
  128. if($Site.Title -eq $NULL -or $Site.Title -eq ""){$Title = ($Site.url -replace $pat,'$2')}ELSE{$Title = $Site.Title}
  129. $BasePercent = try {$Site.StorageUsageCurrent / $Site.StorageQuota} catch {0}
  130. If ($BasePercent -ge ".75"){$Percent = '<Cell ss:StyleID="s72"><Data ss:Type="Number">'+$BasePercent+'</Data></Cell>'}
  131. If (($BasePercent -lt ".75") -and ($BasePercent -ge ".60")){$Percent = '<Cell ss:StyleID="s70"><Data ss:Type="Number">'+$BasePercent+'</Data></Cell>'}
  132. If ($BasePercent -lt ".60"){$Percent = '<Cell ss:StyleID="s68"><Data ss:Type="Number">'+$BasePercent+'</Data></Cell>'}
  133.  
  134. $URLCODE = IF($Site.URL -ilike "*Prod*"){"s90"}ELSE{"s66"}
  135. add-content $File ('<Row ss:AutoFitHeight="0">')
  136. add-content $File ('<Cell ss:StyleID="s64"><Data ss:Type="String">'+($Title)+'</Data></Cell>')
  137. add-content $File ('<Cell ss:StyleID="'+($URLCODE)+'" ss:HRef="'+($Site.url)+'">'+'<Data ss:Type="String">'+($Site.url)+'</Data></Cell>')
  138. add-content $File ('<Cell ss:StyleID="s64"><Data ss:Type="String">'+($Owner)+'</Data></Cell>')
  139. add-content $File ('<Cell ss:StyleID="s64"><Data ss:Type="Number">'+($Site.StorageQuota)+'</Data></Cell>')
  140. add-content $file $Percent
  141. add-content $File ('<Cell ss:StyleID="s64"><Data ss:Type="Number">'+($Site.StorageQuotaWarningLevel)+'</Data></Cell>')
  142. add-content $File ('<Cell ss:StyleID="s64"><Data ss:Type="Number">'+($Site.ResourceQuota)+'</Data></Cell>')
  143. add-content $File ('<Cell ss:StyleID="s64"><Data ss:Type="Number">'+($Site.ResourceQuotaWarningLevel)+'</Data></Cell>')
  144. add-content $File ('<Cell ss:StyleID="s64"><Data ss:Type="Number">'+($Site.StorageUsageCurrent)+'</Data></Cell>')
  145. add-content $File '</Row>'
  146. }
  147. add-content $file (
  148. '    </Table>
  149.  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
  150.   <Unsynced/>
  151.   <Print>
  152.    <ValidPrinterInfo/>
  153.    <HorizontalResolution>600</HorizontalResolution>
  154.    <VerticalResolution>600</VerticalResolution>
  155.   </Print>
  156.   <Selected/>
  157.   <LeftColumnVisible>1</LeftColumnVisible>
  158.   <FreezePanes/>
  159.   <FrozenNoSplit/>
  160.   <SplitHorizontal>1</SplitHorizontal>
  161.   <TopRowBottomPane>1</TopRowBottomPane>
  162.   <ActivePane>2</ActivePane>
  163.   <Panes>
  164.    <Pane>
  165.     <Number>3</Number>
  166.     <ActiveCol>1</ActiveCol>
  167.    </Pane>
  168.    <Pane>
  169.     <Number>2</Number>
  170.     <ActiveRow>10</ActiveRow>
  171.     <ActiveCol>2</ActiveCol>
  172.    </Pane>
  173.   </Panes>
  174.   <ProtectObjects>False</ProtectObjects>
  175.   <ProtectScenarios>False</ProtectScenarios>
  176.  </WorksheetOptions>
  177. </Worksheet>
  178. </Workbook>')
  179.  
  180. $objExcel = new-object -comobject excel.application
  181. $UserWorkBook = $objExcel.Workbooks.Open($file)
  182. $UserWorkBook.SaveAs($file2,51)
  183. $UserWorkBook.Close()
  184.  
  185. copy $file2 $NetLoc
  186. remove-item $file
  187. remove-item $file2
  188.  
  189. $web = $Null
  190. $site = $Null
  191.  
  192. Invoke-Command -ComputerName "SRVLABSPWEB02" -Authentication Credssp -Credential $onpremcred{
  193.     Add-PSSnapin microsoft.sharepoint.powershell -ErrorAction SilentlyContinue
  194.     $File3 = GCI ("\\srvlabspweb02\c$\temp\SiteCollectionUsage.XLSX")
  195.    
  196.     $site = New-Object Microsoft.SharePoint.SPSite("http://pyroweb-qa.pyrotek-lab.local/departments/sales/CRM/working_proj")
  197.     $web = $site.OpenWeb()
  198.     $list=$web.Lists["Documents"]
  199.     $listItems = $list.Items
  200.     $listItemsTotal = $listItems.Count
  201.     $Web.AllowUnsafeUpdates = $true;
  202.     for ($x=$listItemsTotal-1;$x -ge 0; $x--)
  203.     {
  204.         if($listItems[$x].name.Contains("SiteCollectionUsage.XLSX"))
  205.         {
  206.         Try {$listItems[$x].File.CheckIn("Automatic CheckIn. (SysAdmin)")}Catch{}
  207.         $listItems[$x].Delete()
  208.         }
  209.     }
  210.     $fileStream = ([System.IO.FileInfo] (GI $File3.FullName)).OpenRead()
  211.     $folder = $List.RootFolder
  212.     [Microsoft.SharePoint.SPFile]$spFile = $folder.Files.Add($folder.Url + "/" + $File3.Name, [System.IO.Stream]$fileStream, $true)
  213.     $fileStream.Close()
  214. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!