Functions/Get-RSC/Get-RSCMSSQLInstances.ps1
|
################################################ # Function - Get-RSCMSSQLInstances - Getting all MSSQL instances visible to the RSC instance ################################################ Function Get-RSCMSSQLInstances { <# .SYNOPSIS A Rubrik Security Cloud (RSC) Reporting Module Function returning a list of all MSSQL hosts. .DESCRIPTION Makes the required GraphQL API calls to RSC via Invoke-RestMethod to get the data as described, then creates a usable array of the returned information, removing the need for the PowerShell user to understand GraphQL in order to interact with RSC. .LINK GraphQL schema reference: https://rubrikinc.github.io/rubrik-api-documentation/schema/reference .OUTPUTS Returns an array of all the available information on the GraphQL endpoint in a uniform and usable format. .EXAMPLE Get-RSCMSSQLInstances This example returns an array of all the information returned by the GraphQL endpoint for this object type. .NOTES Author: Joshua Stenhouse Date: 05/11/2023 #> ################################################ # Importing Module & Running Required Functions ################################################ # Importing the module is it needs other modules Import-Module RSCReporting # Checking connectivity, exiting function with error if not connected Test-RSCConnection ################################################ # Getting All Instances ################################################ # Creating array for objects $RSCHostList = @() # Building GraphQL query $RSCGraphQL = @{"operationName" = "PhysicalHosts"; "variables" = @{ "hostRoot" = "WINDOWS_HOST_ROOT" }; "query" = "query PhysicalHosts(`$hostRoot: HostRoot!) { physicalHosts(hostRoot: `$hostRoot) { edges { node { id name cluster { id name } descendantConnection { edges { node { ... on MssqlInstance { id objectType name slaAssignment effectiveSlaDomain { id name version } unprotectableReasons numWorkloadDescendants } } } } } } } }" } ################################################ # API Call To RSC GraphQL URI ################################################ # Querying API $RSCHostsResponse = Invoke-RestMethod -Method POST -Uri $RSCGraphqlURL -Body $($RSCGraphQL | ConvertTo-JSON -Depth 20) -Headers $RSCSessionHeader # Setting variable $RSCHostList += $RSCHostsResponse.data.PhysicalHosts.edges.node # Getting all results from paginations While ($RSCHostsResponse.data.PhysicalHosts.pageInfo.hasNextPage) { # Getting next set $RSCGraphQL.variables.after = $RSCHostsResponse.data.PhysicalHosts.pageInfo.endCursor $RSCHostsResponse = Invoke-RestMethod -Method POST -Uri $RSCGraphqlURL -Body $($RSCGraphQL | ConvertTo-JSON -Depth 20) -Headers $RSCSessionHeader $RSCHostList += $RSCHostsResponse.data.PhysicalHosts.edges.node } ################################################ # Processing All Hosts ################################################ # Creating array $RSCMSSQLInstances = [System.Collections.ArrayList]@() # Getting current time for last connected $UTCDateTime = [System.DateTime]::UtcNow # For Each Object Getting Data ForEach ($RSCHost in $RSCHostList) { # Setting variables $HostName = $RSCHost.name $HostID = $RSCHost.id $HostRubrikCluster = $RSCHost.cluster.name $HostRubrikClusterID = $RSCHost.cluster.id $HostInfo = $RSCHost.descendantConnection.edges.node $HostSQLInstances = $HostInfo | Where-Object {$_.ObjectType -eq "MssqlInstance"} # Adding each SQL instance ForEach($HostSQLInstance in $HostSQLInstances) { $MSSQLInstanceID = $HostSQLInstance.id $MSSQLInstanceType = $HostSQLInstance.objectType $MSSQLInstanceName = $HostSQLInstance.name $MSSQLInstanceSLAssignment = $HostSQLInstance.slaAssignment $MSSQLInstanceSLADomain = $HostSQLInstance.effectiveSlaDomain.name $MSSQLInstanceSLADomainID = $HostSQLInstance.effectiveSlaDomain.id $MSSQLInstanceDBCount = $HostSQLInstance.numWorkloadDescendants # Adding To Array $Object = New-Object PSObject $Object | Add-Member -MemberType NoteProperty -Name "RSCInstance" -Value $RSCInstance $Object | Add-Member -MemberType NoteProperty -Name "Instance" -Value $MSSQLInstanceName $Object | Add-Member -MemberType NoteProperty -Name "InstanceID" -Value $MSSQLInstanceID $Object | Add-Member -MemberType NoteProperty -Name "InstanceType" -Value $MSSQLInstanceType $Object | Add-Member -MemberType NoteProperty -Name "DBs" -Value $MSSQLInstanceDBCount $Object | Add-Member -MemberType NoteProperty -Name "SLAAssignment" -Value $MSSQLInstanceSLAssignment $Object | Add-Member -MemberType NoteProperty -Name "SLADomain" -Value $MSSQLInstanceSLADomain $Object | Add-Member -MemberType NoteProperty -Name "SLADomainID" -Value $MSSQLInstanceSLADomainID $Object | Add-Member -MemberType NoteProperty -Name "Host" -Value $HostName $Object | Add-Member -MemberType NoteProperty -Name "HostID" -Value $HostID $Object | Add-Member -MemberType NoteProperty -Name "RubrikCluster" -Value $HostRubrikCluster $Object | Add-Member -MemberType NoteProperty -Name "RubrikClusterID" -Value $HostRubrikClusterID # Adding $RSCMSSQLInstances.Add($Object) | Out-Null # End of for each sql instance on host below } # End of for each sql instance on host above # End of for each host below } # End of for each host above # Returning array Return $RSCMSSQLInstances # End of function } |