topic Re: JSON to table with multi-value fields. in Getting Data In

JSON to table with multi-value fields.

changux — Thu, 03 Aug 2017 03:22:48 GMT

Hi, all.

I have a REST input configured and ingesting long events in JSON format. A sample event can be looked here.

Splunk created the fields, I have:

Some objects in the JSON file are multi-value, like:

{
    "error": 0,
    "data": {
        "totalItems": 1,
        "items": [
            {
                "status": "enabled",
                "pci": [
                    "10.2.7",
                    "10.6.1"
                ],
                "description": "Crontab entry changed.",
                "file": "0020-syslog_rules.xml",
                "level": 5,
                "path": "/var/ossec/ruleset/rules",
                "groups": [
                    "syslog",
                    "cron"
                ],
                "id": 2832,
                "details": {
                    "if_sid": "2830",
                    "match": "REPLACE"
                }
            }
        ]
    }
}

Where for example, groups and pci objects have multiple values. I want to present a table with some columns including those columns (data.items{}.id as id, data.items{}.file as file, data.items{}.description as desc, data.items{}.groups{} as groups, data.items{}.pci{} as pci, data.items{}.level as level) with no look until now. In summary, i need.

Create a table with the fields mentioned.
Include all the multi-value data inside their respective field on the table.

By now, I am spending time with something like:

index="wpi" sourcetype="wrules" | head 1 | spath | rename data.items{}.id as id, data.items{}.file as file, data.items{}.description as desc, data.items{}.groups{} as groups, data.items{}.pci{} as pci, data.items{}.level as level |eval x=mvzip(file,mvzip(desc,mvzip(groups,mvzip(pci,level)))) | table file,desc,groups,pci,level

But in cases with multi-value, I only have the first element and not all.

Please help me!

Re: JSON to table with multi-value fields.

niketn — Thu, 03 Aug 2017 08:20:33 GMT

@changux, you have only two fields groups and pci. Since other fields are single value value, if you stitch them together using mvzip(), it will retain only one value from all the fields.

Try the following run anywhere search using mvzip() only on multi-valued fields and then mvexpand command to convert them to single value, followed by split()to get the values of groups and pci

| makeresults
| eval jsonData = "
 {
     \"error\": 0,
     \"data\": {
         \"totalItems\": 1,
         \"items\": [
             {
                 \"status\": \"enabled\",
                 \"pci\": [
                     \"10.2.7\",
                     \"10.6.1\"
                 ],
                 \"description\": \"Crontab entry changed.\",
                 \"file\": \"0020-syslog_rules.xml\",
                 \"level\": 5,
                 \"path\": \"/var/ossec/ruleset/rules\",
                 \"groups\": [
                     \"syslog\",
                     \"cron\"
                 ],
                 \"id\": 2832,
                 \"details\": {
                     \"if_sid\": \"2830\",
                     \"match\": \"REPLACE\"
                 }
             }
         ]
     }
 }
"
| spath input=jsonData path="data.items{}.id" output=id
| spath input=jsonData path="data.items{}.file" output=file
| spath input=jsonData path="data.items{}.description" output=desc
| spath input=jsonData path="data.items{}.groups{}" output=groups
| spath input=jsonData path="data.items{}.pci{}" output=pci
| spath input=jsonData path="data.items{}.level" output=level
| eval MVFields=mvzip(groups,pci)
| mvexpand MVFields
| eval MVFields=split(MVFields,",")
| eval groups=mvindex(MVFields,0)
| eval pci=mvindex(MVFields,1)
| table id file desc groups pci level

PS: Commands until spath are to mimic sample data and fields. You will require commands from mvzip() onward.

Re: JSON to table with multi-value fields.

changux — Thu, 03 Aug 2017 16:38:26 GMT

Thank you so much for your help. I ran:

index="wapi" sourcetype="wrules" | head 1 | spath  path="data.items{}.id" output=dataid
 | spath path="data.items{}.file" output=file
 | spath path="data.items{}.description" output=desc
 | spath path="data.items{}.groups{}" output=groups
 | spath path="data.items{}.pci{}" output=pci
 | spath path="data.items{}.level" output=level
 | eval x=mvzip(groups,pci)
 | mvexpand x
 | eval x=split(x,",")
 | eval groups=mvindex(x,0)
 | eval pci=mvindex(x,1)
 | table dataid file desc groups pci level

