Join by field not working with JSON from URL API - Infinity Datasource

Grafana Dashboards
, ,
1

Hi! I’m using Infinity Datasource, and trying to get the Detections Details for each detection from Crowdstrike API, joining the information from two API URLs responses:

One, https://api.crowdstrike.com/detects/queries/detects/v1, that return the list of IDs of Detections:

imagen
imagen3015×1594 278 KB

This query only respond the column “Detection”, as you can see in the image

The other, https://api.crowdstrike.com/detects/entities/summaries/GET/v1, that returns the details of Detection ID:

imagen
imagen3212×1626 325 KB

This query return four columns: “Detection”, “IP Pública”, “Hostname” and “IP Local” (some of the field names are in spanish, sorry for that).

I’m doing the Join by field, OUTER, with “Detection Detection”, the field of the first query:

imagen
imagen2925×1563 275 KB

My expectation is that after the join, I have additional information of the first Detection (in the second query I’m only passing the ID of the first detection hardcoded, to test; if i finally get it to work, I have to figure out how to pass each of the values ​​I get with a variable or something like that from the first API query to the second API query), and the other IDs returned without additional information, but I see the return of the first query (all Detection IDs) as there no join configured.

Here is the content of the first API response, detections.json:

{
 "meta": {
  "query_time": 0.014125516,
  "pagination": {
   "offset": 0,
   "limit": 100,
   "total": 90
  },
  "powered_by": "legacy-detects",
  "trace_id": "0357f511-9a41-428c-92c7-a268372e286b"
 },
 "resources": [
  "ldt:aad4340146db4f94b3efafaa5e6f7bf2:240518760420",
  "ldt:aad4340146db4f94b3efafaa5e6f7bf2:240519379385",
  "ldt:aad4340146db4f94b3efafaa5e6f7bf2:236224880601",
  "ldt:aad4340146db4f94b3efafaa5e6f7bf2:236225729249",
  "ldt:aad4340146db4f94b3efafaa5e6f7bf2:236223673153"
 ],
 "errors": []
}

Here is the content of the second API response, detection_details.json (private information from some fields was removed):

