Welcome‎ > ‎

Scripting

Retrieve information of network share and put results in Excel

posted Oct 16, 2013, 12:10 AM by Jurien Bosman

This script looks at a networkshare, retrieves directorynames and show which Groups/Users have access to that directory.


cls
# Ask for credentials
$cred =  Get-Credential
# Connect PS drive
New-PSDrive  -Credential $cred  -PSProvider Filesystem -Root \\netwerk-share\Data -Name "Netwerkdrive"
#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True

$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
$intcol = 4
$intRow = 1
$path = "\\netwerk-share\data"
$maindirectories = gci $path | ?{ $_.PSIsContainer }| select name

$Sheet.Cells.Item($intRow,1) = "Directorystructure FileShare"
$Sheet.Cells.Item($intRow,2) = "\\netwerk-share\Data"
$intRow ++
foreach ($maindirectory in $maindirectories){
$Sheet.Cells.Item($intRow,1) = $maindirectory.name
$Sheet.Cells.Item($intRow,1).Borders.Linestyle = 1
$Sheet.Cells.Item($intRow,1).Font.Bold=$True
$subpath1 = $path + "\" + $maindirectory.Name
$access1 = Get-Acl $subpath1
$rights = $access1.Access
$intcol = 4
foreach ($item in $rights){
[string]$veld = $item.IdentityReference
$veld = $veld.Replace("AD\","")
$Sheet.Cells.Item($intRow,$intcol) = $veld
$Sheet.Cells.Item($intRow,$intcol).Borders.Linestyle = 1
$intcol++
}
$intRow++
$subdirectories1 = gci $subpath1 | ?{ $_.PSIsContainer }| select name
foreach ($subdirectory1 in $subdirectories1){
$Sheet.Cells.Item($intRow,2) = $subdirectory1.name
$Sheet.Cells.Item($intRow,2).Borders.Linestyle = 1
$subpath2 = $subpath1 + "\" + $subdirectory1.Name
$access1 = Get-Acl $subpath2
$rights = $access1.Access
$intcol = 4
foreach ($item in $rights){
[string]$veld = $item.IdentityReference
$veld = $veld.Replace("AD\","")
$Sheet.Cells.Item($intRow,$intcol) = $veld
$Sheet.Cells.Item($intRow,$intcol).Borders.Linestyle = 1
$intcol++
}
$subdirectories2 = gci $subpath2 | ?{ $_.PSIsContainer }| select name
$intRow++
foreach ($subdirectory2 in $subdirectories2){
if ($subdirectories2) {
$Sheet.Cells.Item($intRow,3) = $subdirectory2.name
$Sheet.Cells.Item($intRow,3).Borders.Linestyle = 1
$Sheet.Cells.Item($intRow,3).Font.Italic=$True
$subpath3 = $subpath2 + "\" + $subdirectory2.Name
$access1 = Get-Acl $subpath3
$rights = $access1.Access
$intcol = 4
foreach ($item in $rights){
[string]$veld = $item.IdentityReference
$veld = $veld.Replace("AD\","")
$Sheet.Cells.Item($intRow,$intcol) = $veld
$Sheet.Cells.Item($intRow,$intcol).Borders.Linestyle = 1
$intcol++
}
$intRow++
}
}
}
$intRow++
}
Remove-PSDrive -Name "Netwerkdrive"
$Sheet.UsedRange.EntireColumn.AutoFit()

Find services running under credentials domain administrator account

posted Jul 17, 2013, 1:23 AM by Jurien Bosman   [ updated Jul 17, 2013, 1:24 AM ]

On many servers services are running under the domain administrator account.
To find out which services they are i wrote a little script. It checks all domain computers running Windows Server


$servers = get-qadcomputer | where { $_.OSName -match "Server"} | sort Name
foreach ($server in $servers) {
$hostname = $server.name
#Write-Host $hostname
if (Test-Connection -ComputerName $hostname -BufferSize 16 -Count 1 -ea 0 -quiet)
{
Write-Host $hostname -ForegroundColor blue
gwmi win32_service -computer $hostname | where {$_.StartName -eq "domain\administrator"} | select name
}
} 

Enable-forwarding-Exchange

posted May 7, 2012, 5:29 AM by Jurien Bosman

This script can be used to enable forwarding of mail to an external address. There are three forwarding options.
In this script a contact will also be created if it doesn't exist.


function set_forward_mail($username, $forwarding_mode, $forwarding_address)
{
  # forwarding_mode
  # 0 = forwarding disabled
  # 1 = forward without local delivery
  # 2 = forward with local delivery
  if ($forwarding_mode -eq "2")
    {
      if (!(get-qadobject -identity "$username (forwarded by PowershellApp)"))
      {
        # contact doesn't exist (yet). Create now
        New-QADObject -ParentContainer "$base_security_groups_container$FQDN" -type "contact" -name "$username (forwarded by PowershellApp)" -DisplayName "$username (forwarded by PowershellApp)" -ObjectAttributes @{Description="$username (forwarded by PowershellApp)";mail="$forward_address";targetAddress="SMTP:$forwarding_address";mailNickname="$username"+"_forwarded_by_PowershellApp";msExchHideFromAddressLists=$true}
        # Recipient Update Service will do the rest.
        # Set the forwarding mode, type 2
        $forward_user_dn = (Get-QADObject -identity "$username (forwarded by PowershellApp)" | Select-Object dn).dn
        set-qaduser -identity $username -objectAttributes @{deliverAndRedirect=$true;altRecipient=$forward_user_dn}
      }
      else
      {
        # contact DOES exist. Update
        set-qadobject -identity "$username (forwarded by PowershellApp)" -ObjectAttributes @{Description="$username (forwarded by PowershellApp)";mail="$forward_address";targetAddress="SMTP:$forwarding_address";mailNickname="$username"+"_forwarded_by_PowershellApp";msExchHideFromAddressLists=$true}
        # clear any old addresses in the list of addresses and make the new one primary
        get-qadobject -identity "$username (forwarded by PowershellApp)" | Clear-QADProxyAddress | Add-QADProxyAddress -Address "SMTP:$forwarding_address" -Primary
        # make sure the forwarding mode is correct, type 2
        $forward_user_dn = (Get-QADObject -identity "$username (forwarded by PowershellApp)" | Select-Object dn).dn
        set-qaduser -identity $username -objectAttributes @{deliverAndRedirect=$true;altRecipient=$forward_user_dn}
      }
    }
  if ($forwarding_mode -eq "1")
    {
      if (!(get-qadobject -identity "$username (forwarded by PowershellApp)"))
      {
        # contact doesn't exist (yet). Create now
        New-QADObject -ParentContainer "$base_security_groups_container$FQDN" -type "contact" -name "$username (forwarded by PowershellApp)" -DisplayName "$username (forwarded by PowershellApp)" -ObjectAttributes @{Description="$username (forwarded by PowershellApp)";mail="$forward_address";targetAddress="SMTP:$forwarding_address";mailNickname="$username"+"_forwarded_by_PowershellApp";msExchHideFromAddressLists=$true}
        # Recipient Update Service will do the rest.
        # Set the forwarding mode, type 2
        $forward_user_dn = (Get-QADObject -identity "$username (forwarded by PowershellApp)" | Select-Object dn).dn
        set-qaduser -identity $username -objectAttributes @{deliverAndRedirect=$false;altRecipient=$forward_user_dn}
      }
      else
      {
        # contact DOES exist. Update
        set-qadobject -identity "$username (forwarded by PowershellApp)" -ObjectAttributes @{Description="$username (forwarded by PowershellApp)";mail="$forward_address";targetAddress="SMTP:$forwarding_address";mailNickname="$username"+"_forwarded_by_PowershellApp";msExchHideFromAddressLists=$true}
        # clear any old addresses in the list of addresses and make the new one primary
        get-qadobject -identity "$username (forwarded by PowershellApp)" | Clear-QADProxyAddress | Add-QADProxyAddress -Address "SMTP:$forwarding_address" -Primary
        # make sure the forwarding mode is correct, type 2
        $forward_user_dn = (Get-QADObject -identity "$username (forwarded by PowershellApp)" | Select-Object dn).dn
        set-qaduser -identity $username -objectAttributes @{deliverAndRedirect=$false;altRecipient=$forward_user_dn}
      }
    }
  if ($forwarding_mode -eq "0")
    {
      if (!(get-qadobject -identity "$username (forwarded by PowershellApp)"))
      {
        # contact doesn't exist, just disable forwarding
        set-qaduser -identity $username -objectAttributes @{deliverAndRedirect=$false;altRecipient=""}
      }
      else
      {
        # contact DOES exist. disable forwarding and delete contact
        set-qaduser -identity $username -objectAttributes @{deliverAndRedirect=$false;altRecipient=""}
        Remove-QADObject -identity "$username (forwarded by PowershellApp)" -Force
      }
    }
}

Netwerk configuratie ESX 5 PowerCli updated

posted Feb 3, 2012, 3:04 PM by Jurien Bosman   [ updated Feb 4, 2012, 12:52 PM ]

cls
#variabelen
$hostname = Read-Host "Type de naam in van de te configureren ESX-host"
$ntpserver = "ntp.domain.local.com"
$esxdomein = "domain.local"

$gethostnameip = Import-Csv c:\script\vmware\hosts.csv | where-object {$_.hostname -eq $hostname}
$iscsiip = Import-Csv c:\script\vmware\iscsi_ip.csv | where-object {$_.hostname -eq $hostname}
$HostnameIP = $gethostnameip.ipnummer
$vmotionip = $gethostnameip.vmotionip
$vmkip1 = $iscsiip.iscsi1
$vmkip2 = $iscsiip.iscsi2
$vmkip3 = $iscsiip.iscsi3
$vmkip4 = $iscsiip.iscsi4

#vaste waarden

#connecteren met de te configureren esx-host
Connect-VIServer $HostnameIP -User root -Password xxxxxxxxx

#hostname en domein instellen
$esxcli = Get-EsxCli
$esxcli.system.hostname.set($esxdomein,$null,$hostname)

#Standaard aangemaakte portgroup VM Network verwijderen
$DefaultPG = Get-VirtualPortgroup -Name 'VM Network' -ErrorAction SilentlyContinue
Remove-VirtualPortGroup -VirtualPortGroup $DefaultPG -Confirm:$false -ErrorAction SilentlyContinue

#Vmnic toevoegen aan vSwitch0
Set-VirtualSwitch -VirtualSwitch vSwitch0 -Nic vmnic0,vmnic6 -Confirm:$false

#vMotion toevoegen aan vSwitch0
New-VirtualPortGroup -Name vMotion -VirtualSwitch vSwitch0 -VLanId 240
New-VMHostNetworkAdapter -VirtualSwitch vSwitch0 -PortGroup vMotion -IP $vmotionip -VMotionEnabled:$true -SubnetMask 255.255.255.0 -ErrorAction SilentlyContinue


## Definiëren vSwitch1
New-VirtualSwitch -Name vSwitch1 -nic vmnic1,vmnic2 -NumPorts 256 -ErrorAction SilentlyContinue
# Definiëren NicTeamingPolicy
$policy = Get-VirtualSwitch -Name vSwitch1 | Get-NicTeamingPolicy
$policy | Set-NicTeamingPolicy -LoadBalancingPolicy LoadBalanceIP
# Definiëren Portgroepen
New-VirtualPortGroup -Name ServerLan -VirtualSwitch vSwitch1
New-VirtualPortGroup -Name "VLAN50" -VirtualSwitch vSwitch1 -VLanId 50
New-VirtualPortGroup -Name "VLAN100" -VirtualSwitch vSwitch1 -VLanId 100
New-VirtualPortGroup -Name "VLAN150" -VirtualSwitch vSwitch1 -VLanId 150
New-VirtualPortGroup -Name "VLAN200" -VirtualSwitch vSwitch1 -VLanId 200

# Definiëren vSwitch2
New-VirtualSwitch -Name vSwitch2 -nic vmnic3,vmnic4,vmnic7,vmnic8 -NumPorts 64 -ErrorAction SilentlyContinue

# Definiëren NicTeamingPolicy
$policy = Get-VirtualSwitch -Name vSwitch2 | Get-NicTeamingPolicy
$policy | Set-NicTeamingPolicy -LoadBalancingPolicy LoadBalanceIP

# Definiëren Portgroepen
#iSCSI1
New-VirtualPortGroup -Name iSCSI1 -VirtualSwitch vSwitch2 -VLanId 250 -ErrorAction SilentlyContinue
$policy = Get-VirtualSwitch -Name vSwitch2 | Get-VirtualPortGroup -Name iSCSI1 | Get-NicTeamingPolicy
$policy | Set-NicTeamingPolicy -MakeNicUnused vmnic7,vmnic8,vmnic4 -MakeNicActive vmnic3
#iSCSI2
New-VirtualPortGroup -Name iSCSI2 -VirtualSwitch vSwitch2 -VLanId 250 -ErrorAction SilentlyContinue
$policy = Get-VirtualSwitch -Name vSwitch2 | Get-VirtualPortGroup -Name iSCSI2 | Get-NicTeamingPolicy
$policy | Set-NicTeamingPolicy -MakeNicUnused vmnic7,vmnic8,vmnic3 -MakeNicActive vmnic4
#iSCSI3
New-VirtualPortGroup -Name iSCSI3 -VirtualSwitch vSwitch2 -VLanId 250 -ErrorAction SilentlyContinue
$policy = Get-VirtualSwitch -Name vSwitch2 | Get-VirtualPortGroup -Name iSCSI3 | Get-NicTeamingPolicy
$policy | Set-NicTeamingPolicy -MakeNicUnused vmnic4,vmnic8,vmnic3 -MakeNicActive vmnic7
#iSCSI4
New-VirtualPortGroup -Name iSCSI4 -VirtualSwitch vSwitch2 -VLanId 250 -ErrorAction SilentlyContinue
$policy = Get-VirtualSwitch -Name vSwitch2 | Get-VirtualPortGroup -Name iSCSI4 | Get-NicTeamingPolicy
$policy | Set-NicTeamingPolicy -MakeNicUnused vmnic7,vmnic4,vmnic3 -MakeNicActive vmnic8

#Omzetten vm network naar vmk
$vs = Get-VirtualSwitch -Name "vSwitch2" -ErrorAction SilentlyContinue
New-VMHostNetworkAdapter -VirtualSwitch $vs -PortGroup iSCSI1 -IP $vmkip1 -SubnetMask 255.255.255.0 -ErrorAction SilentlyContinue
New-VMHostNetworkAdapter -VirtualSwitch $vs -PortGroup iSCSI2 -IP $vmkip2 -SubnetMask 255.255.255.0 -ErrorAction SilentlyContinue
New-VMHostNetworkAdapter -VirtualSwitch $vs -PortGroup iSCSI3 -IP $vmkip3 -SubnetMask 255.255.255.0 -ErrorAction SilentlyContinue
New-VMHostNetworkAdapter -VirtualSwitch $vs -PortGroup iSCSI4 -IP $vmkip4 -SubnetMask 255.255.255.0 -ErrorAction SilentlyContinue

# enable iSCSI adapter
Get-VMHostStorage -VMHost $HostnameIP | Set-VMHostStorage -SoftwareIScsiEnabled $True -ErrorAction SilentlyContinue
#Lees het HBA nummer van de iSCSI Software Adapter
$HBANumber = Get-VMHostHba -VMHost $HostnameIP -Type iSCSI | %{$_.Device}

#Laat PowerCLI gebruik maken van esxcli commandos
$esxcli = Get-EsxCli

#vKernel Nummers iSCSI
$vmk1nummer = Get-VMHostNetworkAdapter | Where-object { $_.IP -match $vmkip1 } | %{ $_.Name }
$vmk2nummer = Get-VMHostNetworkAdapter | Where-object { $_.IP -match $vmkip2 } | %{ $_.Name }
$vmk3nummer = Get-VMHostNetworkAdapter | Where-object { $_.IP -match $vmkip3 } | %{ $_.Name }
$vmk4nummer = Get-VMHostNetworkAdapter | Where-object { $_.IP -match $vmkip4 } | %{ $_.Name }

#Binds VMKernel ports aan de iSCSI Software Adapter HBA
$EsxCli.iscsi.networkportal.add($HbANumber,$null,$vmk1nummer)
$EsxCli.iscsi.networkportal.add($HbANumber,$null,$vmk2nummer)
$EsxCli.iscsi.networkportal.add($HbANumber,$null,$vmk3nummer)
$EsxCli.iscsi.networkportal.add($HbANumber,$null,$vmk4nummer)

#timeserver configuratie
$arrNTPList = Get-VMHostNtpServer -VMHost $HostnameIP
Remove-VMHostNtpServer -VMHost $HostnameIP -NtpServer $arrNTPList -Confirm:$false -ErrorAction SilentlyContinue
Add-VMHostNtpServer -VMHost $HostnameIP -NtpServer $ntpserver -Confirm:$false
Set-VMHostService -HostService (Get-VMHostservice -VMHost (Get-VMHost $HostnameIP) | Where-Object {$_.key -eq "ntpd"}) -Policy "Automatic"
Get-VmhostFirewallException -VMHost $HostnameIP -Name "NTP Client" | Set-VMHostFirewallException -enabled:$true
$ntpd = Get-VMHostService -VMHost $HostnameIP | where {$_.Key -eq 'ntpd'}
Restart-VMHostService $ntpd -Confirm:$false

#reboot ESX host
Get-VMHost $HostnameIP |%{Get-View $_.ID} | %{$_.RebootHost_Task($TRUE)}

#disconnect
Disconnect-VIServer -Confirm:false

Blog moved

posted May 23, 2011, 7:53 AM by Jurien Bosman

Because i want to do stuff on my blog that (yet) can't be done using google sites i decided to move my blog.
You can find it at : http://blog.v-apps.nl

thanks!

Check uptime servers (in this case Citrix servers which reboot every day)

posted Mar 2, 2011, 12:14 AM by Jurien Bosman

At my current job we're running 16 Citrix servers on 8 blades running Citrix-XenServer.
They're supposed to boot everyday so a fresh image is loaded on a daily base.
However sometimes sometimes one or two of the servers are not booted, hanging in shutting down.
This causes people not being abled to logon with an ica session.

To check wether a server has booted i created a little script to check upon the uptime.
If the number of days is not 0 (zero) a message is being sent.

cls
$computerlist = "c:\scripts\inventaris\citrix_servers.txt"
$computers = Get-Content $computerlist
$body = "Citrix Server(s) not booted this morning:"
foreach ($computer in $computers)

{

    $wmi = gwmi -ComputerName $computer -Query "SELECT LastBootUpTime FROM Win32_OperatingSystem"
    $now = Get-Date
    $boottime = $wmi.ConvertToDateTime($wmi.LastBootUpTime)
    $uptime = $now - $boottime
    $d =$uptime.days
    $h =$uptime.hours
    $m =$uptime.Minutes
    $s = $uptime.Seconds
    $Info = "$d Days $h Hours $m Min $s Sec"
    if ($d -gt "0"){
    $body = $body + "`r`n $computer        uptime: $info" 
    }

}

$emailFrom = "Citrix_Uptime@domain.local"
$emailTo = "helpdesk@domain.local"
$subject = "Server uptime Citrix)"

$smtpServer = "mail01"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($emailFrom, $emailTo, $subject, $body)

Facebook like button on google-site

posted Jan 6, 2011, 7:17 AM by Jurien Bosman   [ updated Jan 6, 2011, 7:31 AM ]

No scripting involved, but just for fun!

You need to create 2 files: ilike.xml and ilike.htm

Content ilike.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<Module>
  <ModulePrefs title="iLike"
  directory_title="iLike"
  description="iframe"
  author="Your Name" author_email="Your E-mail"
  author_link="Your Website"
  scrolling="true"
  category="Facebook">
  </ModulePrefs>
<Content type="url"  href="http://location of ilike.htm"/>
</Module>


Content ilike.htm:

<iframe src="http://www.facebook.com/plugins/like.php?href=http%3A%2F%2Fwww.somewebsite.com&amp;layout=box_count&amp;show_faces=false&amp;width=100&amp;action=like&amp;font=tahoma&amp;colorscheme=light&amp;height=65" scrolling="no" frameborder="0" style="border:none; overflow:hidden; width:100px; height:65px;" allowTransparency="true"></iframe>

replace www.somewebsite.com with the URL of your own domain.

Put these files outside of your google-site but somewhere where you can reach them.

Now you can add a gadget to your page, just point to the ilike.xml file and you're ready to go!

I Like V-Apps

The gadget spec URL could not be found

ODBC connections inventory

posted Nov 30, 2010, 7:48 AM by Jurien Bosman

Ever wondered what ODBC connections are defined on all servers in your farm?

This script leads you in the right direction, don't copy one on one because you could be missing something.
Also the results of this script are presented in Excel.

$computers = Get-QADComputer  | sort | where {$_.operatingSystem -match "2003" -AND $_.description -notmatch "disabled" -and $_.name -notmatch "CTX" -and $_.name -notmatch "SEQ02" -and $_.name -notmatch "IBV_S"}

#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True

$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

#Counter variable for rows
$intRow = 1
 

#Read all hosts
foreach ($computer in $computers){
$Srv = $computer.name
#Create column headers
     $Sheet.Cells.Item($intRow,1) = "Hostname:"
     $Sheet.Cells.Item($intRow,2) = $srv
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True
     $intRow++
     
     
      $Sheet.Cells.Item($intRow,1) = "ODBC NAME"
      $Sheet.Cells.Item($intRow,2) = "Database"
      $Sheet.Cells.Item($intRow,3) = "Driver"
      $Sheet.Cells.Item($intRow,4) = "LastUser"
      $Sheet.Cells.Item($intRow,5) = "Server"
      #Format the column headers
     for ($col = 1; $col –le 5; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }
     $intRow++
     $pingresult = ping $srv
if ($pingresult -match "reply from"){


$key = "SOFTWARE\ODBC\ODBC.ini\ODBC Data Sources"
$type = [Microsoft.Win32.RegistryHive]::LocalMachine
$regKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $Srv)
$regKey = $regKey.OpenSubKey($key)


if ($regkey -ne $null){
Foreach($sub in $regKey.GetSubKeyNames()){$sub}

Foreach($val in $regkey.GetValueNames()){

$Sheet.Cells.Item($intRow,1) = $val

$odbckey = "SOFTWARE\ODBC\ODBC.ini\$val"
$valuename1 = "Database"
$valuename2 = "Driver"
$valuename3 = "LastUser"
$valuename4 = "Server"

$odbcreg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $srv)
$odbcregkey = $odbcreg.opensubkey($odbckey)
$database = $odbcregkey.getvalue($valuename1)
$driver = $odbcregkey.getvalue($valuename2)
$lastuser = $odbcregkey.getvalue($valuename3)
$server = $odbcregkey.getvalue($valuename4)
$Sheet.Cells.Item($intRow,2) = $database
$Sheet.Cells.Item($intRow,3) = $driver
$Sheet.Cells.Item($intRow,4) = $lastuser
$Sheet.Cells.Item($intRow,5) = $server

$intRow++} }
}
else {$Sheet.Cells.Item($intRow,1) = "No ODBC connections on $srv"
$intRow++}
}
$Sheet.UsedRange.EntireColumn.AutoFit()


Database script

posted Nov 30, 2010, 7:30 AM by Jurien Bosman

The following script creates an excel sheet with the following values:
DATABASE NAME    COLLATION    COMPATIBILITY LEVEL    AUTOSHRINK    RECOVERY MODEL    SIZE (MB)    SPACE AVAILABLE (MB)

Make sure you've got a text-file with servernames and put it in C:\scripts\SQL\Servers.txt:
example:
SQL01
SQL02
SQL03
etc.

Code:
CLS
#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True

$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

#Counter variable for rows
$intRow = 1

#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "C:\Scripts\SQL\Servers.txt")
{

     #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "DATABASE NAME"
      $Sheet.Cells.Item($intRow,2) = "COLLATION"
      $Sheet.Cells.Item($intRow,3) = "COMPATIBILITY LEVEL"
      $Sheet.Cells.Item($intRow,4) = "AUTOSHRINK"
      $Sheet.Cells.Item($intRow,5) = "RECOVERY MODEL"
      $Sheet.Cells.Item($intRow,6) = "SIZE (MB)"
      $Sheet.Cells.Item($intRow,7) = "SPACE AVAILABLE (MB)"

     #Format the column headers
     for ($col = 1; $col –le 7; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
      #######################################################
     #This script gets SQL Server database information using PowerShell

     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

     # Create an SMO connection to the instance
     $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

     $dbs = $s.Databases

 
     #Formatting using Excel

     ForEach ($db in $dbs)
     {

          #Divide the value of SpaceAvailable by 1KB
          $dbSpaceAvailable = $db.SpaceAvailable/1KB

          #Format the results to a number with three decimal places
          $dbSpaceAvailable = "{0:N3}" -f $dbSpaceAvailable

          $Sheet.Cells.Item($intRow, 1) = $db.Name
          $Sheet.Cells.Item($intRow, 2) = $db.Collation
          $Sheet.Cells.Item($intRow, 3) = $db.CompatibilityLevel

           #Change the background color of the Cell depending on the AutoShrink property value
           if ($db.AutoShrink -eq "True")
          {
               $fgColor = 3
          }
          else
          {
               $fgColor = 0
          }

          $Sheet.Cells.Item($intRow, 4) = $db.AutoShrink
          $Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor

          $Sheet.Cells.Item($intRow, 5) = $db.RecoveryModel
          $Sheet.Cells.Item($intRow, 6) = "{0:N3}" -f $db.Size

          #Change the background color of the Cell depending on the SpaceAvailable property value
          if ($dbSpaceAvailable -lt 1.00)
          {
               $fgColor = 3
          }
           else
          {
               $fgColor = 0
          }

          $Sheet.Cells.Item($intRow, 7) = $dbSpaceAvailable
          $Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor

          $intRow ++

     }

$intRow ++

}

$Sheet.UsedRange.EntireColumn.AutoFit()
cls


Retrieve Server OS and platform using Powershell

posted Nov 30, 2010, 5:06 AM by Jurien Bosman

I was looking for a way to get an overview of all servers in the domain regarding to Operating System and platform.
This is the first script in here using get-WMIOBJECT

Use WMI Explorer to find the properties you are looking for.

Again, if i can be of any help, just let me know.

Here's the script, it gives an idea how to use the get-wmiobject command in PS:

cls
$strPath="c:\xml\computers.xls"
$a = new-object -comobject excel.application
$a.Visible = $true
if (Test-Path $strPath)
{
# Open the document
$b = $a.WorkBooks.Open($strPath)
$c = $b.Worksheets.Item(1)
} else {
# Create It
$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)
#$c.Cells.Item(1,1) = "_Computername"
#$c.cells.item(1,2) = "_Operating System"
#$c.cells.item(1,3) = "_Harware model"
$d = $c.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True

$introw = $c.UsedRange.Rows.Count + 1
$computers = Get-QADComputer -OSName 'Windows*Server*'
foreach ($computer in $computers){
$name = $computer.name
$OS = $computer.operatingSystem
$uuu = $OS.Substring(0,14)
$ServerOS = Get-WmiObject Win32_OperatingSystem -ComputerName $computer.name
$Platform = Get-WmiObject Win32_ComputerSystem -ComputerName $computer.name
$SOS = $ServerOS.caption
$hw = $Platform.Model
if ($uuu -eq 'Windows Server') {
$introw = $introw  + 1
$c.cells.item($introw, 1) = $name
$c.cells.item($introw, 2) = $SOS
$c.cells.item($introw, 3) = $hw
}}
$objRange = $c.UsedRange
$objRange2 = $a.Range("A1")
[void] $objRange.Sort($objRange2)
$objRange.NumberFormat = "0#########"
$objRange.EntireColumn.AutoFit() |out-null

1-10 of 12

Comments