What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general.

Learn more

PRTG Network Monitor

Intuitive to Use. Easy to manage.
More than 500,000 users rely on Paessler PRTG every day. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free Download

Top Tags


View all Tags

PRTG API to Power BI - Historic Data

Votes:

0

Good Morning,

Sorry if this has been asked before but I have been unable to find a relevant answer. I am using PowerBI to query the PRTG API. This has been simple in terms of getting all sensors an account can see, but less obvious for getting bulk historic data.

What I want is to be able to grab historic data for all sensors relevant to the account, and be able to identify which sensor that data is associated with. Currently I only end up with a lot of data with information on which sensor it belongs to.

Is there a property that can be added to the historic data API call to return the object ID or sensor name?

This is what I have done so far: -

  • Grab all the sensors for the account created with
  • /api/table.json?content=sensors&columns=objid,group,device,sensor,status,message,lastvalue
  • Navigate and Expand the list to get the record data.
  • Convert this to a table.
  • Expand the json values into columns.
  • Add a new column combining the URL and ID together to create an full URL for historic data.
  • /api/historicdata.json?id=" & Text.From([objid]) & "&avg=0&sdate=2018-11-02-00-00-00&edate=2018-11-02-23-59-59&usecaption=1)
  • Add a custom column with the contents of the URL
  • Convert this to a table
  • Create a new column based on the JSON output from the URL
  • data = List.Transform(#"Renamed Columns"[objecturl], each Json.Document(Web.Contents(_))),
  • Convert this to a table
  • Expand this column a few times to get the histdata as seperate columns.

Unfortunately I only see datatime, loading time, bytes received, download bandwidth, time to first byte and coverage - with no way to relate these back to the original object ID.

api powerbi prtg

Created on Nov 5, 2018 9:58:19 AM



Best Answer

Accepted Answer

Votes:

5

Hi Chris,

What works for me is to set up a daily scheduled task on my PRTG server to run a powershell script. This script exports the historic data that I need from PRTG, sorts through it, formats it and exports it as a .csv. I then can pick up the csv file with Power BI and use the data to create a dashboard.

I have posted my code below. Let me know if this helps or if you have any further questions. PRTGAPI by lordmilko is a must have if you're working with the API and powershell. https://github.com/lordmilko/PrtgAPI

#########################################
#                                       #
#       Uptime Report version 5.2       #
# -Daily Report on Routers and Switches #
#          -5am until Midnight          #
#                                       #
#########################################

#region Functions

function Request-PRTGSensorHistoricData
{
    Param (
        [Parameter(Position=0,Mandatory=$True)]
        [ValidateNotNullorEmpty()]
        [int]$ID,
		[int]$days,
		[int]$datemodifier
    )

    try
    {
        $edate = (Get-Date).AddDays(-$datemodifier+1).ToString("yyyy-MM-dd")
        $sdate = (Get-Date).AddDays(-$datemodifier).ToString("yyyy-MM-dd")
        $url = "https://$PRTGHost/api/historicdata.csv?id=$ID&avg=3600&sdate=$sdate-05-00-00&edate=$edate-00-00-00&$auth"
        $request = Invoke-WebRequest -Uri $url -MaximumRedirection 0 -ErrorAction Ignore
        convertFrom-csv ($request)
    }
    catch
    {
    
    }
}
function Get-PRTGSensor
{
    [cmdletbinding(
        DefaultParameterSetName='ID'
    )]
    Param(
        [Parameter(
            ParameterSetName='ID',
            Mandatory = $true,
            Position = 0
        )]
        [int]
        $ID,
        [Parameter(
            ParameterSetName='Name',
            Mandatory = $true,
            Position = 0
        )]
        [String]
        $Name,
        [Parameter(
            ParameterSetName='Device',
            Mandatory = $true,
            Position = 0
        )]
        [int]
        $DeviceID,
        [Parameter(
            ParameterSetName='Group',
            Mandatory = $true,
            Position = 0
        )]
        [int]
        $GroupID,
        [Parameter(
            ParameterSetName='Tag',
            Mandatory = $true,
            Position = 0
        )]
        [string]
        $Tag
    )

    switch($PSCmdlet.ParameterSetName)
    {
        “ID”  
        {
            $filter = "&filter_objid=$ID"
        }
        “Name”
        {
            $filter = "&filter_name=@sub($Name)"
        }
        “Device”
        {
            $filter = "&id=$DeviceID"
        }
        “Group”
        {
            $filter = "&id=$GroupID"
        }
        “Tag”
        {
            $filter = "&filter_Tags=@sub($Tag)"
        }
    }

    $Sensors = @()
    Try
    {
        $url = "https://$PRTGHost/api/table.xml?content=sensors&output=csvtable&columns=probe,group,favorite,lastvalue,device,downtime,downtimetime,downtimesince,uptime,uptimetime,uptimesince,knowntime,cumsince,lastcheck,lastup,lastdown,minigraph,schedule,basetype,baselink,parentid,notifiesx,interval,access,dependency,position,status,comments,priority,message,type,tags,active,objid,name"+$filter+"&count=*&$auth"
        $request = Invoke-WebRequest -Uri $url -MaximumRedirection 0 -ErrorAction Ignore
        $Sensors += convertFrom-csv ($request) -WarningAction SilentlyContinue
    }
    Catch
    {
    
    }

    $return = @()
    foreach($sensor in $Sensors)
    {
        $return += [PSCustomObject]@{
            "ID" = $sensor.ID
			"Name" = $sensor.object
            "Device" = $sensor.Device
            "Group" = $sensor.Group
            "Probe" = $sensor.Probe
            "Tags" = $sensor.Tags
            "Status" = $sensor.Status
            "Parent ID" = $sensor."Parent ID"
            "Active" = $sensor."Active/Paused"
        }
    }
    $return | sort Group
}
function Login-PRTGTaskScheduler
{
    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

    $global:auth = "username=USERNAME&passhash=PASSHASH"
    $global:PRTGHost = "PRTGHOST"
    
    Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope CurrentUser -Force
    
    $cred = New-Credential -UserName USERNAME -Password "PASSHASH"
    Connect-PrtgServer -Credential $cred -Server $PRTGHost
}

#endregion 

#region Import-Module and Login to PrtgServer

Import-Module PRTGAPI
Login-PRTGTaskScheduler
cls

#endregion

#region Gather Sensors

Write-Host -ForegroundColor Yellow "Starting Daily Uptime Report..."
Write-Host "Getting Sensors..."
$Sensors = @(Get-PRTGSensor -GroupID 26292 | where {$_.tags -match "Pingsensor"})
$Sensors = $Sensors | sort group

#endregion

#region Generate Report

$routerswitchUptimeReport = @()

foreach($s in ($Sensors | where {$_.tags -match "switch" -or $_.tags -match "gateway"}))
{
	Write-Host -ForegroundColor Yellow "Working on $($S.Device)..."
	$group = $s.Group
	if($group -match "Routers")
	{
		$group = $group.Substring(0,$group.Length - 8)
	}
	if($group -match "Switches")
	{
		$group = $group.Substring(0,$group.Length - 9)
	}
	switch -Wildcard ($s.Tags)
	{
		'*gateway*'{ $type = "Gateway" }
		'*switch*'{ $type = "Switch" }
	}

    $i = 0
    do
    {
        $i++

        Write-Host "Obtaining Report..."
	    $reportcsv = Request-PRTGSensorHistoricData -ID $s.id -Days 1 -datemodifier $i
	    $dtresults = $($reportcsv | select "Downtime(RAW)")
	    $dt = 0
	    foreach($r in $dtresults)
	    {
		    $dt += $r.'Downtime(RAW)'
	    }
	    $Uptime = [math]::Round(100 - ($dt / ($dtresults.Count - 1)),2)
        $report = [PSCustomObject]@{
		    "Site" = $group
		    "Category" = $type
		    "Device" = $s.Device
		    "Uptime" = $Uptime
            "Date" = $((Get-Date).AddDays(-$i).ToString("dd-MM-yyyy"))
	    }
        $routerswitchUptimeReport += $report
        $report
    }
    until($i -eq 7)
}

#endregion

#region Save Report
$savelocation = "D:\PRTG Data\Reports\RouterSwitchUptime\7day Report\RouterSwitchUptimeReport.csv"

Write-Host -ForegroundColor Yellow "Saving Report to File..."
$routerswitchUptimeReport | Export-Csv -Path $savelocation -NoTypeInformation
Write-Host "Saved..."
#endregion

Created on Feb 15, 2019 10:47:46 AM



12 Replies

Votes:

0

Hello Chris,

Thank you very much for your question.

I don't see an approach to achieve this via the API. The possibilities for the historic data export via PRTG's API can be found under 'Setup | PRTG API | Historic Data' (corresponding manual article).

You might also check if Historic Data Reports might help to query the data as required. Additionally, please see this kb thread, as it also refers to Power BI even though it does not really provide you with the solution.

Please excuse that I am not able to provide you with a more satisfactory answer here.

Best regards,
Sebastian

Created on Nov 6, 2018 1:10:12 PM by Sebastian Kniege [Paessler Support]



Accepted Answer

Votes:

5

Hi Chris,

What works for me is to set up a daily scheduled task on my PRTG server to run a powershell script. This script exports the historic data that I need from PRTG, sorts through it, formats it and exports it as a .csv. I then can pick up the csv file with Power BI and use the data to create a dashboard.

I have posted my code below. Let me know if this helps or if you have any further questions. PRTGAPI by lordmilko is a must have if you're working with the API and powershell. https://github.com/lordmilko/PrtgAPI

#########################################
#                                       #
#       Uptime Report version 5.2       #
# -Daily Report on Routers and Switches #
#          -5am until Midnight          #
#                                       #
#########################################

#region Functions

function Request-PRTGSensorHistoricData
{
    Param (
        [Parameter(Position=0,Mandatory=$True)]
        [ValidateNotNullorEmpty()]
        [int]$ID,
		[int]$days,
		[int]$datemodifier
    )

    try
    {
        $edate = (Get-Date).AddDays(-$datemodifier+1).ToString("yyyy-MM-dd")
        $sdate = (Get-Date).AddDays(-$datemodifier).ToString("yyyy-MM-dd")
        $url = "https://$PRTGHost/api/historicdata.csv?id=$ID&avg=3600&sdate=$sdate-05-00-00&edate=$edate-00-00-00&$auth"
        $request = Invoke-WebRequest -Uri $url -MaximumRedirection 0 -ErrorAction Ignore
        convertFrom-csv ($request)
    }
    catch
    {
    
    }
}
function Get-PRTGSensor
{
    [cmdletbinding(
        DefaultParameterSetName='ID'
    )]
    Param(
        [Parameter(
            ParameterSetName='ID',
            Mandatory = $true,
            Position = 0
        )]
        [int]
        $ID,
        [Parameter(
            ParameterSetName='Name',
            Mandatory = $true,
            Position = 0
        )]
        [String]
        $Name,
        [Parameter(
            ParameterSetName='Device',
            Mandatory = $true,
            Position = 0
        )]
        [int]
        $DeviceID,
        [Parameter(
            ParameterSetName='Group',
            Mandatory = $true,
            Position = 0
        )]
        [int]
        $GroupID,
        [Parameter(
            ParameterSetName='Tag',
            Mandatory = $true,
            Position = 0
        )]
        [string]
        $Tag
    )

    switch($PSCmdlet.ParameterSetName)
    {
        “ID”  
        {
            $filter = "&filter_objid=$ID"
        }
        “Name”
        {
            $filter = "&filter_name=@sub($Name)"
        }
        “Device”
        {
            $filter = "&id=$DeviceID"
        }
        “Group”
        {
            $filter = "&id=$GroupID"
        }
        “Tag”
        {
            $filter = "&filter_Tags=@sub($Tag)"
        }
    }

    $Sensors = @()
    Try
    {
        $url = "https://$PRTGHost/api/table.xml?content=sensors&output=csvtable&columns=probe,group,favorite,lastvalue,device,downtime,downtimetime,downtimesince,uptime,uptimetime,uptimesince,knowntime,cumsince,lastcheck,lastup,lastdown,minigraph,schedule,basetype,baselink,parentid,notifiesx,interval,access,dependency,position,status,comments,priority,message,type,tags,active,objid,name"+$filter+"&count=*&$auth"
        $request = Invoke-WebRequest -Uri $url -MaximumRedirection 0 -ErrorAction Ignore
        $Sensors += convertFrom-csv ($request) -WarningAction SilentlyContinue
    }
    Catch
    {
    
    }

    $return = @()
    foreach($sensor in $Sensors)
    {
        $return += [PSCustomObject]@{
            "ID" = $sensor.ID
			"Name" = $sensor.object
            "Device" = $sensor.Device
            "Group" = $sensor.Group
            "Probe" = $sensor.Probe
            "Tags" = $sensor.Tags
            "Status" = $sensor.Status
            "Parent ID" = $sensor."Parent ID"
            "Active" = $sensor."Active/Paused"
        }
    }
    $return | sort Group
}
function Login-PRTGTaskScheduler
{
    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

    $global:auth = "username=USERNAME&passhash=PASSHASH"
    $global:PRTGHost = "PRTGHOST"
    
    Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope CurrentUser -Force
    
    $cred = New-Credential -UserName USERNAME -Password "PASSHASH"
    Connect-PrtgServer -Credential $cred -Server $PRTGHost
}

#endregion 

#region Import-Module and Login to PrtgServer

Import-Module PRTGAPI
Login-PRTGTaskScheduler
cls

#endregion

#region Gather Sensors

Write-Host -ForegroundColor Yellow "Starting Daily Uptime Report..."
Write-Host "Getting Sensors..."
$Sensors = @(Get-PRTGSensor -GroupID 26292 | where {$_.tags -match "Pingsensor"})
$Sensors = $Sensors | sort group

#endregion

#region Generate Report

$routerswitchUptimeReport = @()

foreach($s in ($Sensors | where {$_.tags -match "switch" -or $_.tags -match "gateway"}))
{
	Write-Host -ForegroundColor Yellow "Working on $($S.Device)..."
	$group = $s.Group
	if($group -match "Routers")
	{
		$group = $group.Substring(0,$group.Length - 8)
	}
	if($group -match "Switches")
	{
		$group = $group.Substring(0,$group.Length - 9)
	}
	switch -Wildcard ($s.Tags)
	{
		'*gateway*'{ $type = "Gateway" }
		'*switch*'{ $type = "Switch" }
	}

    $i = 0
    do
    {
        $i++

        Write-Host "Obtaining Report..."
	    $reportcsv = Request-PRTGSensorHistoricData -ID $s.id -Days 1 -datemodifier $i
	    $dtresults = $($reportcsv | select "Downtime(RAW)")
	    $dt = 0
	    foreach($r in $dtresults)
	    {
		    $dt += $r.'Downtime(RAW)'
	    }
	    $Uptime = [math]::Round(100 - ($dt / ($dtresults.Count - 1)),2)
        $report = [PSCustomObject]@{
		    "Site" = $group
		    "Category" = $type
		    "Device" = $s.Device
		    "Uptime" = $Uptime
            "Date" = $((Get-Date).AddDays(-$i).ToString("dd-MM-yyyy"))
	    }
        $routerswitchUptimeReport += $report
        $report
    }
    until($i -eq 7)
}

#endregion

#region Save Report
$savelocation = "D:\PRTG Data\Reports\RouterSwitchUptime\7day Report\RouterSwitchUptimeReport.csv"

Write-Host -ForegroundColor Yellow "Saving Report to File..."
$routerswitchUptimeReport | Export-Csv -Path $savelocation -NoTypeInformation
Write-Host "Saved..."
#endregion

Created on Feb 15, 2019 10:47:46 AM



Votes:

0

Hello Matthew,

Thank you very much for your valuable input.

Best regards,
Sebastian

Created on Feb 15, 2019 11:43:24 AM by Sebastian Kniege [Paessler Support]



Votes:

1

Thanks for your reply, Matthew.

Since writing the initial request, I did manage to achieve this solely through Power BI.
With any luck, it will help others. Also, there may well be other ways to achieve the same result. I also admit this has not yet been optimised or tidied up!

Live Data

let
    Source                         = Json.Document(Web.Contents("https://your.website.here/",[RelativePath="api/table.json?content=sensors&columns=objid,group,device,sensor,status,message,lastvalue&username=&passhash=",Query=[username="Username",passhash="Passhash"]])),
    sensors                        = Source[sensors],
    #"Converted to Table"          = Table.FromList(sensors, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1"            = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"objid", "group", "device", "sensor", "status", "message", "lastvalue"}, {"objid", "group", "device", "sensor", "status", "message", "lastvalue"}),
    #"Split Column by Delimiter"   = Table.SplitColumn(#"Expanded Column1", "message", Splitter.SplitTextByEachDelimiter({">"}, QuoteStyle.Csv, false), {"message.1", "message.2"}),
    #"Removed Columns"             = Table.RemoveColumns(#"Split Column by Delimiter",{"message.1"}),
    #"Split Column by Delimiter1"  = Table.SplitColumn(#"Removed Columns", "message.2", Splitter.SplitTextByEachDelimiter({"<"}, QuoteStyle.Csv, false), {"message.2.1", "message.2.2"}),
    #"Changed Type"                = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"message.2.1", type text}, {"message.2.2", type text}}),
    #"Removed Columns1"            = Table.RemoveColumns(#"Changed Type",{"message.2.2"}),
    #"Renamed Columns"             = Table.RenameColumns(#"Removed Columns1",{{"message.2.1", "Message"}, {"lastvalue", "Last Result"}, {"status", "Status"}, {"sensor", "Sensor Name"}, {"group", "Environment"}, {"device", "Category"}, {"objid", "ObjID"}}),
    #"Changed Type1"               = Table.TransformColumnTypes(#"Renamed Columns",{{"ObjID", Int64.Type}, {"Environment", type text}, {"Category", type text}, {"Sensor Name", type text}, {"Status", type text}}),
    #"Added Index"                 = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1),
    #"Reordered Columns"           = Table.ReorderColumns(#"Added Index",{"Index", "ObjID", "Environment", "Category", "Sensor Name", "Status", "Message", "Last Result"}),
    #"Duplicated Column"           = Table.DuplicateColumn(#"Reordered Columns", "Last Result", "Last Result - Copy"),
    #"Split Column by Delimiter2"  = Table.SplitColumn(#"Duplicated Column", "Last Result - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Last Result - Copy.1", "Last Result - Copy.2"}),
    #"Changed Type2"               = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Last Result - Copy.1", Int64.Type}, {"Last Result - Copy.2", type text}}),
    #"Renamed Columns1"            = Table.RenameColumns(#"Changed Type2",{{"Last Result - Copy.1", "Reading"}, {"Last Result - Copy.2", "Metric"}})
    in
    #"Renamed Columns1"

Created on Feb 15, 2019 12:43:29 PM

Last change on Feb 18, 2019 7:48:30 AM by Felix Saure [Paessler Support]



Votes:

0

and for the Historic Data I've done this: -

let
    Source                      = Json.Document(Web.Contents("https://your.website.here/",[RelativePath="api/table.json?content=sensors&columns=objid&username=&passhash=",Query=[username="Username",passhash="Passhash"]])),
    sensors                     = Source[sensors],
    #"Converted to Table"       = Table.FromList(sensors, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1"         = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"objid"}, {"objid"}),
    #"Added Custom"             = Table.AddColumn(#"Expanded Column1", "Relative", each "api/historicdata.json?id=" & Text.From([objid]) & "&avg=0&sdate=2018-12-01-00-00-00&edate=2018-12-31-23-59-59&usecaption=1&username=Username&passhash=Passhash"),
    Data                        = List.Transform(#"Added Custom"[Relative],each Json.Document(Web.Contents("https://your.website.here/",[RelativePath=_]))),
    #"Converted to Table2"      = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index"              = Table.AddIndexColumn(#"Converted to Table2", "Index", 0, 1),
    #"Reordered Columns1"       = Table.ReorderColumns(#"Added Index",{"Index", "Column1"}),
    #"Expanded Column2"         = Table.ExpandRecordColumn(#"Reordered Columns1", "Column1", {"histdata"}, {"histdata"}),
    #"Expanded histdata"        = Table.ExpandListColumn(#"Expanded Column2", "histdata"),
    #"Expanded histdata1"       = Table.ExpandRecordColumn(#"Expanded histdata", "histdata", {"datetime", "Loading time", "Bytes received", "Download Bandwidth", "Time to first byte", "coverage"}, {"datetime", "Loading time", "Bytes received", "Download Bandwidth", "Time to first byte", "coverage"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Expanded histdata1", {{"datetime", type datetime}}, "en-US"),
    #"Renamed Columns"          = Table.RenameColumns(#"Changed Type with Locale",{{"datetime", "Date/Time"}, {"coverage", "Availability"}, {"Loading time", "Loading Time"}, {"Bytes received", "Bytes Received"}, {"Time to first byte", "Time To First Byte"}}),
    #"Changed Type"             = Table.TransformColumnTypes(#"Renamed Columns",{{"Loading Time", Int64.Type}, {"Bytes Received", Int64.Type}, {"Download Bandwidth", Int64.Type}, {"Time To First Byte", Int64.Type}, {"Availability", Percentage.Type}}),
    #"Sorted Rows"              = Table.Sort(#"Changed Type",{{"Date/Time", Order.Descending}})
in
    #"Sorted Rows"

Created on Feb 15, 2019 12:47:11 PM

Last change on Feb 15, 2019 6:17:00 PM by Torsten Lindner [Paessler Support]



Votes:

0

Looks like the code formatting option broke on my two posts - hopefully Sebastian can correct that as there should be numbered lines ;)

Created on Feb 15, 2019 3:46:29 PM

Last change on Feb 15, 2019 3:46:29 PM



Votes:

1

Hi Chris,

The formatting should now be fixed ;)

