This is THE solution.
Sorry, without comments but it is simle enough:
cls
function Convert-FromUnixDate ($UnixDate)
{
[timezone]::CurrentTimeZone.ToLocalTime(([datetime]'1/1/1970').AddMilliseconds($UnixDate))
}
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;
}
}
"@
$AllProtocols = [System.Net.SecurityProtocolType]'Tls12'
[System.Net.ServicePointManager]::SecurityProtocol = $AllProtocols
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy
Import-Module SQLPS -DisableNameChecking
$DatabaseName = 'dbname'
$DatabaseUserName = 'username'
$DatabasePassword = 'password'
$DatabaseServerInstance = 'server.domain.org\dom,1436'
$username="monitoring"
$pass = "monitoring"
$password = ConvertTo-SecureString -AsPlainText $pass -Force
$bytes = [System.Text.Encoding]::UTF8.GetBytes("$username`:$pass")
$encodedCredentials = [System.Convert]::ToBase64String($bytes)
$headers = @{
"Authorization" = "Basic " + $encodedCredentials
"Accept" = "application/vnd.netapp.object.inventory.hal+json"
}
$events = Invoke-RestMethod -Headers $headers -Uri https://ocum.domain.org/rest/events?limit=9999999
$query = 'DELETE FROM [dbname].[dbo].[NetApp]'
Invoke-Sqlcmd -Database $DatabaseName -Username $DatabaseUserName -Password $DatabasePassword -Query $Query -ServerInstance $DatabaseServerInstance
foreach ($event in $events._embedded."netapp:eventDtoList")
{
Write-Host $event.name - $event.conditionMessage
$query = "INSERT INTO [dbname].[dbo].[NetApp]
(
objectId,
name,
conditionMessage,
timestamp,
severity,
impactArea,
impactLevel,
sourceFullName,
state,
resolvedTimestamp,
resolvedBy,
acknowledgedTimestamp,
acknowledgedBy,
sourceResourceType,
daysOutstanding
)
values
(
'$($event.objectId)',
'$($event.name)',
'$($event.conditionMessage -replace "'", """)',
'$(Convert-FromUnixDate ($event.timestamp))',
'$($event.severity)',
'$($event.impactArea)',
'$($event.impactLevel)',
'$($event.sourceFullName)',
'$($event.state)',
'$(Convert-FromUnixDate ($event.resolvedTimestamp))',
'$($event.resolvedBy)',
'$(Convert-FromUnixDate ($event.acknowledgedTimestamp))',
'$($event.acknowledgedBy)',
'$($event.sourceResourceType)',
'$($event.daysOutstanding)'
)
"
#Write-Host $query
Write-Host
Invoke-Sqlcmd -Database $DatabaseName -Username $DatabaseUserName -Password $DatabasePassword -Query $Query -ServerInstance $DatabaseServerInstance
}
$username = 'user'
$pass = 'password'
$password = ConvertTo-SecureString ($pass) -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential ($username, $password)
Connect-NcController -Credential $cred -name 172.17.114.150
$volumes = Get-NcVol
foreach ($volume in $volumes)
{
$lastSnapshotDate = Get-NcSnapshot -Volume $volume | Sort-Object Created | Select-Object Created, Name -Last 1
$dateNow = (Get-Date).AddDays(0)
$timeSpan = 1
if ($volume.Name -eq 'data_CIFS_volume' -or $volume.Name -eq 'nvrvideo_CIFS_volume') { $timeSpan = 7 }
if ((New-TimeSpan -Start $lastSnapshotDate.Created -End $dateNow).Days -gt $timeSpan)
{
$lastSnaphotErrorString = "The last snapshot of the volume `"$volume`" was created at $(($lastSnapshotDate.Created).ToString(`"dd/MM/yyyy,`")) $((New-TimeSpan -Start $lastSnapshotDate.Created -End $dateNow).Days) days ago"
Write-host $lastSnaphotErrorString
$query = "INSERT INTO [dbname].[dbo].[NetApp]
(
objectId,
name,
conditionMessage,
timestamp,
severity,
impactArea,
impactLevel,
sourceFullName,
state,
resolvedTimestamp,
resolvedBy,
acknowledgedTimestamp,
acknowledgedBy,
sourceResourceType,
daysOutstanding
)
values
(
'-1',
'Last snapshot',
'$lastSnaphotErrorString',
null,
'error',
null,
null,
'$volume',
'NEW',
null,
null,
null,
null,
null,
null
)
"
Invoke-Sqlcmd -Database $DatabaseName -Username $DatabaseUserName -Password $DatabasePassword -Query $Query -ServerInstance $DatabaseServerInstance
}
}
<#
SELECT objectId , 'NetApp ' + sourceFullName + ': ' + name + ': ' + conditionMessage as message FROM [dbname].[dbo].[NetApp] where state = 'NEW' and severity <> 'information'
----------------------------------------------------------------------------------------------------------
USE [dbname]
GO
/****** Object: Table [dbo].[NetApp] Script Date: 3/26/2019 12:22:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NetApp](
[objectId] [int] NULL,
[name] [nvarchar](max) NULL,
[conditionMessage] [nvarchar](max) NULL,
[timestamp] [datetime] NULL,
[severity] [nchar](15) NULL,
[impactArea] [nchar](15) NULL,
[impactLevel] [nchar](10) NULL,
[sourceFullName] [nvarchar](max) NULL,
[state] [nvarchar](50) NULL,
[resolvedTimestamp] [datetime] NULL,
[resolvedBy] [nvarchar](50) NULL,
[acknowledgedTimestamp] [datetime] NULL,
[acknowledgedBy] [nvarchar](50) NULL,
[sourceResourceType] [nvarchar](50) NULL,
[daysOutstanding] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
----------------------------------------------------------------------------------------------------------
#>