Getting Data In

JSON to table with multi-value fields.

changux
Builder

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:

alt text

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.

  1. Create a table with the fields mentioned.
  2. 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.

alt text

Please help me!

0 Karma

woodcock
Esteemed Legend

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
0 Karma

changux
Builder

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.

0 Karma

changux
Builder

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!

0 Karma

woodcock
Esteemed Legend

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?

0 Karma

changux
Builder

Sounds great. How can I do?

0 Karma

niketn
Legend

@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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

changux
Builder

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:

alt text

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

Thank you again, I appreciate your help.

0 Karma

changux
Builder

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).

0 Karma

niketn
Legend

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,

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

changux
Builder

Thank you!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...