topic Re: Field Extraction from Complex json files in Splunk Search

Help with Field Extraction from Complex json files

SplunkDash — Tue, 23 Aug 2022 13:42:27 GMT

Hello,

is there any way we can extract fields from this sample data, any help will be highly appreciated.

Thank you!

2022-07-22 17:21:50 - { "type" : "core", "r/o" : false, "booting" : true, "version" : "7.2.9.GA", "user" : "anonymous", "domainUUID" : null, "access" : null, "remote-address" : null, "success" : true, "ops" : [ { "operation" : "add", "address" : [{ "system-property" : "dstest.tx.node.id" }], "value" : "vp2mbg_c001_r3050" }, { "operation" : "add", "address" : [{ "system-property" : "jdk.tls.client.protocols" }], "value" : "TLSv1.2" }, { "operation" : "add", "address" : [{ "system-property" : "org.apache.coyote.ajp.DEFAULT_CONNECTION_TIMEOUT" }], "value" : "600000" }, { "operation" : "add", "address" : [{ "system-property" : "org.apache.coyote.ajp.MAX_PACKET_SIZE" }], "value" : "65536" }, { "operation" : "add", "address" : [{ "system-property" : "javax.net.ssl.trustStore" }], "value" : "/opt/app/dstest/ssl/cacerts.jks" }, { "operation" : "add", "address" : [{ "system-property" : "javax.net.ssl.trustStorePassword" }], "value" : { "EXPRESSION_VALUE" : "${VAULT::vb::truststorepass::1}" } }, { "operation" : "add", "address" : [{ "system-property" : "javax.net.ssl.keyStore" }], "value" : "/opt/app/DSTest/ssl/tccs-proddr.keystore" }, { "operation" : "add", "address" : [{ "system-property" : "javax.net.ssl.keyStorePassword" }], "value" : { "EXPRESSION_VALUE" : "${VAULT::vb::certpass::1}" } }, { "operation" : "add", "address" : [{ "system-property" : "tcp.allow.dev.esa.token" }], "value" : "true" }, { "operation" : "add", "address" : [{ "system-property" : "tccs.allow.dev.esa.token" }], "value" : "true" }, { "operation" : "add", "address" : [{ "system-property" : "CLAS.ENVIRONMENT" }], "value" : "prod" }, { "operation" : "add", "address" : [{ "system-property" : "TCCS.ENVIRONMENT" }], "value" : "prod" }, { "operation" : "add", "address" : [{ "system-property" : "agent.user" }], "value" : { "EXPRESSION_VALUE" : "${VAULT::vb::agentuser::1}" } }, { "operation" : "add", "address" : [{ "system-property" : "agent.password" }], "value" : { "EXPRESSION_VALUE" : "${VAULT::vb::agentpass::1}" } }, { "address" : [{ "path" : "DSTest.server.ADCredStore.dir" }], "operation" : "add", "path" : "/opt/app/DSTest/profiles/instances/tccs/ADCredStore" }, { "address" : [{ "path" : "DSTest.ssl" }], "operation" : "add", "path" : "/opt/app/DSTest/ssl" }, { "address" : [{ "core-service" : "vault" }], "operation" : "add", "vault-options" : [ { "KEYSTORE_URL" : "/opt/app/DSTest/profiles/instances/tccs/configuration/eap7vault.keystore" }, { "KEYSTORE_PASSWORD" : "MASK-0dF/GimhesRBlxgjOeSNqf" }, { "KEYSTORE_ALIAS" : "vault" }, { "SALT" : "147asa2900" }, { "ITERATION_COUNT" : "8" }, { "ENC_FILE_DIR" : "/opt/app/DSTest/profiles/instances/tccs/configuration/" } ] }] }

Re: Field Extraction from Complex json files

bowesmana — Tue, 23 Aug 2022 04:50:10 GMT

If the part following the date/time is good JSON, then you can do this - this search uses your data