Best regards

Created on Feb 18, 2019 7:52:22 AM by Sebastian Kniege [Paessler Support]



Votes:

0

Hi, I've edited the script provided by Matthew but I can't find out how to change the date period; for example how can I set the daily report for the past month?

Thanks in advance.

Created on Apr 15, 2019 2:32:16 PM



Votes:

1

Hi dl_infra,

The line to look at is the following

$reportcsv = Request-PRTGSensorHistoricData -ID $s.id -Days 1 -datemodifier $i

-datemodifier = how many days ago

    $i = 0
    do
    {
        $i++

        Write-Host "Obtaining Report..."
	    $reportcsv = Request-PRTGSensorHistoricData -ID $s.id -Days 1 -datemodifier $i
	    $dtresults = $($reportcsv | select "Downtime(RAW)")
	    $dt = 0
	    foreach($r in $dtresults)
	    {
		    $dt += $r.'Downtime(RAW)'
	    }
	    $Uptime = [math]::Round(100 - ($dt / ($dtresults.Count - 1)),2)
        $report = [PSCustomObject]@{
		    "Site" = $group
		    "Category" = $type
		    "Device" = $s.Device
		    "Uptime" = $Uptime
            "Date" = $((Get-Date).AddDays(-$i).ToString("dd-MM-yyyy"))
	    }
        $routerswitchUptimeReport += $report
        $report
    }
    until($i -eq 7)

