How to create custom reports in CSV using PowerShell and Rest API?
Overview
The following procedures describe how you can create custom reports in CSV format using the Invoke-RestMethod of PowerShell.
Procedure
To refer the list of APIs, see List of Report APIs section.
- Open PowerShell as an administrator and run the following command to execute digitally unsigned custom scrips:
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
- Obtain the authentication information from the Druva Admin console. For more information, see Using Report APIs.
- Create the PowerShell script and save it as a .ps1 file.
- Use one of the following scripts based on the number of users in your environment:
- Use this script of user's API in inSync environment having less than 8500 users. In the following example:
- Replace the <username> with the user name of the administrator who generated the access token.
- Replace the <Access-Token> with the token generated in the step 2.
- Replace https://”<On-prem-Master-FQDN-OR-IP:port>” with the hhtps:// and inSync Master IP/FQDN and port number. Ignore port number if the inSync master is using port 443
- For inSync cloud replace https://”<On-prem-Master-FQDN-OR-IP:port>” with https://cloud.druva.com
- Set the path to the csv file to the location you want.
- Select desired attributes next to select-object variable in the script.
Example script of user API:
add-type @"
using System.Net;
using System.Security.Cryptography.X509Certificates;
public class TrustAllCertsPolicy : ICertificatePolicy {
public bool CheckValidationResult(
ServicePoint srvPoint, X509Certificate certificate,
WebRequest request, int certificateProblem) {
return true;
}
}
"@
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$secpasswd = ConvertTo-SecureString "<Access-Token>" -AsPlainText -Force
$Cred = New-Object System.Management.Automation.PSCredential ('<username>’, $secpasswd)
$user = Invoke-RestMethod -Method 'Get' -uri "https://<On-prem-Master-FQDN-OR-IP:port>/api/reports/v1/users" -Credential $Cred
$user.data | select-object ‘user_name’,’email_id’,’profile’,’storage’,’added_on’,’user_status’,’total_usage’,’allocated_quota’ | Export-Csv -path users.csv -NoTypeInformation
- Use this script of user's API in inSync environment having greater than 8500 users. In the following example:
- Replace the <username> with the user name of the administrator who generated the access token.
- Replace the <Access-Token> with the token generated in the step 2.
- Replace https://”<On-prem-Master-FQDN-OR-IP:port>” with the hhtps:// and inSync Master IP/FQDN and port number. Ignore port number if the inSync master is using port 443
- For inSync cloud replace https://”<On-prem-Master-FQDN-OR-IP:port>” with https://cloud.druva.com
- Under the try and catch method, update the “select-object ‘xxx’,’xxx’ | Export-Csv -path xxx.csv” with the same exact information.
- Select desired attributes next to select-object variable in the script.
Example script of user API
add-type @"
using System.Net;
using System.Security.Cryptography.X509Certificates;
public class TrustAllCertsPolicy : ICertificatePolicy {
public bool CheckValidationResult(
ServicePoint srvPoint, X509Certificate certificate,
WebRequest request, int certificateProblem) {
return true;
}
}
"@
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
function ParseItem($jsonItem)
{
if($jsonItem.PSObject.TypeNames -match 'Array')
{
return ParseJsonArray($jsonItem)
}
elseif($jsonItem.PSObject.TypeNames -match 'Dictionary')
{
return ParseJsonObject([HashTable]$jsonItem)
}
else
{
return $jsonItem
}
}
function ParseJsonObject($jsonObj)
{
$result = New-Object -TypeName PSCustomObject
foreach ($key in $jsonObj.Keys)
{
$item = $jsonObj[$key]
if ($item)
{
$parsedItem = ParseItem $item
}
else
{
$parsedItem = $null
}
$result | Add-Member -MemberType NoteProperty -Name $key -Value $parsedItem
}
return $result
}
function ParseJsonArray($jsonArray)
{
$result = @()
$jsonArray | ForEach-Object -Process {
$result += , (ParseItem $_)
}
return $result
}
function ParseJsonString($json)
{
$config = $javaScriptSerializer.DeserializeObject($json)
return ParseJsonObject($config)
}
$secpasswd = ConvertTo-SecureString "<Access-Token>" -AsPlainText -Force
$Cred = New-Object System.Management.Automation.PSCredential ('<username>’, $secpasswd)
$user = Invoke-RestMethod -Method 'Get' -uri "https://<On-prem-Master-FQDN-OR-IP:port>/api/reports/v1/users" -Credential $Cred
try
{
$result = ParseItem ((New-Object -TypeName System.Web.Script.Serialization.JavaScriptSerializer -Property @{MaxJsonLength=67108864}).DeserializeObject($user))
$result.data | select-object ‘user_name’,’email_id’,’profile’,’storage’,’added_on’,’user_status’,’total_usage’,’allocated_quota’ | Export-Csv -path users.csv -NoTypeInformation
}
catch
{
$user.data | select-object ‘user_name’,’email_id’,’profile’,’storage’,’added_on’,’user_status’,’total_usage’,’allocated_quota’ | Export-Csv -path users.csv –NoTypeInformation
}
- Use this script of user's API in inSync environment having less than 8500 users. In the following example:
- Open the PowerShell and run the PowerShell script file created.
- You will get the output in the CSV file at the location mentioned in the script.