topic Re: Extract Nested JSON in Splunk Search

Extract Nested JSON

kernand0 — Tue, 03 Aug 2021 14:47:09 GMT

I have events coming from an API that all have the same 10 fields. Viewing the RAW event one of the fields (detail) is quote escaped JSON (\"). The contents of the field varies and I cannot get consistent parsing via configuration files. The props.conf does already include KV_MODE = json

If I add | spath input=detail to the SPL it parses perfectly, but I need to do the parsing from the config files so I can build Datamodels. Since KV's vary across events parsing the whole detail field verses regex's on specifc KV's seems to be more efficient. I've had limited success using a regex in transforms.conf. And I think trying to use the | eval details = spath(X,Y) won't work because there are multiple keys and values.

Some sample events are below.

{"edgeName": "DVC_NAME", "enterpriseUsername": null, "event": "EDGE_NEW_DEVICE", "category": "EDGE", "id": 12345678, "segmentName": null, "severity": "NOTICE", "eventTime": "2021-08-03T13:21:31.000Z", "message": "New or updated client device 01:23:45:67:ab:ef, ip 192.168.0.100, segId 0, hostname NT_HOSTNAME, os", "detail": "{\"last_request_time\":0,\"client_mac\":\"01:23:45:67:ab:ef\",\"client_ipv4addr\":\"192.168.0.100\",\"hostname\":\"NT_HOSTNAME\",\"os_type\":0,\"os_class\":0,\"os_class_name\":\"UNKNOWN\",\"os_version\":\"\",\"device_type\":\"\",\"os_description\":\"\",\"dhcp_param_list\":\"1,3,6,15,31,33,43,44,46,47,119,121,249,252\",\"segment_id\":0}"}

{"id": 73646231, "severity": "INFO", "eventTime": "2021-08-03T06:36:31.000Z", "segmentName": null, "message": "Edge [DVC_NAME] has re-established communication with the Orchestrator", "category": "EDGE", "event": "EDGE_UP", "enterpriseUsername": null, "detail": "{\"enterpriseAlertConfigurationId\":null,\"enterpriseId\":316,\"edgeId\":8748,\"edgeName\":\"DVC_NAME\",\"state\":\"PENDING\",\"stateSetTime\":\"2021-08-03T06:36:30.867Z\",\"triggerTime\":\"2021-08-03T06:36:30.867Z\",\"remainingNotifications\":1,\"nextNotificationTime\":\"2021-08-03T06:36:30.867Z\",\"lastContact\":\"2021-08-03T06:36:29.000Z\",\"name\":\"EDGE_UP\",\"type\":\"EDGE_UP\",\"firstNotificationSeconds\":0,\"maxNotifications\":1,\"notificationIntervalSeconds\":120,\"resetIntervalSeconds\":3600,\"timezone\":\"America/Phoenix\",\"locale\":null}", "edgeName": "DVC_NAME"}

{"edgeName": "DVC_NAME", "id": 73579676, "eventTime": "2021-08-02T23:24:58.000Z", "event": "MGD_CONF_APPLIED", "severity": "INFO", "segmentName": null, "enterpriseUsername": null, "detail": "{\"heartBeatSeconds\": 30, \"managementPlaneProxy\": {\"drHeartbeatSecs\": 60, \"primary\": \"host-1.domain.net\", \"secondary\": \"host-2.domain.net\"}, \"timeSliceSeconds\": 300, \"statsUploadSeconds\": 300}", "message": "Applied new configuration for managementPlane version 1627946184323", "category": "EDGE"}

Re: Extract Nested JSON

venkatasri — Wed, 04 Aug 2021 01:17:18 GMT

Hi @kernand0

that's good when its working with spath, where did you set KV_MODE = json ? Props having KV_MODE shall be deployed to Search head.

Re: Extract Nested JSON

kernand0 — Wed, 04 Aug 2021 04:49:30 GMT

Yes. KV_MODE = json is set on the search head.

Re: Extract Nested JSON

venkatasri — Wed, 04 Aug 2021 05:35:16 GMT

@kernand0 Can you run a btool and check what props have been considered on SH?

./splunk btool props list --debug | grep <your_sourcetype_here>

Re: Extract Nested JSON

kernand0 — Thu, 05 Aug 2021 13:26:38 GMT

The host is an AIO Splunk instance but here is the output of btool for props.conf:

[velocloud:api]
ADD_EXTRA_TIME_FIELDS = True
ANNOTATE_PUNCT = True
AUTO_KV_JSON = true
BREAK_ONLY_BEFORE =
BREAK_ONLY_BEFORE_DATE = True
CHARSET = UTF-8
DATETIME_CONFIG = /etc/datetime.xml
DEPTH_LIMIT = 1000
EVAL action =
HEADER_MODE =
KV_MODE = JSON
LEARN_MODEL = true
LEARN_SOURCETYPE = true
LINE_BREAKER_LOOKBEHIND = 100
MATCH_LIMIT = 100000
MAX_DAYS_AGO = 2000
MAX_DAYS_HENCE = 2
MAX_DIFF_SECS_AGO = 3600
MAX_DIFF_SECS_HENCE = 604800
MAX_EVENTS = 256
MAX_TIMESTAMP_LOOKAHEAD = 128
MUST_BREAK_AFTER =
MUST_NOT_BREAK_AFTER =
MUST_NOT_BREAK_BEFORE =
SEGMENTATION = indexing
SEGMENTATION-all = full
SEGMENTATION-inner = inner
SEGMENTATION-outer = outer
SEGMENTATION-raw = none
SEGMENTATION-standard = standard
SHOULD_LINEMERGE = True
TIME_FORMAT = %Y-%m-%dT%H:%M:%S%Z
TIME_PREFIX = \"eventTime\"\s*\:\s*\"
TRANSFORMS =
TRANSFORMS-core = velocloud_host
TRUNCATE = 10000
TZ = UTC
detect_trailing_nulls = false
maxDist = 100
priority =
sourcetype =