How to export the list of All Sites with its schedules. Please advise if there is a SQL query for this

InsightVM SQL Queries
1

How to export the list of All Sites with its schedules. Please advise if there is a SQL query for this.

2

The only way to do this would be through the API. The reporting data model for the SQL database doesn’t hold any of that data.

Mind you, you would need to first pull all the site id’s and then feed them back in and loop through each id to list out the schedules.

3

Hi John, Thanks for the response. Is there any other easy way to do it.

4

I have always used the API to do this like @john_hartman said, I couldn’t find another way.

Here is what I did, hopefully this will get you started:

$Rapid7_User = Get-Credential
$Rapid7_Headers = @{Authorization = "Basic $([System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes("$($Rapid7_User.UserName):$($Rapid7_User.GetNetworkCredential().password)")))"}
$Rapid7_BaseUrl = 'https://<Rapid7 Host>:3780/api/3'

$Rapid7_Sites = (Invoke-RestMethod -Uri "$Rapid7_BaseUrl/sites?size=500" -Headers $Rapid7_Headers).resources 
$Rapid7_Sites_Info = @()
foreach($Rapid7_Site in $Rapid7_Sites) {
    $Rapid7_Site_Schedules           = (Invoke-RestMethod -Uri "$Rapid7_BaseUrl/sites/$($Rapid7_Site.id)/scan_schedules" -Headers $Rapid7_Headers).resources | select id,scanName,enabled,scanTemplateId,repeat
    $Rapid7_Site_Tags                = (Invoke-RestMethod -Uri "$Rapid7_BaseUrl/sites/$($Rapid7_Site.id)/tags" -Headers $Rapid7_Headers).resources | select id,name,source,type
    #$Rapid7_Site_Users               = (Invoke-RestMethod -Uri "$Rapid7_BaseUrl/sites/$($Rapid7_Site.id)/users" -Headers $Rapid7_Headers).resources | select id,enabled,login
    $Rapid7_Site_Assets              = (Invoke-RestMethod -Uri "$Rapid7_BaseUrl/sites/$($Rapid7_Site.id)/assets" -Headers $Rapid7_Headers).resources | select id,ip,hostname,os
    $Rapid7_Site_IncludedTargets     = (Invoke-RestMethod -Uri "$Rapid7_BaseUrl/sites/$($Rapid7_Site.id)/included_targets" -Headers $Rapid7_Headers).resources
    $Rapid7_Site_ExcludedTargets     = (Invoke-RestMethod -Uri "$Rapid7_BaseUrl/sites/$($Rapid7_Site.id)/excluded_targets" -Headers $Rapid7_Headers).resources
    $Rapid7_Site_IncludedAssetGroups = (Invoke-RestMethod -Uri "$Rapid7_BaseUrl/sites/$($Rapid7_Site.id)/included_asset_groups" -Headers $Rapid7_Headers).resources | select id,name,type,searchCriteria
    $Rapid7_Site_ExcludedAssetGroups = (Invoke-RestMethod -Uri "$Rapid7_BaseUrl/sites/$($Rapid7_Site.id)/excluded_asset_groups" -Headers $Rapid7_Headers).resources | select id,name,type,searchCriteria

    $Rapid7_Sites_Info += [pscustomobject]@{
        ID                  = $Rapid7_Site.id
        Name                = $Rapid7_Site.name
        ScanTemplate        = $Rapid7_Site.scanTemplate
        Type                = $Rapid7_Site.type
        Schedules           = $Rapid7_Site_Schedules
        Tags                = $Rapid7_Site_Tags
        #Users               = $Rapid7_Site_Users
        Assets              = $Rapid7_Site_Assets
        IncludedTargets     = $Rapid7_Site_IncludedTargets
        ExcludedTargets     = $Rapid7_Site_ExcludedTargets
        IncludedAssetGroups = $Rapid7_Site_IncludedAssetGroups
        ExcludedAssetGroups = $Rapid7_Site_ExcludedAssetGroups
    }
}

$Rapid7_Sites_Info | select ID,Name,ScanTemplate,Type,@{name=’Schedules’; expression={if($_.Schedules -eq $null) {0} elseif ($_.Schedules.GetType().Name -eq 'PSCustomObject') {1} else {$_.Schedules.count}}},@{name=’Tags’; expression={if($_.Tags -eq $null) {0} elseif ($_.Tags.GetType().Name -eq 'PSCustomObject') {1} else {$_.Tags.count}}},@{name=’Assets’; expression={if($_.Assets -eq $null) {0} elseif ($_.Assets.GetType().Name -eq 'PSCustomObject') {1} else {$_.Assets.count}}},@{name=’IncludedTargets’; expression={if($_.IncludedTargets -eq $null) {0} elseif ($_.IncludedTargets.GetType().Name -eq 'PSCustomObject') {1} else {$_.IncludedTargets.count}}},@{name=’ExcludedTargets’; expression={if($_.ExcludedTargets -eq $null) {0} elseif ($_.ExcludedTargets.GetType().Name -eq 'PSCustomObject') {1} else {$_.ExcludedTargets.count}}},@{name=’IncludedAssetGroups’; expression={if($_.IncludedAssetGroups -eq $null) {0} elseif ($_.IncludedAssetGroups.GetType().Name -eq 'PSCustomObject') {1} else {$_.IncludedAssetGroups.count}}},@{name=’ExcludedAssetGroups’; expression={if($_.ExcludedAssetGroups -eq $null) {0} elseif ($_.ExcludedAssetGroups.GetType().Name -eq 'PSCustomObject') {1} else {$_.ExcludedAssetGroups.count}}} | Export-Csv -Path .\Downloads\Rapid7_Sites.csv -NoTypeInformation
1 Like
5

Thanks a lot, brandon. We will try this come back with feedback.

6

As a really easy/lazy way of doing what I see you asking. You could also run ‘show schedule’ from the /admin/global/diag_console.html Run dialogue.
Screenshot 2022-11-17 at 4.03.28 PM

1 Like
7

This is super easy. Thank you