Getting Data In

Nested JSON issues with Spath

leon_r
New Member

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!

0 Karma

woodcock
Esteemed Legend

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

djrose
New Member

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!

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...