This code with the last line "until($i -eq 7)" gets the report once per day for 7 days.

Alternatively...

You can modify the following code to specifiy start and end dates and set "until($i -eq 1)" This would return one line to the CSV per sensor rather than 7

$edate = (Get-Date).AddDays(-$datemodifier+1).ToString("yyyy-MM-dd")
$sdate = (Get-Date).AddDays(-$datemodifier).ToString("yyyy-MM-dd")
$url = "https://$PRTGHost/api/historicdata.csv?id=$ID&avg=3600&sdate=$sdate-05-00-00&edate=$edate-00-00-00&$auth"

Note: my script only returns data from between 5am till midnight

Hope that helps. If you need further help post your code and let me know where you are stuck.

Matthew

Created on Apr 15, 2019 3:53:37 PM



Votes:

0

Hi Matthew, thanks for the quick reply.

Thanks to your suggestion I'm able to change the report period.

I'm now looking a method to always create a report for the past month so I can schedule it without change anytime the parameters.

Is that possible?

Thanks

Created on Apr 17, 2019 8:50:45 AM



Votes:

1

Yes completely possible.

Change the line 

$reportcsv = Request-PRTGSensorHistoricData -ID $s.id -Days 1 -datemodifier $i

to

$reportcsv = Request-PRTGSensorHistoricData  -ID $s.id

