Saturday, 16 May 2020

[PowerShell] Using PowerShell Hash Tables for Instantaneous Search

I have always been a big fan of hash tables. Very powerful things.

I had a task where I needed to match the dates from some custom data to the Date Dimension from the NetApp OnCommand Insight Data Warehouse.

365 days of data x 3 (there were 3 rows in my custom data table for each date) = 1095 rows

Initially I had a where clause in my FOR loop of 1095 repeats like this:


$dateTK = ($DateDimension | Where{($_.dateYear -eq $dateYear) -and ($_.monthNum -eq $monthNum) -and ($_.dayInMonth -eq $dayInMonth) -and ($_.repDay -eq 1)}).tk


If you are curious, I had got Date Dimension from running -


select * from dwh_capacity.date_dimension;


- in MySQL Workbench and exporting as a CSV. Then importing the CSV into PowerShell as:


$DateDimension = Import-CSV date_dimension.csv


Date Dimension in this case was say 6 years or 2190 individual dates (I was only interested in days which explains the $_.repDay -eq 1 in the PowerShell above).

And my PowerShell program ran very slowly, which makes sense, as for each of my 1095 loops, it had got to search through another array of 2190 until it finds a match.

The solution to speed my program up was to create a DateTK hash table (Date TK was what I was looking for in Date Dimension) prior to running my 1095 loops.

This is what I did:


$DateTK_HT = @()
$DateDimension | Foreach{
  [String]$DY = $_.dateYear
  [String]$DM = $_.monthNum
  [String]$DD = $_.dayInMonth
  If(!($DateTK_HT.$DY)){$DateTK_HT.$DY = @{}}
  If(!($DateTK_HT.$DY.$DM)){$DateTK_HT.$DY.$DM = @{}}
  If($_.repDay -eq 1){
    [String]$DateTK_HT.$DY.$DM.$DD = $_.tk
  }
}


Getting DateTK from my hash table is instantaneous:


[String]$dateTK = $DateTK_HT.$dateYear.$monthNum.$dayInMonth


Instead of having say 2000 loops inside each of my 1095 loops, it went down to 1 simple request from the hash table per loop.

Image: Using PowerShell Hash Tables for Instantaneous Search

Sunday, 10 May 2020

[PowerShell] Delphix Reporting Get Report ‘Result Storage Summary’

I needed to get the ‘Result Storage Summary’ report from several Delphix Reporting instances. Not sure how many times I will need to do this, so to avoid repetition of tasks, always easier to write a little automation.

The following function takes in user inputs (as parameters or prompts), logs in to the Delphix Reporting Web Services API (more information here), downloads the report (as a JSON string), then outputs to screen as a table, and outputs to CSV.

I am sharing as a function here because it is super easy to just paste the entire function into your PowerShell window and then run the function within that PowerShell session, as many times as you like.

Image: Example: Running Get-DelphixReporting-ResultStorageSummary with prompts or parameters

The PowerShell Function

FUNCTION Get-DelphixReporting-ResultStorageSummary{
  PARAM(
    [Parameter(Mandatory=$TRUE)][String]$Username,
    [Parameter(Mandatory=$TRUE)][String]$Password,
    [Parameter(Mandatory=$TRUE)][String]$DelphixReportingHostname,
    [Parameter(Mandatory=$TRUE)][String]$ReportSavePath
  )
 
  ## VARIABLES SETUP ##
  [String]$Creds = "password=$Password&user=$Username"
  [String]$DelphixReportingAPI = "https://$DelphixReportingHostname/api/"
 
  ## LOGIN TO DELPHIX REPORTING WEB SERVICE API ##
  $CurlOut = [String](curl.exe --data $Creds ($DelphixReportingAPI + "login"))
  $CurlJson = $CurlOut | ConvertFrom-Json
  IF($CurlJson.Success -eq $TRUE){
    WRITE-HOST "Login Success!" -ForegroundColor GREEN
  }ELSE{
    RETURN "Login failure!"
  }
  [String]$Login = $CurlJson.LoginToken
  [String]$UsrId = $CurlJson.userId
 
  ## RECORD DATE ##
  [Int64]$DateTicks = (date).ticks
 
  ## GET THE 'RESULT STORAGE SUMMARY' REPORT AND OUTPUT IT ##
  $Report_Result_Storage_Summary_Json = [String](curl.exe -H "X-Login-Token: $Login" -H "X-User-Id: $UsrId" ($DelphixReportingAPI + "get_report?report=result_storage_summary"))
  $Report_Result_Storage_Summary = $Report_Result_Storage_Summary_Json | ConvertFrom-Json
  $Report_Result_Storage_Summary | FT # Outputs 'Result Storage Summary'
 
  ## FILE OUTPUT ##
  If(!$ReportSavePath.EndsWith("\")){$ReportSavePath += "\"}
  [String]$SavePath = ($ReportSavePath + $DelphixReportingHostname + "." + $DateTicks + ".csv")
  If(Test-Path $ReportSavePath){
    $Report_Result_Storage_Summary | Export-CSV -NoTypeInformation -Encoding UTF8 -Path $SavePath
  }ELSE{
    WRITE-HOST "Test-path $ReportSavePath failed!" -ForegroundColor RED
  }
 
  ## LOGOUT ##
  $CurlOut = [String](curl.exe -H "X-Login-Token: $Login" -H "X-User-Id: $UsrId" ($DelphixReportingAPI + "logout"))
}

Saturday, 9 May 2020

Application and Capacity Report - NetApp OCI DWH & IBM Cognos

An OCI DWH mini project/task to try.

Mini Project/Task:
Create an Application and Capacity report, to list all applications with their allocated and used data totals.

At first hand the task sounds a little daunting and you are unsure where to start, but it turns out to be quite an easy task as you will see below.

What is the first thing you do?

This is what I would do.

1) Search through ‘Team Content’ to see if there is already an application specific related report.
2) If there is nothing suitable, have a look on the Storage Automation Store > OnCommand Insight > Reports and see if there’s anything usable.
3) If there is, import the report from the ‘Storage Automation Store’ and modify it as per your requirements.

