Well after the roaring success of this post I've had a stream of people (well at least one) knocking at my door to do something similar but showing our Oracle installations in Azure.
Now if you're a little bit familiar with Oracle on Azure you'll know there is no PaaS offering here - you have to run it in the traditional way on IaaS (it works just as well as on premises just be very mindful of the IO layout you put in....). The fact that it's just something running on IaaS means though that Azure as a platform has no clue at all that Oracle is running on there - it's not like you can just go and look in the portal for oracle databases.
So trying to find out what you have is not possible right? A whole load of disconnected servers in different subscriptions and networks with no common interface to them - I'm sure you are all asking how can this be done?
Well let me tell you - we can use a wonderful PowerShell script to achieve this - the key part being a little cmdlet called Invoke-AzVMRuncommand - as long as the user that runs that command has the 'virtual machine contributor' role it is able to execute commands on any server you are in the context of in Azure - you don't need to be able to reach it via the ip network and you don't even need to authenticate explicitly - the cmdlet is 'allowed' to do this.
So powered by this cmdlet I built a whole wrapper around it to go out and fetch the details of what is installed. Now this discovery is fairly basic - it's not going to tell you options in use - it's just going to deal with the basics of ORACLE_HOME, ORACLE_SID,VERSION, DATABASE NAME and if the database is actually running. For us that is enough for our immediate needs - it's not going to be everything you need for a full audit of what you have but it's a really good start.
I've pasted the script in it's entirety at the end of this post - so if you want to jump ahead and just cut and paste it then go for it - I'll spend the next few paragraphs explaining what it's doing and how it works as I think it's useful to be able to follow my logic and learn some PowerShell along the way.
To be able to do the full serverless deployment - please refer back to that earlier post - the steps are exactly the same - the only addition is making sure the function app has virtual machine contributor in addition to everything else and possibly increasing the timeout to a few hours (the cmdlet seems to often hand for quite a while in the actual connecting phase though the script being run on the destination server is almost instant.
OK - onto the explanation - I'll start by again referring here http://ramblingcookiemonster.github.io/Join-Object/ for the excellent join-object function I make use of in the code - thanks Warren.
The first part of the code is just the standard function app headers and Warrens code so I won't say anything more there and I'll jump on to the more interesting parts.
The first one is the bit of PowerShell that writes out a bash script (the thing I actually want to run on the Linux machine) - not this was an exercise in understanding all the different types of quotes and escape character in PowerShell - so the end result contains a well formatted bash script - this was no mean feat I can assure you. This end up looking like this:
The end result being a bash script called (originally enough) script.sh.
Now it's worth looking at what this will actually do as this is the key thing that tells us if Oracle is installed or not and what that looks like when it is.
So in the screenshot above - here is an explanation of the lines..... (for a short script this needs quite a bit of explanation.....)
515 - define that this script should be executed using the bash shell
516 - Define the internal file separator as just being a newline character - by default it treats spaces/tabs also as a separator and this messes up later processing
517 - start a loop for every line in the oratab that isn't commented out and doesn't contain the word agent
519 - pull the ORACLE_HOME out of the oratab line
520 - pull the database name out of the oratab line
521 - find the actual version of the software using the sqlplus header (some processing to cope with the way the header changes in some versions)
522 - check if the database is actually running
523 - echo out all the above info I found + the word YOOHOO which i will make use of later
Right now that's out of the way and I've reattached all the quote keys to my keyboard after smashing them all up working out how to create this we can move on in the script to this section
Here I initialize a few basic variables (couple of arrays and a date value) then I do the following (again explaining some of the lines below)
530 - fetch all the subscriptions into a variable
531/532 - loop through those setting the context to each one as we go round the loop
535 - do a resource graph query to return all the Linux machines in that subscription (now I've added some extra criteria here to filter out some of the noise but you don't have to do that.) I exclude anything that hasn't got ORA in the name of the VM, anything where the name starts with aks (these are machine created by the AKS PaaS service that i don't need to check), anything where the plan is not null (appliance machines will have this set to something - NetApp for example) - and finally only return values where the machine is running as the cmdlet can do nothing against a powered down machine.
The reason that I look specifically for machines with Ora in is that the dataset was too huge to do everything in one pass (and have it complete in a reasonable time) so I have 2 version of the script running in parallel - one looking for ORA and the other doing the inverse. Hopefully in your case that logic isn't required. Also note that I did this as a different function in a different function app. It seems 2 functions in the same functionapp interfere with each other - it kind of looks like only one function can be executed at a time.....
537 - open a new loop within the main loop do do something for every VM
538 - run the magic cmdlet to execute the script i created earlier and store the output in a variable
541 - define a pattern to look for (the YOOHOO thing I set in the script) - this allows me to pull out the lines of interest from the rest of the noise in the output
544 - split the output into individual lines
547-552 parse the output data (this is quite a minor work of art in itself and took me a while to figure out)
554-558 - expand the array of data i now have with some additional information (like the vm name , subscription etc)
562 - append the final output from that VM to the FINRESULT array - the loop continues round adding each 'row' to this array
OK - that's that stage complete - now i need to enrich that dataset with some more info - like tags that contain some extra information
This is what that section looks like
So to explain here
568-572 is doing a resource graph query to pull out various elements of what Azure knows - i do a few joins in resource graph to pull everything out I want
575 - i then join the ARG array i just got to the earlier FINRESULT array using a column i created called joiner in both sides. This column is a concatenation of vmname, resourcegroup and subscription - the combination of which is unique for the join
Final stretch now kids - last few lines are here:
And final bit of explanation
578 - fetch the metadata about all the machine sizes available in West Europe into yet another array variable
580 - join the previously joined data to this VM metadata (the reason i do this is primarily just to get the cpu count - which as we all know is a key metric for oracle licencing - I also have the VM series from which we can see if hyperthreading is relevant or not.
582 - output the file to a temporary local file - quoting some of the columns that contain 'funny' characters that will confuse the csv format
586-587 - write that file out to blob storage with a date stamp
And there you have it - a list of al of your oracle database across all of your Azure estate - the impossible made possible :-)
Caveats though before you all run off and copy the script and do this yourself
a) only works against Linux - Oracle on Windows is not something we do so script does not cater for that. I guess something similar is possible - you'd maybe just need some PowerShell equivalent of the discovery I did
b) Only works if the machines are up - we scheduled the script to run during office hours as we heavily make use of shutting down machines to save costs out of normal hours
c) relies on using the oratab file in the 'normal' way
d) only be tested on 11.2 and up - older versions may not work the same way
e) It really only covers basic discovery - if you have a more sophisticated script then I guess you can just call that via this method and parse the output differently.
A quick sample of the output with some columns hidden and some data redacted is shown below
And here is the script in all its magnificence.....
# Input bindings are passed in via param block. param($Timer) # Get the current universal time in the default string format. $currentUTCtime = (Get-Date).ToUniversalTime() # The 'IsPastDue' property is 'true' when the current function invocation is later than scheduled. if ($Timer.IsPastDue) { Write-Host "PowerShell timer is running late!" } # Write an information log with the current time. Write-Host "PowerShell timer trigger function ran! TIME: $currentUTCtime" function Join-Object { <# .SYNOPSIS Join data from two sets of objects based on a common value .DESCRIPTION Join data from two sets of objects based on a common value For more details, see the accompanying blog post: http://ramblingcookiemonster.github.io/Join-Object/ For even more details, see the original code and discussions that this borrows from: Dave Wyatt's Join-Object - http://powershell.org/wp/forums/topic/merging-very-large-collections Lucio Silveira's Join-Object - http://blogs.msdn.com/b/powershell/archive/2012/07/13/join-object.aspx .PARAMETER Left 'Left' collection of objects to join. You can use the pipeline for Left. The objects in this collection should be consistent. We look at the properties on the first object for a baseline. .PARAMETER Right 'Right' collection of objects to join. The objects in this collection should be consistent. We look at the properties on the first object for a baseline. .PARAMETER LeftJoinProperty Property on Left collection objects that we match up with RightJoinProperty on the Right collection .PARAMETER RightJoinProperty Property on Right collection objects that we match up with LeftJoinProperty on the Left collection .PARAMETER LeftProperties One or more properties to keep from Left. Default is to keep all Left properties (*). Each property can: - Be a plain property name like "Name" - Contain wildcards like "*" - Be a hashtable like @{Name="Product Name";Expression={$_.Name}}. Name is the output property name Expression is the property value ($_ as the current object) Alternatively, use the Suffix or Prefix parameter to avoid collisions Each property using this hashtable syntax will be excluded from suffixes and prefixes .PARAMETER RightProperties One or more properties to keep from Right. Default is to keep all Right properties (*). Each property can: - Be a plain property name like "Name" - Contain wildcards like "*" - Be a hashtable like @{Name="Product Name";Expression={$_.Name}}. Name is the output property name Expression is the property value ($_ as the current object) Alternatively, use the Suffix or Prefix parameter to avoid collisions Each property using this hashtable syntax will be excluded from suffixes and prefixes .PARAMETER Prefix If specified, prepend Right object property names with this prefix to avoid collisions Example: Property Name = 'Name' Suffix = 'j_' Resulting Joined Property Name = 'j_Name' .PARAMETER Suffix If specified, append Right object property names with this suffix to avoid collisions Example: Property Name = 'Name' Suffix = '_j' Resulting Joined Property Name = 'Name_j' .PARAMETER Type Type of join. Default is AllInLeft. AllInLeft will have all elements from Left at least once in the output, and might appear more than once if the where clause is true for more than one element in right, Left elements with matches in Right are preceded by elements with no matches. SQL equivalent: outer left join (or simply left join) AllInRight is similar to AllInLeft. OnlyIfInBoth will cause all elements from Left to be placed in the output, only if there is at least one match in Right. SQL equivalent: inner join (or simply join) AllInBoth will have all entries in right and left in the output. Specifically, it will have all entries in right with at least one match in left, followed by all entries in Right with no matches in left, followed by all entries in Left with no matches in Right. SQL equivalent: full join .EXAMPLE # #Define some input data. $l = 1..5 | Foreach-Object { [pscustomobject]@{ Name = "jsmith$_" Birthday = (Get-Date).adddays(-1) } } $r = 4..7 | Foreach-Object{ [pscustomobject]@{ Department = "Department $_" Name = "Department $_" Manager = "jsmith$_" } } #We have a name and Birthday for each manager, how do we find their department, using an inner join? Join-Object -Left $l -Right $r -LeftJoinProperty Name -RightJoinProperty Manager -Type OnlyIfInBoth -RightProperties Department # Name Birthday Department # ---- -------- ---------- # jsmith4 4/14/2015 3:27:22 PM Department 4 # jsmith5 4/14/2015 3:27:22 PM Department 5 .EXAMPLE # #Define some input data. $l = 1..5 | Foreach-Object { [pscustomobject]@{ Name = "jsmith$_" Birthday = (Get-Date).adddays(-1) } } $r = 4..7 | Foreach-Object{ [pscustomobject]@{ Department = "Department $_" Name = "Department $_" Manager = "jsmith$_" } } #We have a name and Birthday for each manager, how do we find all related department data, even if there are conflicting properties? $l | Join-Object -Right $r -LeftJoinProperty Name -RightJoinProperty Manager -Type AllInLeft -Prefix j_ # Name Birthday j_Department j_Name j_Manager # ---- -------- ------------ ------ --------- # jsmith1 4/14/2015 3:27:22 PM # jsmith2 4/14/2015 3:27:22 PM # jsmith3 4/14/2015 3:27:22 PM # jsmith4 4/14/2015 3:27:22 PM Department 4 Department 4 jsmith4 # jsmith5 4/14/2015 3:27:22 PM Department 5 Department 5 jsmith5 .EXAMPLE # #Hey! You know how to script right? Can you merge these two CSVs, where Path1's IP is equal to Path2's IP_ADDRESS? #Get CSV data $s1 = Import-CSV $Path1 $s2 = Import-CSV $Path2 #Merge the data, using a full outer join to avoid omitting anything, and export it Join-Object -Left $s1 -Right $s2 -LeftJoinProperty IP_ADDRESS -RightJoinProperty IP -Prefix 'j_' -Type AllInBoth | Export-CSV $MergePath -NoTypeInformation .EXAMPLE # # "Hey Warren, we need to match up SSNs to Active Directory users, and check if they are enabled or not. # I'll e-mail you an unencrypted CSV with all the SSNs from gmail, what could go wrong?" # Import some SSNs. $SSNs = Import-CSV -Path D:\SSNs.csv #Get AD users, and match up by a common value, samaccountname in this case: Get-ADUser -Filter "samaccountname -like 'wframe*'" | Join-Object -LeftJoinProperty samaccountname -Right $SSNs ` -RightJoinProperty samaccountname -RightProperties ssn ` -LeftProperties samaccountname, enabled, objectclass .NOTES This borrows from: Dave Wyatt's Join-Object - http://powershell.org/wp/forums/topic/merging-very-large-collections/ Lucio Silveira's Join-Object - http://blogs.msdn.com/b/powershell/archive/2012/07/13/join-object.aspx Changes: Always display full set of properties Display properties in order (left first, right second) If specified, add suffix or prefix to right object property names to avoid collisions Use a hashtable rather than ordereddictionary (avoid case sensitivity) .LINK http://ramblingcookiemonster.github.io/Join-Object/ .FUNCTIONALITY PowerShell Language #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true, ValueFromPipeLine = $true)] [object[]] $Left, # List to join with $Left [Parameter(Mandatory=$true)] [object[]] $Right, [Parameter(Mandatory = $true)] [string] $LeftJoinProperty, [Parameter(Mandatory = $true)] [string] $RightJoinProperty, [object[]]$LeftProperties = '*', # Properties from $Right we want in the output. # Like LeftProperties, each can be a plain name, wildcard or hashtable. See the LeftProperties comments. [object[]]$RightProperties = '*', [validateset( 'AllInLeft', 'OnlyIfInBoth', 'AllInBoth', 'AllInRight')] [Parameter(Mandatory=$false)] [string]$Type = 'AllInLeft', [string]$Prefix, [string]$Suffix ) Begin { function AddItemProperties($item, $properties, $hash) { if ($null -eq $item) { return } foreach($property in $properties) { $propertyHash = $property -as [hashtable] if($null -ne $propertyHash) { $hashName = $propertyHash["name"] -as [string] $expression = $propertyHash["expression"] -as [scriptblock] $expressionValue = $expression.Invoke($item)[0] $hash[$hashName] = $expressionValue } else { foreach($itemProperty in $item.psobject.Properties) { if ($itemProperty.Name -like $property) { $hash[$itemProperty.Name] = $itemProperty.Value } } } } } function TranslateProperties { [cmdletbinding()] param( [object[]]$Properties, [psobject]$RealObject, [string]$Side) foreach($Prop in $Properties) { $propertyHash = $Prop -as [hashtable] if($null -ne $propertyHash) { $hashName = $propertyHash["name"] -as [string] $expression = $propertyHash["expression"] -as [scriptblock] $ScriptString = $expression.tostring() if($ScriptString -notmatch 'param\(') { Write-Verbose "Property '$HashName'`: Adding param(`$_) to scriptblock '$ScriptString'" $Expression = [ScriptBlock]::Create("param(`$_)`n $ScriptString") } $Output = @{Name =$HashName; Expression = $Expression } Write-Verbose "Found $Side property hash with name $($Output.Name), expression:`n$($Output.Expression | out-string)" $Output } else { foreach($ThisProp in $RealObject.psobject.Properties) { if ($ThisProp.Name -like $Prop) { Write-Verbose "Found $Side property '$($ThisProp.Name)'" $ThisProp.Name } } } } } function WriteJoinObjectOutput($leftItem, $rightItem, $leftProperties, $rightProperties) { $properties = @{} AddItemProperties $leftItem $leftProperties $properties AddItemProperties $rightItem $rightProperties $properties New-Object psobject -Property $properties } #Translate variations on calculated properties. Doing this once shouldn't affect perf too much. foreach($Prop in @($LeftProperties + $RightProperties)) { if($Prop -as [hashtable]) { foreach($variation in ('n','label','l')) { if(-not $Prop.ContainsKey('Name') ) { if($Prop.ContainsKey($variation) ) { $Prop.Add('Name',$Prop[$Variation]) } } } if(-not $Prop.ContainsKey('Name') -or $Prop['Name'] -like $null ) { Throw "Property is missing a name`n. This should be in calculated property format, with a Name and an Expression:`n@{Name='Something';Expression={`$_.Something}}`nAffected property:`n$($Prop | out-string)" } if(-not $Prop.ContainsKey('Expression') ) { if($Prop.ContainsKey('E') ) { $Prop.Add('Expression',$Prop['E']) } } if(-not $Prop.ContainsKey('Expression') -or $Prop['Expression'] -like $null ) { Throw "Property is missing an expression`n. This should be in calculated property format, with a Name and an Expression:`n@{Name='Something';Expression={`$_.Something}}`nAffected property:`n$($Prop | out-string)" } } } $leftHash = @{} $rightHash = @{} # Hashtable keys can't be null; we'll use any old object reference as a placeholder if needed. $nullKey = New-Object psobject $bound = $PSBoundParameters.keys -contains "InputObject" if(-not $bound) { [System.Collections.ArrayList]$LeftData = @() } } Process { #We pull all the data for comparison later, no streaming if($bound) { $LeftData = $Left } Else { foreach($Object in $Left) { [void]$LeftData.add($Object) } } } End { foreach ($item in $Right) { $key = $item.$RightJoinProperty if ($null -eq $key) { $key = $nullKey } $bucket = $rightHash[$key] if ($null -eq $bucket) { $bucket = New-Object System.Collections.ArrayList $rightHash.Add($key, $bucket) } $null = $bucket.Add($item) } foreach ($item in $LeftData) { $key = $item.$LeftJoinProperty if ($null -eq $key) { $key = $nullKey } $bucket = $leftHash[$key] if ($null -eq $bucket) { $bucket = New-Object System.Collections.ArrayList $leftHash.Add($key, $bucket) } $null = $bucket.Add($item) } $LeftProperties = TranslateProperties -Properties $LeftProperties -Side 'Left' -RealObject $LeftData[0] $RightProperties = TranslateProperties -Properties $RightProperties -Side 'Right' -RealObject $Right[0] #I prefer ordered output. Left properties first. [string[]]$AllProps = $LeftProperties #Handle prefixes, suffixes, and building AllProps with Name only $RightProperties = foreach($RightProp in $RightProperties) { if(-not ($RightProp -as [Hashtable])) { Write-Verbose "Transforming property $RightProp to $Prefix$RightProp$Suffix" @{ Name="$Prefix$RightProp$Suffix" Expression=[scriptblock]::create("param(`$_) `$_.'$RightProp'") } $AllProps += "$Prefix$RightProp$Suffix" } else { Write-Verbose "Skipping transformation of calculated property with name $($RightProp.Name), expression:`n$($RightProp.Expression | out-string)" $AllProps += [string]$RightProp["Name"] $RightProp } } $AllProps = $AllProps | Select -Unique Write-Verbose "Combined set of properties: $($AllProps -join ', ')" foreach ( $entry in $leftHash.GetEnumerator() ) { $key = $entry.Key $leftBucket = $entry.Value $rightBucket = $rightHash[$key] if ($null -eq $rightBucket) { if ($Type -eq 'AllInLeft' -or $Type -eq 'AllInBoth') { foreach ($leftItem in $leftBucket) { WriteJoinObjectOutput $leftItem $null $LeftProperties $RightProperties | Select $AllProps } } } else { foreach ($leftItem in $leftBucket) { foreach ($rightItem in $rightBucket) { WriteJoinObjectOutput $leftItem $rightItem $LeftProperties $RightProperties | Select $AllProps } } } } if ($Type -eq 'AllInRight' -or $Type -eq 'AllInBoth') { foreach ($entry in $rightHash.GetEnumerator()) { $key = $entry.Key $rightBucket = $entry.Value $leftBucket = $leftHash[$key] if ($null -eq $leftBucket) { foreach ($rightItem in $rightBucket) { WriteJoinObjectOutput $null $rightItem $LeftProperties $RightProperties | Select $AllProps } } } } } } echo '#!/bin/bash' >.\script.sh echo "IFS=`$'\n'" >>.\script.sh echo 'for line in `grep -v \^\# /etc/oratab |grep -v agent `' >>.\script.sh echo 'do' >>.\script.sh echo "VERS=``echo `$line |awk -F: '{print `$2}' ;``" >>.\script.sh echo "DB=``echo `$line |awk -F: '{print `$1`}' ;``" >>.\script.sh echo 'ACTUALVERS=`export ORACLE_HOME=${VERS};export LD_LIBRARY_PATH=${VERS}/lib;${VERS}/bin/sqlplus -v |tail -2|head -1`' >>.\script.sh echo 'RUNNING=`ps -ef |grep pmon_${DB} |grep -v grep |wc -l`' >>.\script.sh echo 'echo YOOHOO,"$DB","$VERS","$ACTUALVERS","$RUNNING"' >>.\script.sh echo 'done' >>.\script.sh $script:FINRESULT = @() $script:FINRESULT2 = @() $date = Get-Date -format dd-MM-yyyy $Subscriptions = Get-AzSubscription foreach ($sub in $Subscriptions) { Get-AzSubscription -SubscriptionName $sub.Name | Set-AzContext $vms = Search-AzGraph -Query 'Resources |where type == "microsoft.compute/virtualmachines" | where name contains "ORA" and properties.storageProfile.osDisk.osType=="Linux" and name !startswith "aks" and isnull(plan) and properties.extended.instanceView.powerState.displayStatus=="VM running" |project resourceGroup,name' -First 5000 -subscription $sub.id foreach ($vm in $vms) { $output = Invoke-AzVMRunCommand -ResourceGroupName $vm.resourceGroup -VMName $vm.name -ScriptPath ".\script.sh" -CommandId RunShellScript -Verbose $pattern = '^YOOHOO' # split data into lines, and process: $lines = $output.Value.Message.Split("`n") $result = $lines | Select-Object -Skip 2 | ForEach-Object { if ($_ -match $pattern) { $_ | ConvertFrom-Csv -Header dummy,dbname,path,version,running } } $result| Add-Member -MemberType NoteProperty "Server" -Value $vm.name $result| Add-Member -MemberType NoteProperty "sub" -Value $sub.name $result| Add-Member -MemberType NoteProperty "FLAG" -Value "ORA" $result| Add-Member -MemberType NoteProperty "RG" -Value $vm.resoureGroup $result| Add-Member -MemberType NoteProperty "joiner" -Value "$($vm.name.tolower())$($vm.resourceGroup.tolower())$($sub.Id.tolower())" $result |ft $script:FINRESULT +=$result } } $ARG = Search-AzGraph -Query '(Resources |where type == "microsoft.compute/virtualmachines" | project vmname=name,resourceGroup=toupper(resourceGroup),vmsize=properties.hardwareProfile.vmSize,sub=toupper(subscriptionId),os=properties.storageProfile.osDisk.osType,shut=tags.AutoShutdownSchedule,vmid=id,offer=properties.storageProfile.imageReference.offer,sku=properties.storageProfile.imageReference.sku | join kind=inner (ResourceContainers |where type=="microsoft.resources/subscriptions/resourcegroups" |project name=toupper(name),itera=tags.IteraplanID,own=tags.Owner,email=tags.Owner_email,sub2=toupper(subscriptionId) ) on $left.resourceGroup==$right.name and $left.sub==$right.sub2) | join kind=leftouter (ResourceContainers | where type=="microsoft.resources/subscriptions" | project SubName=name,sub=toupper(subscriptionId)) on $left.sub==$right.sub | project vmname=tolower(vmname),resourceGroup=tolower(resourceGroup),sub=tolower(sub),SubName,vmsize,os,itera,shut,own,email,vmid,offer,sku,joiner=strcat(vmname,resourceGroup,sub)' -First 5000 -subscription $Subscriptions.id $JOINRESULT = Join-Object -Left $script:FINRESULT -Right $ARG -LeftJoinProperty joiner -RightJoinProperty joiner -Type OnlyIfInBoth $MACHINES = get-azvmsize -location westeurope $FINALJOIN = Join-Object -Left $JOINRESULT -Right $MACHINES -LeftJoinProperty vmsize -RightJoinProperty Name -Type OnlyIfInBoth $FINALJOIN | Export-Csv -Path .\temporacle.csv -QuoteFields "dbname","shut","own","email" $Context = New-AzStorageContext -StorageAccountName "account here" -StorageAccountKey "key here" Set-AzStorageBlobContent -Context $Context -Container "azure" -File "temporacle.csv" -Blob "azureoracle-$date.csv" -Force
Comments
Post a Comment