{
 "meta": {
  "query_time": 0.004915711,
  "powered_by": "legacy-detects",
  "trace_id": "e85b725b-1996-4f87-b54e-043e7cdbb860"
 },
 "resources": [
  {
   "cid": "9e250b3eed53444f87e16a18e970a691",
   "created_timestamp": "2023-03-11T20:38:56.889269698Z",
   "detection_id": "ldt:aad4340146db4f94b3efafaa5e6f7bf2:240518760420",
   "device": {
    "device_id": "aad4340146db4f94b3efafaa5e6f7bf2",
    "cid": "9e250b3eed53444f87e16a18e970a691",
    "agent_load_flags": "1",
    "agent_local_time": "2023-03-11T17:34:38.698Z",
    "agent_version": "6.51.16510.0",
    "bios_manufacturer": "American Megatrends International, LLC.",
    "bios_version": "B.C0",
    "config_id_base": "65994753",
    "config_id_build": "16510",
    "config_id_platform": "3",
    "external_ip": "181.16.120.166",
    "hostname": "PC-MATI",
    "first_seen": "2022-09-17T01:34:08Z",
    "last_seen": "2023-03-11T20:36:13Z",
    "local_ip": "192.168.1.100",
    "mac_address": "28-87-ba-a4-a7-ab",
    "major_version": "10",
    "minor_version": "0",
    "os_version": "Windows 10",
    "platform_id": "0",
    "platform_name": "Windows",
    "product_type": "1",
    "product_type_desc": "Workstation",
    "status": "normal",
    "system_manufacturer": "Micro-Star International Co., Ltd.",
    "system_product_name": "MS-7A38",
    "tags": [
     "SensorGroupingTags/LAB_PV"
    ],
    "groups": [
     "0efc708426d349c2af1f9de9ea7acce3"
    ],
    "modified_timestamp": "2023-03-11T20:37:47Z"
   },
   "behaviors": [
    {
     "device_id": "aad4340146db4f94b3efafaa5e6f7bf2",
     "timestamp": "2023-03-11T20:38:50Z",
     "behavior_id": "5714",
     "filename": "BEService.exe",
     "filepath": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "alleged_filetype": "exe",
     "cmdline": "\"C:\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe\"",
     "scenario": "NGAV",
     "objective": "Falcon Detection Method",
     "tactic": "Machine Learning",
     "tactic_id": "CSTA0004",
     "technique": "Sensor-based ML",
     "technique_id": "CST0007",
     "display_name": "",
     "description": "This file meets the machine learning-based on-sensor AV protection's lowest-confidence threshold for malicious files.",
     "severity": 10,
     "confidence": 10,
     "ioc_type": "hash_sha256",
     "ioc_value": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "ioc_source": "library_load",
     "ioc_description": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "user_name": "PC-MATI$",
     "user_id": "S-1-5-18",
     "control_graph_id": "ctg:aad4340146db4f94b3efafaa5e6f7bf2:240518760420",
     "triggering_process_graph_id": "pid:aad4340146db4f94b3efafaa5e6f7bf2:335318596996",
     "sha256": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "md5": "4796e18088a55f4ac248595a95bd154e",
     "parent_details": {
      "parent_sha256": "",
      "parent_md5": "",
      "parent_cmdline": "",
      "parent_process_graph_id": ""
     },
     "pattern_disposition": 2176,
     "pattern_disposition_details": {
      "indicator": false,
      "detect": false,
      "inddet_mask": false,
      "sensor_only": false,
      "rooting": false,
      "kill_process": false,
      "kill_subprocess": false,
      "quarantine_machine": false,
      "quarantine_file": true,
      "policy_disabled": false,
      "kill_parent": false,
      "operation_blocked": false,
      "process_blocked": true,
      "registry_operation_blocked": false,
      "critical_process_disabled": false,
      "bootup_safeguard_enabled": false,
      "fs_operation_blocked": false,
      "handle_operation_downgraded": false,
      "kill_action_failed": false,
      "blocking_unsupported_or_disabled": false,
      "suspend_process": false,
      "suspend_parent": false
     }
    },
    {
     "device_id": "aad4340146db4f94b3efafaa5e6f7bf2",
     "timestamp": "2023-03-11T22:15:36Z",
     "behavior_id": "5714",
     "filename": "BEService.exe",
     "filepath": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "alleged_filetype": "exe",
     "cmdline": "\"C:\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe\"",
     "scenario": "NGAV",
     "objective": "Falcon Detection Method",
     "tactic": "Machine Learning",
     "tactic_id": "CSTA0004",
     "technique": "Sensor-based ML",
     "technique_id": "CST0007",
     "display_name": "",
     "description": "This file meets the machine learning-based on-sensor AV protection's lowest-confidence threshold for malicious files.",
     "severity": 10,
     "confidence": 10,
     "ioc_type": "hash_sha256",
     "ioc_value": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "ioc_source": "library_load",
     "ioc_description": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "user_name": "PC-MATI$",
     "user_id": "S-1-5-18",
     "control_graph_id": "ctg:aad4340146db4f94b3efafaa5e6f7bf2:240518760420",
     "triggering_process_graph_id": "pid:aad4340146db4f94b3efafaa5e6f7bf2:335680719972",
     "sha256": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "md5": "4796e18088a55f4ac248595a95bd154e",
     "parent_details": {
      "parent_sha256": "",
      "parent_md5": "",
      "parent_cmdline": "",
      "parent_process_graph_id": ""
     },
     "pattern_disposition": 2176,
     "pattern_disposition_details": {
      "indicator": false,
      "detect": false,
      "inddet_mask": false,
      "sensor_only": false,
      "rooting": false,
      "kill_process": false,
      "kill_subprocess": false,
      "quarantine_machine": false,
      "quarantine_file": true,
      "policy_disabled": false,
      "kill_parent": false,
      "operation_blocked": false,
      "process_blocked": true,
      "registry_operation_blocked": false,
      "critical_process_disabled": false,
      "bootup_safeguard_enabled": false,
      "fs_operation_blocked": false,
      "handle_operation_downgraded": false,
      "kill_action_failed": false,
      "blocking_unsupported_or_disabled": false,
      "suspend_process": false,
      "suspend_parent": false
     }
    },
    {
     "device_id": "aad4340146db4f94b3efafaa5e6f7bf2",
     "timestamp": "2023-03-11T22:15:57Z",
     "behavior_id": "5714",
     "filename": "BEService.exe",
     "filepath": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "alleged_filetype": "exe",
     "cmdline": "\"C:\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe\"",
     "scenario": "NGAV",
     "objective": "Falcon Detection Method",
     "tactic": "Machine Learning",
     "tactic_id": "CSTA0004",
     "technique": "Sensor-based ML",
     "technique_id": "CST0007",
     "display_name": "",
     "description": "This file meets the machine learning-based on-sensor AV protection's lowest-confidence threshold for malicious files.",
     "severity": 10,
     "confidence": 10,
     "ioc_type": "hash_sha256",
     "ioc_value": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "ioc_source": "library_load",
     "ioc_description": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "user_name": "PC-MATI$",
     "user_id": "S-1-5-18",
     "control_graph_id": "ctg:aad4340146db4f94b3efafaa5e6f7bf2:240518760420",
     "triggering_process_graph_id": "pid:aad4340146db4f94b3efafaa5e6f7bf2:335689163196",
     "sha256": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "md5": "4796e18088a55f4ac248595a95bd154e",
     "parent_details": {
      "parent_sha256": "",
      "parent_md5": "",
      "parent_cmdline": "",
      "parent_process_graph_id": ""
     },
     "pattern_disposition": 2176,
     "pattern_disposition_details": {
      "indicator": false,
      "detect": false,
      "inddet_mask": false,
      "sensor_only": false,
      "rooting": false,
      "kill_process": false,
      "kill_subprocess": false,
      "quarantine_machine": false,
      "quarantine_file": true,
      "policy_disabled": false,
      "kill_parent": false,
      "operation_blocked": false,
      "process_blocked": true,
      "registry_operation_blocked": false,
      "critical_process_disabled": false,
      "bootup_safeguard_enabled": false,
      "fs_operation_blocked": false,
      "handle_operation_downgraded": false,
      "kill_action_failed": false,
      "blocking_unsupported_or_disabled": false,
      "suspend_process": false,
      "suspend_parent": false
     }
    },
    {
     "device_id": "aad4340146db4f94b3efafaa5e6f7bf2",
     "timestamp": "2023-03-11T22:41:43Z",
     "behavior_id": "5714",
     "filename": "BEService.exe",
     "filepath": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "alleged_filetype": "exe",
     "cmdline": "\"C:\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe\"",
     "scenario": "NGAV",
     "objective": "Falcon Detection Method",
     "tactic": "Machine Learning",
     "tactic_id": "CSTA0004",
     "technique": "Sensor-based ML",
     "technique_id": "CST0007",
     "display_name": "",
     "description": "This file meets the machine learning-based on-sensor AV protection's lowest-confidence threshold for malicious files.",
     "severity": 10,
     "confidence": 10,
     "ioc_type": "hash_sha256",
     "ioc_value": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "ioc_source": "library_load",
     "ioc_description": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "user_name": "PC-MATI$",
     "user_id": "S-1-5-18",
     "control_graph_id": "ctg:aad4340146db4f94b3efafaa5e6f7bf2:240518760420",
     "triggering_process_graph_id": "pid:aad4340146db4f94b3efafaa5e6f7bf2:335729350808",
     "sha256": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "md5": "4796e18088a55f4ac248595a95bd154e",
     "parent_details": {
      "parent_sha256": "",
      "parent_md5": "",
      "parent_cmdline": "",
      "parent_process_graph_id": ""
     },
     "pattern_disposition": 2176,
     "pattern_disposition_details": {
      "indicator": false,
      "detect": false,
      "inddet_mask": false,
      "sensor_only": false,
      "rooting": false,
      "kill_process": false,
      "kill_subprocess": false,
      "quarantine_machine": false,
      "quarantine_file": true,
      "policy_disabled": false,
      "kill_parent": false,
      "operation_blocked": false,
      "process_blocked": true,
      "registry_operation_blocked": false,
      "critical_process_disabled": false,
      "bootup_safeguard_enabled": false,
      "fs_operation_blocked": false,
      "handle_operation_downgraded": false,
      "kill_action_failed": false,
      "blocking_unsupported_or_disabled": false,
      "suspend_process": false,
      "suspend_parent": false
     }
    },
    {
     "device_id": "aad4340146db4f94b3efafaa5e6f7bf2",
     "timestamp": "2023-03-11T23:52:38Z",
     "behavior_id": "5714",
     "filename": "BEService.exe",
     "filepath": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "alleged_filetype": "exe",
     "cmdline": "\"C:\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe\"",
     "scenario": "NGAV",
     "objective": "Falcon Detection Method",
     "tactic": "Machine Learning",
     "tactic_id": "CSTA0004",
     "technique": "Sensor-based ML",
     "technique_id": "CST0007",
     "display_name": "",
     "description": "This file meets the machine learning-based on-sensor AV protection's lowest-confidence threshold for malicious files.",
     "severity": 10,
     "confidence": 10,
     "ioc_type": "hash_sha256",
     "ioc_value": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "ioc_source": "library_load",
     "ioc_description": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "user_name": "PC-MATI$",
     "user_id": "S-1-5-18",
     "control_graph_id": "ctg:aad4340146db4f94b3efafaa5e6f7bf2:240518760420",
     "triggering_process_graph_id": "pid:aad4340146db4f94b3efafaa5e6f7bf2:335809832378",
     "sha256": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "md5": "4796e18088a55f4ac248595a95bd154e",
     "parent_details": {
      "parent_sha256": "",
      "parent_md5": "",
      "parent_cmdline": "",
      "parent_process_graph_id": ""
     },
     "pattern_disposition": 2176,
     "pattern_disposition_details": {
      "indicator": false,
      "detect": false,
      "inddet_mask": false,
      "sensor_only": false,
      "rooting": false,
      "kill_process": false,
      "kill_subprocess": false,
      "quarantine_machine": false,
      "quarantine_file": true,
      "policy_disabled": false,
      "kill_parent": false,
      "operation_blocked": false,
      "process_blocked": true,
      "registry_operation_blocked": false,
      "critical_process_disabled": false,
      "bootup_safeguard_enabled": false,
      "fs_operation_blocked": false,
      "handle_operation_downgraded": false,
      "kill_action_failed": false,
      "blocking_unsupported_or_disabled": false,
      "suspend_process": false,
      "suspend_parent": false
     }
    },
    {
     "device_id": "aad4340146db4f94b3efafaa5e6f7bf2",
     "timestamp": "2023-03-11T23:52:47Z",
     "behavior_id": "5714",
     "filename": "BEService.exe",
     "filepath": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "alleged_filetype": "exe",
     "cmdline": "\"C:\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe\"",
     "scenario": "NGAV",
     "objective": "Falcon Detection Method",
     "tactic": "Machine Learning",
     "tactic_id": "CSTA0004",
     "technique": "Sensor-based ML",
     "technique_id": "CST0007",
     "display_name": "",
     "description": "This file meets the machine learning-based on-sensor AV protection's lowest-confidence threshold for malicious files.",
     "severity": 10,
     "confidence": 10,
     "ioc_type": "hash_sha256",
     "ioc_value": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "ioc_source": "library_load",
     "ioc_description": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "user_name": "PC-MATI$",
     "user_id": "S-1-5-18",
     "control_graph_id": "ctg:aad4340146db4f94b3efafaa5e6f7bf2:240518760420",
     "triggering_process_graph_id": "pid:aad4340146db4f94b3efafaa5e6f7bf2:335827679961",
     "sha256": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "md5": "4796e18088a55f4ac248595a95bd154e",
     "parent_details": {
      "parent_sha256": "",
      "parent_md5": "",
      "parent_cmdline": "",
      "parent_process_graph_id": ""
     },
     "pattern_disposition": 2176,
     "pattern_disposition_details": {
      "indicator": false,
      "detect": false,
      "inddet_mask": false,
      "sensor_only": false,
      "rooting": false,
      "kill_process": false,
      "kill_subprocess": false,
      "quarantine_machine": false,
      "quarantine_file": true,
      "policy_disabled": false,
      "kill_parent": false,
      "operation_blocked": false,
      "process_blocked": true,
      "registry_operation_blocked": false,
      "critical_process_disabled": false,
      "bootup_safeguard_enabled": false,
      "fs_operation_blocked": false,
      "handle_operation_downgraded": false,
      "kill_action_failed": false,
      "blocking_unsupported_or_disabled": false,
      "suspend_process": false,
      "suspend_parent": false
     }
    },
    {
     "device_id": "aad4340146db4f94b3efafaa5e6f7bf2",
     "timestamp": "2023-03-11T23:53:05Z",
     "behavior_id": "5714",
     "filename": "BEService.exe",
     "filepath": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "alleged_filetype": "exe",
     "cmdline": "\"C:\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe\"",
     "scenario": "NGAV",
     "objective": "Falcon Detection Method",
     "tactic": "Machine Learning",
     "tactic_id": "CSTA0004",
     "technique": "Sensor-based ML",
     "technique_id": "CST0007",
     "display_name": "",
     "description": "This file meets the machine learning-based on-sensor AV protection's lowest-confidence threshold for malicious files.",
     "severity": 10,
     "confidence": 10,
     "ioc_type": "hash_sha256",
     "ioc_value": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "ioc_source": "library_load",
     "ioc_description": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "user_name": "PC-MATI$",
     "user_id": "S-1-5-18",
     "control_graph_id": "ctg:aad4340146db4f94b3efafaa5e6f7bf2:240518760420",
     "triggering_process_graph_id": "pid:aad4340146db4f94b3efafaa5e6f7bf2:335837183240",
     "sha256": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "md5": "4796e18088a55f4ac248595a95bd154e",
     "parent_details": {
      "parent_sha256": "",
      "parent_md5": "",
      "parent_cmdline": "",
      "parent_process_graph_id": ""
     },
     "pattern_disposition": 2176,
     "pattern_disposition_details": {
      "indicator": false,
      "detect": false,
      "inddet_mask": false,
      "sensor_only": false,
      "rooting": false,
      "kill_process": false,
      "kill_subprocess": false,
      "quarantine_machine": false,
      "quarantine_file": true,
      "policy_disabled": false,
      "kill_parent": false,
      "operation_blocked": false,
      "process_blocked": true,
      "registry_operation_blocked": false,
      "critical_process_disabled": false,
      "bootup_safeguard_enabled": false,
      "fs_operation_blocked": false,
      "handle_operation_downgraded": false,
      "kill_action_failed": false,
      "blocking_unsupported_or_disabled": false,
      "suspend_process": false,
      "suspend_parent": false
     }
    },
    {
     "device_id": "aad4340146db4f94b3efafaa5e6f7bf2",
     "timestamp": "2023-03-11T23:53:21Z",
     "behavior_id": "5714",
     "filename": "BEService.exe",
     "filepath": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "alleged_filetype": "exe",
     "cmdline": "\"C:\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe\"",
     "scenario": "NGAV",
     "objective": "Falcon Detection Method",
     "tactic": "Machine Learning",
     "tactic_id": "CSTA0004",
     "technique": "Sensor-based ML",
     "technique_id": "CST0007",
     "display_name": "",
     "description": "This file meets the machine learning-based on-sensor AV protection's lowest-confidence threshold for malicious files.",
     "severity": 10,
     "confidence": 10,
     "ioc_type": "hash_sha256",
     "ioc_value": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "ioc_source": "library_load",
     "ioc_description": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "user_name": "PC-MATI$",
     "user_id": "S-1-5-18",
     "control_graph_id": "ctg:aad4340146db4f94b3efafaa5e6f7bf2:240518760420",
     "triggering_process_graph_id": "pid:aad4340146db4f94b3efafaa5e6f7bf2:335846258556",
     "sha256": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "md5": "4796e18088a55f4ac248595a95bd154e",
     "parent_details": {
      "parent_sha256": "",
      "parent_md5": "",
      "parent_cmdline": "",
      "parent_process_graph_id": ""
     },
     "pattern_disposition": 2176,
     "pattern_disposition_details": {
      "indicator": false,
      "detect": false,
      "inddet_mask": false,
      "sensor_only": false,
      "rooting": false,
      "kill_process": false,
      "kill_subprocess": false,
      "quarantine_machine": false,
      "quarantine_file": true,
      "policy_disabled": false,
      "kill_parent": false,
      "operation_blocked": false,
      "process_blocked": true,
      "registry_operation_blocked": false,
      "critical_process_disabled": false,
      "bootup_safeguard_enabled": false,
      "fs_operation_blocked": false,
      "handle_operation_downgraded": false,
      "kill_action_failed": false,
      "blocking_unsupported_or_disabled": false,
      "suspend_process": false,
      "suspend_parent": false
     }
    },
    {
     "device_id": "aad4340146db4f94b3efafaa5e6f7bf2",
     "timestamp": "2023-03-11T23:55:15Z",
     "behavior_id": "5714",
     "filename": "BEService.exe",
     "filepath": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "alleged_filetype": "exe",
     "cmdline": "\"C:\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe\"",
     "scenario": "NGAV",
     "objective": "Falcon Detection Method",
     "tactic": "Machine Learning",
     "tactic_id": "CSTA0004",
     "technique": "Sensor-based ML",
     "technique_id": "CST0007",
     "display_name": "",
     "description": "This file meets the machine learning-based on-sensor AV protection's lowest-confidence threshold for malicious files.",
     "severity": 10,
     "confidence": 10,
     "ioc_type": "hash_sha256",
     "ioc_value": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "ioc_source": "library_load",
     "ioc_description": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "user_name": "PC-MATI$",
     "user_id": "S-1-5-18",
     "control_graph_id": "ctg:aad4340146db4f94b3efafaa5e6f7bf2:240518760420",
     "triggering_process_graph_id": "pid:aad4340146db4f94b3efafaa5e6f7bf2:335857303916",
     "sha256": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "md5": "4796e18088a55f4ac248595a95bd154e",
     "parent_details": {
      "parent_sha256": "",
      "parent_md5": "",
      "parent_cmdline": "",
      "parent_process_graph_id": ""
     },
     "pattern_disposition": 2176,
     "pattern_disposition_details": {
      "indicator": false,
      "detect": false,
      "inddet_mask": false,
      "sensor_only": false,
      "rooting": false,
      "kill_process": false,
      "kill_subprocess": false,
      "quarantine_machine": false,
      "quarantine_file": true,
      "policy_disabled": false,
      "kill_parent": false,
      "operation_blocked": false,
      "process_blocked": true,
      "registry_operation_blocked": false,
      "critical_process_disabled": false,
      "bootup_safeguard_enabled": false,
      "fs_operation_blocked": false,
      "handle_operation_downgraded": false,
      "kill_action_failed": false,
      "blocking_unsupported_or_disabled": false,
      "suspend_process": false,
      "suspend_parent": false
     }
    },
    {
     "device_id": "aad4340146db4f94b3efafaa5e6f7bf2",
     "timestamp": "2023-03-11T23:55:36Z",
     "behavior_id": "5714",
     "filename": "BEService.exe",
     "filepath": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "alleged_filetype": "exe",
     "cmdline": "\"C:\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe\"",
     "scenario": "NGAV",
     "objective": "Falcon Detection Method",
     "tactic": "Machine Learning",
     "tactic_id": "CSTA0004",
     "technique": "Sensor-based ML",
     "technique_id": "CST0007",
     "display_name": "",
     "description": "This file meets the machine learning-based on-sensor AV protection's lowest-confidence threshold for malicious files.",
     "severity": 10,
     "confidence": 10,
     "ioc_type": "hash_sha256",
     "ioc_value": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "ioc_source": "library_load",
     "ioc_description": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "user_name": "PC-MATI$",
     "user_id": "S-1-5-18",
     "control_graph_id": "ctg:aad4340146db4f94b3efafaa5e6f7bf2:240518760420",
     "triggering_process_graph_id": "pid:aad4340146db4f94b3efafaa5e6f7bf2:335865843953",
     "sha256": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "md5": "4796e18088a55f4ac248595a95bd154e",
     "parent_details": {
      "parent_sha256": "",
      "parent_md5": "",
      "parent_cmdline": "",
      "parent_process_graph_id": ""
     },
     "pattern_disposition": 2176,
     "pattern_disposition_details": {
      "indicator": false,
      "detect": false,
      "inddet_mask": false,
      "sensor_only": false,
      "rooting": false,
      "kill_process": false,
      "kill_subprocess": false,
      "quarantine_machine": false,
      "quarantine_file": true,
      "policy_disabled": false,
      "kill_parent": false,
      "operation_blocked": false,
      "process_blocked": true,
      "registry_operation_blocked": false,
      "critical_process_disabled": false,
      "bootup_safeguard_enabled": false,
      "fs_operation_blocked": false,
      "handle_operation_downgraded": false,
      "kill_action_failed": false,
      "blocking_unsupported_or_disabled": false,
      "suspend_process": false,
      "suspend_parent": false
     }
    },
    {
     "device_id": "aad4340146db4f94b3efafaa5e6f7bf2",
     "timestamp": "2023-03-11T23:56:02Z",
     "behavior_id": "5714",
     "filename": "BEService.exe",
     "filepath": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "alleged_filetype": "exe",
     "cmdline": "\"C:\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe\"",
     "scenario": "NGAV",
     "objective": "Falcon Detection Method",
     "tactic": "Machine Learning",
     "tactic_id": "CSTA0004",
     "technique": "Sensor-based ML",
     "technique_id": "CST0007",
     "display_name": "",
     "description": "This file meets the machine learning-based on-sensor AV protection's lowest-confidence threshold for malicious files.",
     "severity": 10,
     "confidence": 10,
     "ioc_type": "hash_sha256",
     "ioc_value": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "ioc_source": "library_load",
     "ioc_description": "\\Device\\HarddiskVolume4\\Program Files (x86)\\Common Files\\BattlEye\\BEService.exe",
     "user_name": "PC-MATI$",
     "user_id": "S-1-5-18",
     "control_graph_id": "ctg:aad4340146db4f94b3efafaa5e6f7bf2:240518760420",
     "triggering_process_graph_id": "pid:aad4340146db4f94b3efafaa5e6f7bf2:335890100789",
     "sha256": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "md5": "4796e18088a55f4ac248595a95bd154e",
     "parent_details": {
      "parent_sha256": "",
      "parent_md5": "",
      "parent_cmdline": "",
      "parent_process_graph_id": ""
     },
     "pattern_disposition": 2176,
     "pattern_disposition_details": {
      "indicator": false,
      "detect": false,
      "inddet_mask": false,
      "sensor_only": false,
      "rooting": false,
      "kill_process": false,
      "kill_subprocess": false,
      "quarantine_machine": false,
      "quarantine_file": true,
      "policy_disabled": false,
      "kill_parent": false,
      "operation_blocked": false,
      "process_blocked": true,
      "registry_operation_blocked": false,
      "critical_process_disabled": false,
      "bootup_safeguard_enabled": false,
      "fs_operation_blocked": false,
      "handle_operation_downgraded": false,
      "kill_action_failed": false,
      "blocking_unsupported_or_disabled": false,
      "suspend_process": false,
      "suspend_parent": false
     }
    }
   ],
   "email_sent": false,
   "first_behavior": "2023-03-11T20:38:50Z",
   "last_behavior": "2023-03-11T23:56:02Z",
   "max_confidence": 10,
   "max_severity": 10,
   "max_severity_displayname": "Informational",
   "show_in_ui": true,
   "status": "new",
   "hostinfo": {
    "domain": ""
   },
   "seconds_to_triaged": 0,
   "seconds_to_resolved": 0,
   "quarantined_files": [
    {
     "id": "aad4340146db4f94b3efafaa5e6f7bf2_258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "sha256": "258e18c264ebe68baff92eb928878a902b99a5fb4454881ccdb50ef4c7136e6c",
     "state": "quarantined"
    }
   ],
   "behaviors_processed": [
    "pid:aad4340146db4f94b3efafaa5e6f7bf2:335318596996:5714",
    "pid:aad4340146db4f94b3efafaa5e6f7bf2:335680719972:5714",
    "pid:aad4340146db4f94b3efafaa5e6f7bf2:335689163196:5714",
    "pid:aad4340146db4f94b3efafaa5e6f7bf2:335729350808:5714",
    "pid:aad4340146db4f94b3efafaa5e6f7bf2:335809832378:5714",
    "pid:aad4340146db4f94b3efafaa5e6f7bf2:335827679961:5714",
    "pid:aad4340146db4f94b3efafaa5e6f7bf2:335837183240:5714",
    "pid:aad4340146db4f94b3efafaa5e6f7bf2:335857303916:5714",
    "pid:aad4340146db4f94b3efafaa5e6f7bf2:335846258556:5714",
    "pid:aad4340146db4f94b3efafaa5e6f7bf2:335865843953:5714",
    "pid:aad4340146db4f94b3efafaa5e6f7bf2:335890100789:5714"
   ],
   "date_updated": "2023-03-12T00:00:38Z"
  }
 ],
 "errors": []
}