I did not find anything when I searched for content in my lab Cognos Analytics portal.
On the Storage Automation Store, I did find a report that looked very promising: the Application Capacity and Performance report. The report has more than I need, I just need to create an excel report with a list of Applications and their Capacity used. Essentially, if you look at the graphic below, all I need is the left bit of the bottom table.

Image: The Application Capacity and Performance report from Storage Automation Store

I download the report and following the instructions 'How To - Open a report from clipboard: This is an 'how-to-guide' to open a report from clipboard and to copy XML to clipboard in Cognos 11' (this content would be much better in a blog post somewhere ...) I “upload” the report to a test IBM Cognos Analytics instance.

The bit of the table I am interested in is in the graphic below.

Image: Cognos Analytics 11: Application v Capacity table on the report page

In the table, if you click on - #Application#, #Allocated (GB)#, #Used (GB)#, #Used (%)# - you can find out where the data is coming from.

Expression Definitions:
#Application# = [SQL5].[Application]
#Allocated (GB)# = [SQL5].[Allocated (GB)]
#Used (GB)# = [SQL5].[Used (GB)]
#Used (%)# = [SQL5].[Used (GB)] / [SQL5].[Allocated (GB)]

So, we need to look at the SQL5 query. Remember there will be a lot of stuff in that query that is not useful to us (the right side of the table includes performance data which I am not interested in.) And below is that SQL5 SQL query - all 126 lines of it! All we must do now is trim out the stuff we do not want (to be honest, we could just leave it in), and we have got what we wanted.

I have tried to make it clear with colour where each SELECT FROM starts and ends.
GREEN is the top level SELECT FROM.
CYAN is the next level SELECT FROM (nested inside GREEN)
YELLOW is the next level SELECT FROM (nested inside CYAN)
PINK is the final level SELECT FROM (nested inside YELLOW or CYAN - no further nesting’s inside.)

SELECT total.app AS 'Application',
 SUM(total.allocatedGB) AS 'Allocated (GB)',
 SUM(total.usedGB) AS 'Used (GB)',
 SUM(total.totalIops) AS 'Total Average IOPS',
 SUM(total.maxTotalIops) AS 'Peak Total IOPS',
 SUM(total.totalMbps) AS 'Total Average MB/s',
 SUM(total.maxTotalMbps) AS 'Peak Total MB/s',
 CEIL(AVG(total.avgMs)) AS 'Average Latency',
 CEIL(MAX(total.maxMs)) AS 'Peak Latency'
