Welcome >
Scripting
Retrieve information of network share and put results in Excel
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
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
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
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
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)
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
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&layout=box_count&show_faces=false&width=100&action=like&font=tahoma&colorscheme=light&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! |
ODBC connections inventory
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
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
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