topic Re: Nested JSON issues with Spath in Getting Data In

Nested JSON issues with Spath

leon_r — Thu, 22 Aug 2019 14:43:30 GMT

Hi,
First time poster also fairly new to splunk though I am fluent in Python and decent at sql so apologies if this post is awkward.

I am dealing with json that looks like this:

 Actor: [   [-] 
    {   [-] 
     ID:     8f71273c-c502-4a39-9607-6b272c9df  
     Type:   0  
    }   
    {   [-] 
     ID:    email@myemail.com   
     Type:   5  
    }   
    {   [-] 
     ID:     1003200038F18F0E   
     Type:   3  
    }   
]

I am trying to dig out the email with spath (first time using this) and i am not getting the results I want. Also getting weird results (for example there are 3 fields and if I use something like 100 it still returns data.

Here is my code any help would be super appreciated.

This code works but does not do what i want (it produces 3 events since it is just taking id). I just want email and I dont want to use a regex I just want to dig deeper into the json

index=mine Workload=AzureActiveDirectory ResultStatus=Succeeded Operation=UserLoggedIn 
| bucket span=30s _time
| stats count dc(src) as mycount by "Actor{}.ID" 
| where mycount>=3 
| sort mycount desc

Here is the code that is not working that I want to work

index=mine Workload=AzureActiveDirectory ResultStatus=Succeeded Operation=UserLoggedIn 
| bucket span=30s _time 
| spath output=leon path=Actor{}.ID{1}
| stats count dc(src) as mycount by "Actor{}.ID{1}" 
| where mycount>=3 
| sort mycount desc

This returns no events.

I have played with various forms of .ID and it never works.

Please help!

Re: Nested JSON issues with Spath

djrose — Thu, 22 Aug 2019 17:51:01 GMT

Hey bud!
This is under the assumption that you're trying to get the count of logins by distinct source

index=your_index Workload=your_workload ResultStatus=Succeeded Operation=UserLoggedIn 
| spath
| bucket span=30s _time
| rename Actor{}.ID AS "Email", Actor{}.Type AS "Type"
| eval temp=mvzip(Email,Type)
| mvexpand temp
| eval Email=mvindex(split(temp,","),0)
| stats count(Email) AS logincount BY Email src _time 
| search (logincount >= 3 AND Email=*@*)
| table Email src logincount _time

Hope this helps!

Re: Nested JSON issues with Spath

woodcock — Tue, 17 Sep 2019 21:30:24 GMT

The JSON that you posted is not valid so Splunk will not recognize it as JSON so first fix that (perhaps it is a cut/paste/post mistake on your part). This parses for me:

| makeresults 
| eval _raw = "{
    \"Actor\": [{
        \"ID\": \"8 f71273c - c502 - 4 a39 - 9607 - 6 b272c9df\",
        \"Type\": 0
    }, {
        \"ID\": \"email@myemail.com\",
        \"Type\": 5
    }, {
        \"ID\": \"1003200038 F18F0E\",
        \"Type\": 3
    }]
}"
| spath

Then try this:

index=mine Workload=AzureActiveDirectory ResultStatus=Succeeded Operation=UserLoggedIn 
| spath
| eval email=mvindex('Actor{}.ID', 1)
| stats count dc(src) AS mycount BY email
| where mycount>=3 
| sort 0 - mycount