With this, anybody can reproduce the issue.

Version Details:

Grafana version : 9.4.3
Plugin version : 1.3.0

I tried for example to do this same join inserting some of the information of both JSON files in a MySQL DB, in two different tables, and the Join by Field - OUTER is working as expected, giving me all the list of detections IDs, with the first one filled with the additional information columns.

Can anyone confirm that this is a bug, or tell me if I’m doing something wrong?
I opened a github issue on Infinity Datasource github too, because it is not clear for me if this is a problem of the plugin or of Grafana itself.

Thanks for your help.

2

Infinity does not allow join. It is a feature that might be included in next version. Check infinity github repo

1 Like
3

Thanks @yosiasz , I didn´t see that:

imagen
imagen757×460 101 KB

Regards.

1 Like
4

But that does not mean you cannot do it. For example you could try jsonata along with apache echarts grafana plugin.

Please post sample json data (minus confidential stuff) and see what we can do.
Also what visualization you want this in

5

Thanks @yosiasz.
The sample JSON data is already in the original post. And about the visualization, at first is ok with a table, to understand the process of joining, and after that we can try with bar charts but for more specific information.

Regards!

1 Like
6

sorry got tied up with other stuff. what data point do you need from detections list that you have to join it to the details?

try one of the jsonatas below.