FROM (
 SELECT chargeback.application AS app,
  chargeback.provGB AS allocatedGB,
  chargeback.usedGB AS usedGB,
  performance.totalIops AS totalIops,
  performance.maxTotalIops AS maxTotalIops,
  performance.totalMbps AS totalMbps,
  performance.maxTotalMbps AS maxTotalMbps,
  performance.avgMs AS avgMs,
  performance.maxMs AS maxMs
 FROM (
  SELECT      vmChargeback.appTk,
   vmChargeback.application,
   SUM(vmChargeback.provGB) provGB,
   SUM(vmChargeback.usedGB) usedGB
  FROM (            
   SELECT      IFNULL(dwh_inventory.application.id, 'N/A') appTk,
    IFNULL(dwh_inventory.application.name, 'N/A') application,
    ROUND(SUM(dwh_inventory.hv_virtual_disk.capacityMB)/1024, 2) provGB,
    ROUND(SUM(dwh_inventory.hv_virtual_disk.usedCapacityMB)/1024, 2) usedGB
   FROM       dwh_inventory.hv_virtual_disk
   JOIN       dwh_inventory.hv_virtual_machine_to_disk
   ON dwh_inventory.hv_virtual_disk.id = dwh_inventory.hv_virtual_machine_to_disk.virtualDiskId
   JOIN       dwh_inventory.hv_virtual_machine
   ON dwh_inventory.hv_virtual_machine_to_disk.virtualMachineId = dwh_inventory.hv_virtual_machine.id
   LEFT JOIN   dwh_inventory.vm_to_application
   ON dwh_inventory.hv_virtual_machine.id = dwh_inventory.vm_to_application.vmId
   LEFT JOIN   dwh_inventory.application
   ON dwh_inventory.vm_to_application.applicationId = dwh_inventory.application.id
   GROUP BY
    dwh_inventory.application.id
   UNION
   SELECT      dwh_capacity.application_dimension.id appTk,
    dwh_capacity.application_dimension.name application,
    ROUND(SUM(dwh_capacity.chargeback_fact.provisionedCapacityMB)/1024, 2) provGB,
    ROUND(SUM(dwh_capacity.chargeback_fact.usedCapacityMB)/1024, 2) usedGB
   FROM       dwh_capacity.chargeback_fact
   JOIN       dwh_capacity.application_dimension
   ON dwh_capacity.chargeback_fact.applicationTk = dwh_capacity.application_dimension.tk
   AND dwh_capacity.chargeback_fact.mappedByVM = 0
   JOIN       dwh_capacity.date_dimension
   ON dwh_capacity.chargeback_fact.dateTk = dwh_capacity.date_dimension.tk
   AND dwh_capacity.date_dimension.latest = 1
   GROUP BY
    dwh_capacity.application_dimension.id
   ) vmChargeback
  GROUP BY
       vmChargeback.appTk
  ) chargeback
 LEFT JOIN     ( ## Performance Section START
  SELECT      perf.appTk,
   CEIL(AVG(perf.totalIops)) AS totalIops,
   MAX(perf.totalIops) AS maxTotalIops,
   CEIL(AVG(perf.totalMbps)) AS totalMbps,
   MAX(perf.totalMbps) AS maxTotalMbps,
   ROUND(AVG(perf.avgMs), 2) AS avgMs,
   MAX(perf.maxMs) AS maxMs
  FROM (
   SELECT      dwh_inventory.application.id appTk,
    dwh_performance.time_dimension.hourDateTime,
    ROUND(SUM(dwh_performance.vm_hourly_performance_fact.totalIops),0) totalIops,
    ROUND(SUM(dwh_performance.vm_hourly_performance_fact.totalThroughput),0) totalMbps,
    ROUND(AVG(dwh_performance.vm_hourly_performance_fact.totalResponseTime),0) avgMs,
    ROUND(MAX(dwh_performance.vm_hourly_performance_fact.totalResponseTime),0) maxMs
   FROM       dwh_performance.vm_hourly_performance_fact
   JOIN       dwh_performance.time_dimension
   ON dwh_performance.vm_hourly_performance_fact.timeTk = dwh_performance.time_dimension.tk
   JOIN       dwh_performance.vm_dimension
   ON dwh_performance.vm_hourly_performance_fact.vmTk = dwh_performance.vm_dimension.tk
   AND dwh_performance.vm_dimension.latest = 1
   JOIN       dwh_inventory.vm_to_application
   ON dwh_performance.vm_dimension.id = dwh_inventory.vm_to_application.vmId
   JOIN       dwh_inventory.application
   ON dwh_inventory.vm_to_application.applicationId = dwh_inventory.application.id
   GROUP BY
    dwh_inventory.application.id,
    dwh_performance.time_dimension.tk
   UNION
   SELECT      dwh_performance.application_dimension.id appTk,
    dwh_performance.time_dimension.hourDateTime,
    ROUND(SUM(dwh_performance.volume_hourly_performance_fact.totalIops),0) totalIops,
    ROUND(SUM(dwh_performance.volume_hourly_performance_fact.totalThroughput),0) totalMbps,
    ROUND(AVG(dwh_performance.volume_hourly_performance_fact.totalResponseTime),0) avgMs,
    ROUND(MAX(dwh_performance.volume_hourly_performance_fact.totalResponseTime),0) maxMs
   FROM       dwh_performance.volume_hourly_performance_fact
   JOIN       dwh_performance.time_dimension
   ON dwh_performance.volume_hourly_performance_fact.timeTk = dwh_performance.time_dimension.tk
   JOIN       dwh_performance.application_dimension
   ON dwh_performance.volume_hourly_performance_fact.applicationTk = dwh_performance.application_dimension.tk
   GROUP BY
    dwh_performance.application_dimension.id,
    dwh_performance.time_dimension.tk
   UNION
   SELECT      dwh_performance.application_dimension.id appTk,
    dwh_performance.time_dimension.hourDateTime,
    ROUND(SUM(dwh_performance.internal_volume_hourly_performance_fact.totalIops),0) totalIops,
    ROUND(SUM(dwh_performance.internal_volume_hourly_performance_fact.totalThroughput),0) totalMbps,
    ROUND(AVG(dwh_performance.internal_volume_hourly_performance_fact.totalResponseTime),0) avgMs,
    ROUND(MAX(dwh_performance.internal_volume_hourly_performance_fact.totalResponseTime),0) maxMs
   FROM       dwh_performance.internal_volume_hourly_performance_fact
   JOIN       dwh_performance.time_dimension
   ON dwh_performance.internal_volume_hourly_performance_fact.timeTk = dwh_performance.time_dimension.tk
   JOIN       dwh_performance.application_dimension
   ON dwh_performance.internal_volume_hourly_performance_fact.applicationTk = dwh_performance.application_dimension.tk
   GROUP BY
    dwh_performance.application_dimension.id,
    dwh_performance.time_dimension.tk      
   ) perf
  GROUP BY
   perf.appTk
  ) performance ## Performance Section END
 ON chargeback.appTk = performance.appTk
 GROUP BY
  chargeback.appTk
 ) total
