Eval JSON_EXTRACT Issues

[indigo42]

All,

I'm working on extracting some key info out of an Ansible HEC collector.  I'm hoping to use json_extract stuff like run time, machine etc.

• The data shows up in Search 
• The data is formatted in proper json "tree" view and color coding in Search.
• Ansible app uses the _json source type. 

When I tried to use .

...| eval foo = json_extract(<objectname>) | table foo

I can only get it show values for the first object in the list. 

After many hours of fiddling around I decided to see if I could get json_extract to work in a simpler scenario. I decided to try out the "cities" example from the Splunk online Dovs

https://docs.splunk.com/Documentation/SCS/current/SearchReference/JSONFunctions 

I ingested the example below as a file. I did NOT use _json source type so no index field extractions we should just have the raw JSON below.

{
  "cities": [
    {
      "name": "London",
      "Bridges": [
        { "name": "Tower Bridge", "length": 801 },
        { "name": "Millennium Bridge", "length": 1066 }
      ]
    },
    {
      "name": "Venice",
      "Bridges": [
        { "name": "Rialto Bridge", "length": 157 },
        { "name": "Bridge of Sighs", "length": 36 },
        { "name": "Ponte della Paglia" }
      ]
    },
    {
      "name": "San Francisco",
      "Bridges": [
        { "name": "Golden Gate Bridge", "length": 8981 },
        { "name": "Bay Bridge", "length": 23556 }
      ]
    }
  ]
}

I then try the following statement from the Splunk Doc

...| eval extract_cities = json_extract(cities) | table extract_cities

I get nothing.

The example says I should get this below.

I'm on Splunk 8.0.6. Is this a bug?  This is the first time I've had to work with JSON on this box.

Many thanks in advance for the help.

[waechtler_amaso]

I just stumbled over the same issue.

The reason why it fails is because the eval command json_extract expects a FIELD as the first argument.

So when you run

...| eval extract_cities = json_extract(cities) | table extract_cities

it evaluates the content of a field named "cities", which does not exist. Hence no results.

Just try

...| eval extract_cities = json_extract(_raw) | table extract_cities

and it will work.

[to4kawa]

https://docs.splunk.com/Documentation/SCS/current/SearchReference/JSONFunctions

cities is not the JSON field yet, I guess.

[to4kawa]

your log is not valid json and _raw.

index=_internal |head 1| fields _raw | eval _raw="{\"ansible_check_mode\":false,\"ansible_host\":\"XXXXXXXXX\",\"ansible_playbook\":\"update.yml\",\"ansible_result\":{\"_ansible_no_log\":false,\"changed\":true,\"failed_update_count\":0,\"filtered_updates\":{},\"found_update_count\":2,\"installed_update_count\":2,\"reboot_required\":true,\"updates\":{\"a33fefeb-b078-4cde-b9b9-f3473007bef2\":{\"categories\":[\"Security Updates\",\"Windows Server 2016\"],\"id\":\"a33fefeb-b078-4cde-b9b9-f3473007bef2\",\"installed\":true,\"kb\":[4580346],\"title\":\"2020-10 Cumulative Update for Windows Server 2016 for x64-based Systems (KB4580346)\"},\"b0a743d6-025a-4ca4-a998-b019a5b176f3\":{}}}}"
| spath

Accurate path is ansible_result.updates.a33fefeb-b078-4cde-b9b9-f3473007bef2.kb{}

| rename ansible_result.updates.a33fefeb-b078-4cde-b9b9-f3473007bef2.* as *
| rename *{} as *

how about this?

[indigo42]

I copied a section out of the whole JSON, I may have missed something. 

The problem is I can't pre populate the session ID (the  b0a743d6-025a-4ca4-a998-b019a5b176f3 part is dynamically generated.  So if I were to psuedo code it, I would need something like this...

ansible_result.updates.*.*

But alas, it doesn't seem to want to let me do that. 

 Since this is a json object type I was able to find an index extracted field to wild card, so I tried this...

index=* ansible_task.action="win_updates"  ansible_host="XXXX"
| foreach ansible_result.filtered_updates*.kb* [eval flt_kb=flt_kb.'<<FIELD>>']
| table flt_kb ansible_result.filtered_updates*.kb*

 It produced all of the values but it did not concatenate them in the end. 

[indigo42]

I got something that works.

| eval upd_kb=""
| foreach ansible_result.filtered_updates.*.kb{} [eval upd_kb=upd_kb.if(upd_kb=="","",if(isnull('<<FIELD>>'),"",",")).if(isnull('<<FIELD>>'),"",'<<FIELD>>') ] 
| table upd_kb

 I modified the issue from this post  here. Their problem was just like mine except my data is all in one event, so I could simplify some things. I loved the isnull feature, so I stole it. 

Thanks so much all for helping me on this. 

I will give props to you for stearing me away from json_extract though! I was convinced that was the way to go. 

[to4kawa · Accepted solution]

