Splunk Search

How to search JSON data for specific values?

jwalzerpitt
Influencer

I have a JSON file I am trying to search for a specific value - EventType=GoodMail - and then pull the values from another field - {}.MessageCount. I have the following search to pull back the EventType of just GoodMail:

 

index="mail_reports" 
| spath 
| mvexpand "{}.EventType" 
| search {}.EventType=GoodMail

 

But if I add this on to the end of the search:

 

| stats values "{}.MessageCount"

 

I get - "Error in 'stats' command: The argument '{}.MessageCount' is invalid."

How do I modify the search to pull back the values for {}.MessageCount'?

Thx

Labels (1)
Tags (1)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

The raw data is an array, so you need to use mvexpand.  Instead of just running spath, first extract each array element of {}.

 

| spath path={}
| mvexpand {}
| spath input={}
| where EventType == "GoodMail"
| stats values(MessageCount)

 

This will give you

values(MessageCount)
393253
96305

I'm not sure why values(MessageCount) would be useful, though.  Did you mean to add them together?  Like

 

| spath path={}
| mvexpand {}
| spath input={}
| where EventType == "GoodMail"
| stats sum(MessageCount)​​

 

sum(MessageCount)
489558

View solution in original post

Tags (1)

PickleRick
SplunkTrust
SplunkTrust

Assuming you do have such field, as this is an argument to aggregation function, you need to enclose it in parentheses

| stats values('{}.MessageCount')

jwalzerpitt
Influencer

Thx for the reply

I added the following, but the issue is I'm getting all the MessageCount values back and not just for {}.EventType=GoodMail

| stats values("{}.MessageCount")

 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Show us the full search because from what you're saying it should filter only those events you want.

0 Karma

jwalzerpitt
Influencer

JSON file data is as follows

[
    {
        "Organization":  "foo.foomail.com",
        "Date":  "2023-01-08",
        "EventType":  "GoodMail",
        "Direction":  "Inbound",
        "MessageCount":  393253,
        "Index":  0
    },
    {
        "Organization":  "foo.foomail.com",
        "Date":  "2023-01-08",
        "EventType":  "GoodMail",
        "Direction":  "Outbound",
        "MessageCount":  96305,
        "Index":  2
    },
    {
        "Organization":  "foo.foomail.com",
        "Date":  "2023-01-08",
        "EventType":  "EmailMalware",
        "Direction":  "Inbound",
        "MessageCount":  34,
        "Index":  3
    },
    {
        "Organization":  "foo.foomail.com",
        "Date":  "2023-01-08",
        "EventType":  "EmailPhish",
        "Direction":  "Inbound",
        "MessageCount":  9678,
        "Index":  4
    },
    {
        "Organization":  "foo.foomail.com",
        "Date":  "2023-01-08",
        "EventType":  "EmailPhish",
        "Direction":  "Outbound",
        "MessageCount":  111,
        "Index":  5
    },
    {
        "Organization":  "foo.foomail.com",
        "Date":  "2023-01-08",
        "EventType":  "SpamDetections",
        "Direction":  "Inbound",
        "MessageCount":  55265,
        "Index":  6
    },
    {
        "Organization":  "foo.foomail.com",
        "Date":  "2023-01-08",
        "EventType":  "SpamDetections",
        "Direction":  "Outbound",
        "MessageCount":  1155,
        "Index":  7
    },
    {
        "Organization":  "foo.foomail.com",
        "Date":  "2023-01-08",
        "EventType":  "EdgeBlockSpam",
        "Direction":  "Inbound",
        "MessageCount":  614454,
        "Index":  8
    },
    {
        "Organization":  "foo.foomail.com",
        "Date":  "2023-01-08",
        "EventType":  "TransportRules",
        "Direction":  "Inbound",
        "MessageCount":  24843,
        "Index":  9
    },
    {
        "Organization":  "foo.foomail.com",
        "Date":  "2023-01-08",
        "EventType":  "EdgeBlockSpam",
        "Direction":  "Outbound",
        "MessageCount":  722,
        "Index":  10
    },
    {
        "Organization":  "foo.foomail.com",
        "Date":  "2023-01-08",
        "EventType":  "TransportRules",
        "Direction":  "Outbound",
        "MessageCount":  37537,
        "Index":  11
    }
]
0 Karma

PickleRick
SplunkTrust
SplunkTrust

OK. That's a bit more clear. You have to understand one thing - Splunk treats events as collection of fields, it doesn't get much into the structure of json or XML. If it parses them, it "flattens" them effectively.

So if you do a "global" spath on an event, as you were doing, it will combine into one multivalued field all values of multiple instances of the same field from various sub structures or subarrays.

The only thing that you can do, as @yuanliu did is to split your json into separate strings which will then be treated as separate events because otherwise you'll just get lists of values contained in various structures within the original json structure but without any relationship between them. So your condition matching EventType="GoodMail" was correctly (as per how Splunk works) matching your whole original json structure, not - as you wanted it - a specific substructure contained therein.

 I hope I didn't make it sound too complicated 🙂

jwalzerpitt
Influencer

TYVM for the reply and information as it really helped me understand how to use spath much better. 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

The raw data is an array, so you need to use mvexpand.  Instead of just running spath, first extract each array element of {}.

 

| spath path={}
| mvexpand {}
| spath input={}
| where EventType == "GoodMail"
| stats values(MessageCount)

 

This will give you

values(MessageCount)
393253
96305

I'm not sure why values(MessageCount) would be useful, though.  Did you mean to add them together?  Like

 

| spath path={}
| mvexpand {}
| spath input={}
| where EventType == "GoodMail"
| stats sum(MessageCount)​​

 

sum(MessageCount)
489558
Tags (1)

jwalzerpitt
Influencer

TYVM for breaking this down as it's greatly appreciated.

I understand what the following two commands do more clearly:

| spath path={}
| mvexpand {}

Can you please expand on the following command?

| spath input={}

Is that simply saying to look inside the {} brackets for the data?

Thx

0 Karma

yuanliu
SplunkTrust
SplunkTrust

That is correct.  In spath command, input introduces a field to be used as source. (I which extract, multikv, and so on had a similar setting, too.)  In this context, {} has no special meaning except a string representing that field name that the previous spath command extracted.

jwalzerpitt
Influencer

TYVM for the info - greatly appreciated!

Tags (1)
0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...