How to search JSON data for specific values?

[jwalzerpitt]

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

[PickleRick]

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]

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

[PickleRick]

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

[jwalzerpitt]

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
    }
]

[PickleRick]

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]

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

[yuanliu · Accepted solution]

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

[jwalzerpitt]

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

[yuanliu]

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]

TYVM for the info - greatly appreciated!