index=_internal | head 1 | fields _raw _time | eval _raw="{
  \"cities\": [
    {
      \"name\": \"London\",
      \"Bridges\": [
        { \"name\": \"Tower Bridge\", \"length\": 801 },
        { \"name\": \"Millennium Bridge\", \"length\": 1066 }
      ]
    },
    {
      \"name\": \"Venice\",
      \"Bridges\": [
        { \"name\": \"Rialto Bridge\", \"length\": 157 },
        { \"name\": \"Bridge of Sighs\", \"length\": 36 },
        { \"name\": \"Ponte della Paglia\" }
      ]
    },
    {
      \"name\": \"San Francisco\",
      \"Bridges\": [
        { \"name\": \"Golden Gate Bridge\", \"length\": 8981 },
        { \"name\": \"Bay Bridge\", \"length\": 23556 }
      ]
    }
  ]
}"
| rename COMMENT as "the logic"
| spath cities{} output=cities
| stats count by cities
| spath input=cities Bridges{} output=Bridges
| mvexpand Bridges
| spath input=cities name output=city
| spath input=Bridges
| table city name length

[indigo42]

Ok This almost works but....

index=* ansible_task.action="win_updates" session="f725550b-c8f2-4d01-89e4-6a07527060d3" 
| eval win_upd_failed=spath(_raw, "ansible_result.failed_update_count") 
| eval win_upd_found=spath(_raw, "ansible_result.found_update_count")
| eval win_upd_installed=spath(_raw, "ansible_result.installed_update_count")
| eval win_upd_reboot=spath(_raw, "ansible_result.reboot_required")
| foreach ansible_result.updates*.categories{} [eval upd_cat='<<FIELD>>']
| foreach ansible_result.updates*.installed [eval upd_inst='<<FIELD>>']
| foreach ansible_result.updates*.kb{} [eval upd_kb='<<FIELD>>']
| foreach ansible_result.updates*.title [eval upd_title='<<FIELD>>']
| foreach ansible_result.filtered_updates*.filtered_reason [eval flt_rsn='<<FIELD>>']
| foreach ansible_result.filtered_updates*.categories{} [eval flt_upd_cat='<<FIELD>>']
| foreach ansible_result.filtered_updates*.kb{} [eval flt_kb='<<FIELD>>']
| foreach ansible_result.filtered_updates*.title [eval flt_title='<<FIELD>>']
| table ansible_host win_upd_failed win_upd_found win_upd_installed runtime win_upd_reboot ansible_result.found_update_count upd_inst upd_kb upd_cat upd_title flt_upd_cat flt_rsn flt_kb flt_title

But...it only show the last value, instead of multiple values for the following

| foreach ansible_result.updates*.categories{} [eval upd_cat='<<FIELD>>']
| foreach ansible_result.updates*.kb{} [eval upd_kb='<<FIELD>>']
| foreach ansible_result.filtered_updates*.categories{} [eval flt_upd_cat='<<FIELD>>']
| foreach ansible_result.filtered_updates*.kb{} [eval flt_kb='<<FIELD>>']

The rest of them show multiple values as they should. 

[indigo42]

Thanks to4kawa for the redirect to spath!

I'm trying extract fields from the json output from ansible win_update. Which as fairly deep nesting.

index=* ansible_task.action="win_updates" 
| eval win_upd_failed=spath(_raw, "ansible_result.failed_update_count") 
| eval win_upd_found=spath(_raw, "ansible_result.found_update_count")
| eval win_upd_installed=spath(_raw, "ansible_result.installed_update_count")
| eval win_upd_reboot=spath(_raw, "ansible_result.reboot_required")
| eval win_upd_reboot=spath(_raw, "ansible_result.filtered_updates.categories")
| table ansible_host win_upd_failed  win_upd_found win_upd_installed win_upd_reboot runtime

This part works well... 

Here's the next issue... I need to pull the KB:  numbers and categories: which are buried in there like this. The problem is they exist in an object that gets dynamically named. I don't need to know the object name ever. It's under updates { in the json below...

   ansible_check_mode: false
   ansible_host: XXXXXXXXX
   ansible_playbook: update.yml
   ansible_result: { [-]
     _ansible_no_log: false
     changed: true
     failed_update_count: 0
     filtered_updates: { [-]
     }
     found_update_count: 2
     installed_update_count: 2
     reboot_required: true
     updates: { [-]
       a33fefeb-b078-4cde-b9b9-f3473007bef2: { [-] <--I can't pre-populate this in path.
         categories: [ [-]
           Security Updates
           Windows Server 2016
         ]
         id: a33fefeb-b078-4cde-b9b9-f3473007bef2
         installed: true
         kb: [ [-]
           4580346
         ]
         title: 2020-10 Cumulative Update for Windows Server 2016 for x64-based Systems (KB4580346)
       }
       b0a743d6-025a-4ca4-a998-b019a5b176f3: { [+]
       }
     }
   }

I was trying to get something like this to work with no luck

spath path=ansible_result.filtered_updates.updates{}.categories{} output=foo

I can only get the categories like this by using the darn ID GUID thing. Which I can't pre populate.

index=* ansible_task.action="win_updates" 
| spath ansible_result.filtered_updates output=filtered_updates
| spath input=filtered_updates path="25ca9da5-4eba-4cc5-94b5-d25f1a70e216{}.categories{}" output=foo
| table foo

Thanks for looking into this!!!!