| makeresults | eval _raw="2022-07-22 17:21:50 - { \"type\" : \"core\", \"r/o\" : false, \"booting\" : true, \"version\" : \"7.2.9.GA\", \"user\" : \"anonymous\", \"domainUUID\" : null, \"access\" : null, \"remote-address\" : null, \"success\" : true, \"ops\" : [ { \"operation\" : \"add\", \"address\" : [{ \"system-property\" : \"dstest.tx.node.id\" }], \"value\" : \"vp2mbg_c001_r3050\" }, { \"operation\" : \"add\", \"address\" : [{ \"system-property\" : \"jdk.tls.client.protocols\" }], \"value\" : \"TLSv1.2\" }, { \"operation\" : \"add\", \"address\" : [{ \"system-property\" : \"org.apache.coyote.ajp.DEFAULT_CONNECTION_TIMEOUT\" }], \"value\" : \"600000\" }, { \"operation\" : \"add\", \"address\" : [{ \"system-property\" : \"org.apache.coyote.ajp.MAX_PACKET_SIZE\" }], \"value\" : \"65536\" }, { \"operation\" : \"add\", \"address\" : [{ \"system-property\" : \"javax.net.ssl.trustStore\" }], \"value\" : \"/opt/app/dstest/ssl/cacerts.jks\" }, { \"operation\" : \"add\", \"address\" : [{ \"system-property\" : \"javax.net.ssl.trustStorePassword\" }], \"value\" : { \"EXPRESSION_VALUE\" : \"${VAULT::vb::truststorepass::1}\" } }, { \"operation\" : \"add\", \"address\" : [{ \"system-property\" : \"javax.net.ssl.keyStore\" }], \"value\" : \"/opt/app/DSTest/ssl/tccs-proddr.keystore\" }, { \"operation\" : \"add\", \"address\" : [{ \"system-property\" : \"javax.net.ssl.keyStorePassword\" }], \"value\" : { \"EXPRESSION_VALUE\" : \"${VAULT::vb::certpass::1}\" } }, { \"operation\" : \"add\", \"address\" : [{ \"system-property\" : \"tcp.allow.dev.esa.token\" }], \"value\" : \"true\" }, { \"operation\" : \"add\", \"address\" : [{ \"system-property\" : \"tccs.allow.dev.esa.token\" }], \"value\" : \"true\" }, { \"operation\" : \"add\", \"address\" : [{ \"system-property\" : \"CLAS.ENVIRONMENT\" }], \"value\" : \"prod\" }, { \"operation\" : \"add\", \"address\" : [{ \"system-property\" : \"TCCS.ENVIRONMENT\" }], \"value\" : \"prod\" }, { \"operation\" : \"add\", \"address\" : [{ \"system-property\" : \"agent.user\" }], \"value\" : { \"EXPRESSION_VALUE\" : \"${VAULT::vb::agentuser::1}\" } }, { \"operation\" : \"add\", \"address\" : [{ \"system-property\" : \"agent.password\" }], \"value\" : { \"EXPRESSION_VALUE\" : \"${VAULT::vb::agentpass::1}\" } }, { \"address\" : [{ \"path\" : \"DSTest.server.ADCredStore.dir\" }], \"operation\" : \"add\", \"path\" : \"/opt/app/DSTest/profiles/instances/tccs/ADCredStore\" }, { \"address\" : [{ \"path\" : \"DSTest.ssl\" }], \"operation\" : \"add\", \"path\" : \"/opt/app/DSTest/ssl\" }, { \"address\" : [{ \"core-service\" : \"vault\" }], \"operation\" : \"add\", \"vault-options\" : [ { \"KEYSTORE_URL\" : \"/opt/app/DSTest/profiles/instances/tccs/configuration/eap7vault.keystore\" }, { \"KEYSTORE_PASSWORD\" : \"MASK-0dF/GimhesRBlxgjOeSNqf\" }, { \"KEYSTORE_ALIAS\" : \"vault\" }, { \"SALT\" : \"147asa2900\" }, { \"ITERATION_COUNT\" : \"8\" }, { \"ENC_FILE_DIR\" : \"/opt/app/DSTest/profiles/instances/tccs/configuration/\" } ] }] }" | rex "[^\{]*(?<json>.*)" | spath input=json

It uses rex to make a field called json with the raw JSON in it, then spath to parse the JSON.

Re: Field Extraction from Complex json files

SplunkDash — Tue, 23 Aug 2022 04:53:20 GMT

Hello,

Thank you so much for your quick response. Is there any way I can extract fields using props.conf /transforms.conf files?

Re: Field Extraction from Complex json files

bowesmana — Tue, 23 Aug 2022 05:03:28 GMT

Yes, you can but I am not sure of the 'correct' way to do it

See https://community.splunk.com/t5/Splunk-Search/How-to-use-spath-command-in-props-conf-or-transforms-conf/m-p/347952

where someone else has a similar issue or maybe these legends can help @ITWhisperer @richgalloway @PickleRick @yuanliu

Re: Field Extraction from Complex json files

SplunkDash — Tue, 23 Aug 2022 05:11:30 GMT

Thank you so much again. But how can I reach out to them?