GROUP BY
 total.app

Since we do not need any of the performance data, we can get rid of the entire performance section, and any lines with performance in them. Which makes the query I was looking for 51 lines instead of 126. As below:

SELECT total.app AS 'Application',
 SUM(total.allocatedGB) AS 'Allocated (GB)',
 SUM(total.usedGB) AS 'Used (GB)'
FROM (
 SELECT chargeback.application AS app,
  chargeback.provGB AS allocatedGB,
  chargeback.usedGB AS usedGB
 FROM (
  SELECT      vmChargeback.appTk,
   vmChargeback.application,
   SUM(vmChargeback.provGB) provGB,
   SUM(vmChargeback.usedGB) usedGB
  FROM (            
   SELECT      IFNULL(dwh_inventory.application.id, 'N/A') appTk,
    IFNULL(dwh_inventory.application.name, 'N/A') application,
    ROUND(SUM(dwh_inventory.hv_virtual_disk.capacityMB)/1024, 2) provGB,
    ROUND(SUM(dwh_inventory.hv_virtual_disk.usedCapacityMB)/1024, 2) usedGB
   FROM       dwh_inventory.hv_virtual_disk
   JOIN       dwh_inventory.hv_virtual_machine_to_disk
   ON dwh_inventory.hv_virtual_disk.id = dwh_inventory.hv_virtual_machine_to_disk.virtualDiskId
   JOIN       dwh_inventory.hv_virtual_machine
   ON dwh_inventory.hv_virtual_machine_to_disk.virtualMachineId = dwh_inventory.hv_virtual_machine.id
   LEFT JOIN   dwh_inventory.vm_to_application
   ON dwh_inventory.hv_virtual_machine.id = dwh_inventory.vm_to_application.vmId
   LEFT JOIN   dwh_inventory.application
   ON dwh_inventory.vm_to_application.applicationId = dwh_inventory.application.id
   GROUP BY
    dwh_inventory.application.id
   UNION
   SELECT      dwh_capacity.application_dimension.id appTk,
    dwh_capacity.application_dimension.name application,
    ROUND(SUM(dwh_capacity.chargeback_fact.provisionedCapacityMB)/1024, 2) provGB,
    ROUND(SUM(dwh_capacity.chargeback_fact.usedCapacityMB)/1024, 2) usedGB
   FROM       dwh_capacity.chargeback_fact
   JOIN       dwh_capacity.application_dimension
   ON dwh_capacity.chargeback_fact.applicationTk = dwh_capacity.application_dimension.tk
   AND dwh_capacity.chargeback_fact.mappedByVM = 0
   JOIN       dwh_capacity.date_dimension
   ON dwh_capacity.chargeback_fact.dateTk = dwh_capacity.date_dimension.tk
   AND dwh_capacity.date_dimension.latest = 1
   GROUP BY
    dwh_capacity.application_dimension.id
   ) vmChargeback
  GROUP BY
       vmChargeback.appTk
  ) chargeback
 GROUP BY
  chargeback.appTk
 ) total
GROUP BY
 total.app

And ‘Miracle of Miracles’, the SQL query works perfectly on my LAB DWH. The below image is using MySQL command line.

Image: OCI LAB DWH output of Application with Allocated (GB) and Used (GB)

All that is left to do is the usual IBM Cognos activities to turn it into a report in IBM Cognos.

THE END