Exporting Investigations to CSV

InsightIDR
1

Hi all,

Please, is there a way to export the investigations from InsightIDR to a CSV or Excel.

Thank you.

2 Likes
2

looks like it is just pdf but depending on what your trying to do you could create an alert for all new investigations that get sent to your help desk system, you could then possibly parse or report on. not sure what the end goal is.

2 Likes
3

If the following properties are sufficient you can use InsightIDR API:

  • Investigation id
  • Investigation title
  • Investigation status
  • Investigation source
  • Investigation alerts
  • Investigation created_time

This article can help you to setup the API from InsightIDR side:
https://docs.rapid7.com/insight/managing-platform-api-keys

General InsightIDR API information:
https://docs.rapid7.com/insightidr/insightidr-rest-api/

This PowerShell command bellow will download the last 99 investigations in JSON format and convert them into CSV format and create TheSpreadSheetFile.csv file in your current PowerShell directory:
(((Invoke-WebRequest -Uri "https://eu.api.insight.rapid7.com/idr/v1/investigations?size=99" -Headers @{'X-Api-Key' = ' API key goes here between the quote marks '; 'Content-Type' = 'application/json'}).content|ConvertFrom-Json).data)|Export-Csv -NoTypeInformation -Path TheSpreadSheetFile.csv

You can also pull investigations based on their status as well - open or close.
E.g.:
https://eu.api.insight.rapid7.com/idr/v1/investigations?statuses=OPEN&size=99

Please note that with each API call you can pull limited number of investigations - more details can be found in the document above.

Alternatively if you wish to pull more details about investigations you can open the Investigation page then fire up the built-in browser DevTools then refresh the investigation page.
Under the "Network’ section of DevTools look for request for “list”. The response section will contain the currently displayed investigations in JSON format. Then this JSON can be manipulated with PowerShell and converted to CSV, but that is a tedious process.

Let me know if you wish to take this path so I can share some additional details.

2 Likes
4

Thank you for this! This really satisfy what I was looking for.
I created my organization API key and used it with the Powershell script.

3 Likes
5

Hello
Thank you very much for your help.
The PoweShell script helped me.

The problem is that the csv does not export the fields:
-Priority
-Assignee
-Disposition

Would you know what to modify in PowerShell to get these values?

Thank you very much.

6

Unfortunately “Priority” and “Disposition” are not provided by the API. “Assignee” is present in the API response. However it is a nested value. I will try to modify the script so the “Assignee” value can be added properly to the resulting .csv file.

7

Also just to note, priority and disposition will be coming soon to a new evolved investigation API

David

1 Like
8

(((Invoke-WebRequest -Uri "https://eu.api.insight.rapid7.com/idr/v1/investigations?size=99" -Headers @{'X-Api-Key' = ' API key goes here between the quote marks '; 'Content-Type' = 'application/json'}).content|ConvertFrom-Json).data)| foreach {$r=$_|select * -ExcludeProperty assignee,alerts; $_.assignee | foreach {$r | Add-Member -Name "assignee_name" -Value $_.name -MemberType NoteProperty; $r| Add-Member -Name "assignee_email" -Value $_.email -MemberType NoteProperty}; $_.alerts | foreach {$r | Add-Member -Name "alert_type" -Value $_.type -MemberType NoteProperty; $r| Add-Member -Name "alert_description" -Value $_.type_description -MemberType NoteProperty}; return $r}|Export-Csv -NoTypeInformation -Path TheSpreadSheetFile.csv

(Please adjust “size=” accordingly - from 1 to 1000, please fill in the Api-Key accordingly.)

The script should print the following columns:
id
title
status
source
created_time
assignee_name
assignee_email
alert_type
alert_description

The “assignee” object is replaced by two new objects based on the “assignee” values (this is done by the script):
assignee_name and assignee_email
Same for the “alerts” object - replaced by:
alert_type
alert_description

Credits for the script:
https://stackoverflow.com/a/62451008

Let me know how it goes.

Regards

1 Like
9

I just want to say thank you very much for the shell script provided here. Been using it for a couple of months already.

Unfortunately I think there’s been some changes on the API as the above latest script result doesn’t include the alert [] details portion. So I build one myself based off the information here: https://analyticoolblog.com/graphql-in-python-how-to-query-graphql-api-for-beginners/

Excuse my script as this is only a work of a beginner and I don’t specialize in coding stuff. Hopefully though others here can simplify it.

Python:
import requests
import json
import pandas as pd
from pandas import json_normalize
from pandas import DataFrame
import datetime as dt
from pytz import all_timezones

keyid = “YOUR_API_KEY_ID”

#please use Postman to verify URL expected result to change query
url = “https://eu.api.insight.rapid7.com/idr/v1/investigations?end_time=2023-02-14T11:59:59Z&index=0&size=20&start_time=2023-02-14T00:00:00Z&statuses=OPEN,CLOSED”
headers = {“X-Api-Key”:keyid, “Content-Type”:“application/json”}

#my request
r = requests.request(“GET”, url, headers=headers)

#Flattening out the expected JSON output
def flatten_json(y):
out = {}

def flatten(x, name=''):
    if type(x) is dict:
        for a in x:
            flatten(x[a], name + a + '_')
    elif type(x) is list:
        i = 0
        for a in x:
            flatten(a, name + str(i) + '_')
            i += 1
    else:
        out[name[:-1]] = x

flatten(y)
return out

#converts flatten output into py panda data frame
json_data = json.loads(r.text)
flat = flatten_json(json_data)
df_data = pd.json_normalize(flat)
df = pd.DataFrame(df_data)

#columns maker
df = pd.melt(df)

#Arrange columns
df[“NodeNo”] = df.variable.str.extract(pat=“data_(\d*)")
df[“Varformat”] = df.variable.str.extract(pat= "data[0-9]._(..)”)
df = df[[‘NodeNo’, ‘Varformat’, ‘value’]]
df = df.dropna(subset=[‘NodeNo’])
df = df.drop_duplicates()
df = df.pivot(index=‘NodeNo’, columns=‘Varformat’, values=‘value’)

#resulting file
df.to_csv(“c:/Scripts/Investigation/listinvestigation.csv”, sep=",", index= False)

Expected Output:
image