and change the function Request-PRTGSensorHistoricData from

function Request-PRTGSensorHistoricData
{
    Param (
        [Parameter(Position=0,Mandatory=$True)]
        [ValidateNotNullorEmpty()]
        [int]$ID,
		[int]$days,
		[int]$datemodifier
    )

    try
    {
        $edate = (Get-Date).AddDays(-$datemodifier+1).ToString("yyyy-MM-dd")
        $sdate = (Get-Date).AddDays(-$datemodifier).ToString("yyyy-MM-dd")
        $url = "https://$PRTGHost/api/historicdata.csv?id=$ID&avg=3600&sdate=$sdate-05-00-00&edate=$edate-00-00-00&$auth"
        $request = Invoke-WebRequest -Uri $url -MaximumRedirection 0 -ErrorAction Ignore
        convertFrom-csv ($request)
    }
    catch
    {
    
    }
}

to

function Request-PRTGSensorHistoricData
{
    Param (
        [Parameter(Position=0,Mandatory=$True)]
        [ValidateNotNullorEmpty()]
        [int]$ID
    )

    try
    {
        $edate = (Get-Date).AddDays(-1).ToString("yyyy-MM-dd")
        $sdate = (Get-Date).AddMonths(-1).ToString("yyyy-MM-dd")
        $url = "https://$PRTGHost/api/historicdata.csv?id=$ID&avg=3600&sdate=$sdate-00-00-00&edate=$edate-23-59-59&$auth"
        $request = Invoke-WebRequest -Uri $url -MaximumRedirection 0 -ErrorAction Ignore
        convertFrom-csv ($request)
    }
    catch
    {
    
    }
}

You can then use windows task scheduler to schedule it to run on the 1st of every month and it will report on the previous month.

Created on Apr 17, 2019 10:54:41 AM

Last change on Apr 17, 2019 11:07:48 AM by Dariusz Gorka [Paessler Support]



Votes:

0

Hi Matthew, sorry for the late reply.

I've changed the code lines as indicated by you and it works perfectly.

Thanks a lot

Created on May 2, 2019 8:59:01 AM




Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.