parse-json
| scope "resources"
#|jsonata "$.{'detection_id': detection_id, 'hostname': device.hostname, 'external_ip': device.external_ip, 'local_ip': device.local_ip}"
|jsonata " ( $data := $map($, function($v) { { 'detection_id': $v.detection_id, 'hostname': $v.device.hostname, 'external_ip': $v.device.external_ip, 'local_ip': $v.device.local_ip } });$data[detection_id='$detections'];)"

Or do you want the capability to filter by detection_id:
ldt:aad4340146db4f94b3efafaa5e6f7bf2:240518760420

or All?

image

7

Hi @yosiasz,

Thanks for your response.
Basically, I get the detection IDs list from the first query, and with the second query I get the details of each detection ID. The capability of filter by detection_id is a desired option, yes.

At the end, I managed to get this working by using a variable to get all the detection IDs:

imagen
imagen3840×1477 256 KB

And using it into the query to get the details:
imagen
imagen2972×809 139 KB

Using UQL Parser, because it was more simple to do the parsing, and in this moment I don’t need alerts:

parse-json
| project "resources"
parse-json
| extend "customer"=strcat('TEST'), "technology"=strcat('EDR - CRW'), "status"=toupper("status"), "assigned_to_name"=toupper("assigned_to_name"), "date_updated"=todatetime("date_updated"), "hostname"="device.hostname", "local_ip"="device.local_ip", "external_ip"="device.external_ip", "tactic"="behaviors[0].tactic", "technique"="behaviors[0].technique", "user_name"="behaviors[0].user_name", "id_new"=split("detection_id",':'), "url"=strcat('https://falcon.crowdstrike.com/activity/detections/detail/',"id_new[1]",'/',"id_new[2]"), "detection_id"=strcat('https://grafana.appliance/d/3oU4r7BVk?orgId=1&var-detection_id=',"detection_id")

The final result is this (I’m not showing the detection_id that I’m using as a variable to create the list):

imagen
imagen3683×545 285 KB

It’s not clear now for me if I can join additional queries using variables, because now I’m doing an iteration over all detection IDs values with the second query. Additionally, is not clear for me if I’m doing one API query with all the IDs, or one for each ID.

I’m still trying to get this information in a better way with Grafana (for example using the Backend parser, so I can use alerting in the future).

Thanks.

Regards!

1 Like