Without luck. Check result:

Any suggestion? In the other hand, the search takes a long time to show the result.

Thank you again, I appreciate your help.

Re: JSON to table with multi-value fields.

changux — Thu, 03 Aug 2017 19:04:55 GMT

More comments:

Without pci and groups fields, the table is correct:

index="wapi" sourcetype="wrules" | head 1 | spath  path="data.items{}.id" output=dataid
 | spath path="data.items{}.file" output=file
 | spath path="data.items{}.description" output=desc
 | spath path="data.items{}.level" output=level
  | spath path="data.items{}.pci{}" output=pci
 | table dataid file desc level

When I try with PCI or groups, the values don't correspond to JSON value (multi valued fields).

Re: JSON to table with multi-value fields.

woodcock — Thu, 03 Aug 2017 20:23:59 GMT

The "event" that you liked in your OP is not actually a single event; it is 1828 events ("items"). If you put it in as a single event, then I can't help you. If you put it in as multiple events broken on each "item" then we can work. Which is it?

Re: JSON to table with multi-value fields.

woodcock — Thu, 03 Aug 2017 20:28:40 GMT

What is wrong with this?

| makeresults
 | eval jsonData = "
  {
      \"error\": 0,
      \"data\": {
          \"totalItems\": 1,
          \"items\": [
              {
                  \"status\": \"enabled\",
                  \"pci\": [
                      \"10.2.7\",
                      \"10.6.1\"
                  ],
                  \"description\": \"Crontab entry changed.\",
                  \"file\": \"0020-syslog_rules.xml\",
                  \"level\": 5,
                  \"path\": \"/var/ossec/ruleset/rules\",
                  \"groups\": [
                      \"syslog\",
                      \"cron\"
                  ],
                  \"id\": 2832,
                  \"details\": {
                      \"if_sid\": \"2830\",
                      \"match\": \"REPLACE\"
                  }
              }
          ]
      }
  }"

| rename COMMENT AS "Everything above generates sample data; everything below is your solution"

| spath input=jsonData path="data.items{}.id" output=id
| spath input=jsonData path="data.items{}.file" output=file
| spath input=jsonData path="data.items{}.description" output=desc
| spath input=jsonData path="data.items{}.groups{}" output=groups
| spath input=jsonData path="data.items{}.pci{}" output=pci
| spath input=jsonData path="data.items{}.level" output=level
| table id file desc groups pci level

Re: JSON to table with multi-value fields.

changux — Thu, 03 Aug 2017 21:01:44 GMT

With your example, works perfect, but, when I tried with my ingested data, doesn't work well.

Sample event.

My query:

index="wazuh_api"  sourcetype="wazuh_api_rules" | head 1 
 | spath input=_raw path="data.items{}.id" output=id
 | spath input=_raw path="data.items{}.file" output=file
 | spath input=_raw path="data.items{}.description" output=desc
 | spath input=_raw path="data.items{}.groups{}" output=groups
 | spath input=_raw path="data.items{}.pci{}" output=pci
 | spath input=_raw path="data.items{}.level" output=level
 | table id file desc groups pci level

The result.

Re: JSON to table with multi-value fields.

changux — Thu, 03 Aug 2017 21:06:24 GMT

My propose is to have a table with correct PCI and group fields on each line, as in your example but with all records. Please help!

Re: JSON to table with multi-value fields.

changux — Thu, 03 Aug 2017 21:31:08 GMT

Sounds great. How can I do?

Re: JSON to table with multi-value fields.

niketn — Fri, 04 Aug 2017 05:50:01 GMT

Is the number of results in pci and groups going to remain the same? Based on the question description "But in cases with multi-value, I only have the first element and not all.", I was thinking other fields are not multi-valued. But seems like they are.

Let me convert my answer to comment and have a look at the sample data you have provided,

Re: JSON to table with multi-value fields.

changux — Fri, 04 Aug 2017 06:38:42 GMT

Thank you!