Re: Field Extraction from Complex json files

yuanliu — Tue, 23 Aug 2022 06:43:22 GMT

I do not believe that you can combine the two steps into props.conf. You best bet is to ask the developer who wrote the log files to change the format to pure, conformant JSON, e.g.,

{"timestamp" : "2022-07-22 17:21:50", "type" : "core", "r/o" : false, "booting" : true, "version" : "7.2.9.GA", "user" : "anonymous", "domainUUID" : null, "access" : null, "remote-address" : null, "success" : true, "ops" : [ { "operation" : "add", "address" : [{ "system-property" : "dstest.tx.node.id" }], "value" : "vp2mbg_c001_r3050" }, { "operation" : "add", "address" : [{ "system-property" : "jdk.tls.client.protocols" }], "value" : "TLSv1.2" }, { "operation" : "add", "address" : [{ "system-property" : "org.apache.coyote.ajp.DEFAULT_CONNECTION_TIMEOUT" }], "value" : "600000" }, { "operation" : "add", "address" : [{ "system-property" : "org.apache.coyote.ajp.MAX_PACKET_SIZE" }], "value" : "65536" }, { "operation" : "add", "address" : [{ "system-property" : "javax.net.ssl.trustStore" }], "value" : "/opt/app/dstest/ssl/cacerts.jks" }, { "operation" : "add", "address" : [{ "system-property" : "javax.net.ssl.trustStorePassword" }], "value" : { "EXPRESSION_VALUE" : "${VAULT::vb::truststorepass::1}" } }, { "operation" : "add", "address" : [{ "system-property" : "javax.net.ssl.keyStore" }], "value" : "/opt/app/DSTest/ssl/tccs-proddr.keystore" }, { "operation" : "add", "address" : [{ "system-property" : "javax.net.ssl.keyStorePassword" }], "value" : { "EXPRESSION_VALUE" : "${VAULT::vb::certpass::1}" } }, { "operation" : "add", "address" : [{ "system-property" : "tcp.allow.dev.esa.token" }], "value" : "true" }, { "operation" : "add", "address" : [{ "system-property" : "tccs.allow.dev.esa.token" }], "value" : "true" }, { "operation" : "add", "address" : [{ "system-property" : "CLAS.ENVIRONMENT" }], "value" : "prod" }, { "operation" : "add", "address" : [{ "system-property" : "TCCS.ENVIRONMENT" }], "value" : "prod" }, { "operation" : "add", "address" : [{ "system-property" : "agent.user" }], "value" : { "EXPRESSION_VALUE" : "${VAULT::vb::agentuser::1}" } }, { "operation" : "add", "address" : [{ "system-property" : "agent.password" }], "value" : { "EXPRESSION_VALUE" : "${VAULT::vb::agentpass::1}" } }, { "address" : [{ "path" : "DSTest.server.ADCredStore.dir" }], "operation" : "add", "path" : "/opt/app/DSTest/profiles/instances/tccs/ADCredStore" }, { "address" : [{ "path" : "DSTest.ssl" }], "operation" : "add", "path" : "/opt/app/DSTest/ssl" }, { "address" : [{ "core-service" : "vault" }], "operation" : "add", "vault-options" : [ { "KEYSTORE_URL" : "/opt/app/DSTest/profiles/instances/tccs/configuration/eap7vault.keystore" }, { "KEYSTORE_PASSWORD" : "MASK-0dF/GimhesRBlxgjOeSNqf" }, { "KEYSTORE_ALIAS" : "vault" }, { "SALT" : "147asa2900" }, { "ITERATION_COUNT" : "8" }, { "ENC_FILE_DIR" : "/opt/app/DSTest/profiles/instances/tccs/configuration/" } ] }] }

If they can do this, Splunk will automatically extract fields if you tell it to use JSON data type (INDEXED_EXTRACTIONS = json), or you can ask it not to use JSON type, but to extract JSON data at search time (KV_MODE=json)

Re: Field Extraction from Complex json files

PickleRick — Tue, 23 Aug 2022 07:34:17 GMT

At the moment, at least that's what I found during my research (I needed that as well), you can't tell Splunk to use only part of the message for structured extractions. It's a shame, really, because it's often that the events do contain some part of non-structured (or "human-structured") header and then a json or xml part at the end.

Unfortunately, the only thing you can do to automatically extract the json/xml/whatever part is use transform to cut the non-structured part from the event. Unfortunately, doing so you're obviously losing data from the cut part. So there's no good solution for that (short of extracting that data first into indexed fields but that's another story and very "non-splunky" way)