topic Re: How to search JSON data for specific values? in Splunk Search

How to search JSON data for specific values?

jwalzerpitt — Mon, 09 Jan 2023 21:41:51 GMT

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

Re: How to search JSON data for specific values?

jwalzerpitt — Mon, 09 Jan 2023 21:44:36 GMT

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

Re: How to search JSON data for specific values?

PickleRick — Mon, 09 Jan 2023 21:46:17 GMT

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

| stats values('{}.MessageCount')

Re: How to search JSON data for specific values?

jwalzerpitt — Mon, 09 Jan 2023 22:00:08 GMT

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

Re: How to search JSON data for specific values?

PickleRick — Mon, 09 Jan 2023 23:14:14 GMT

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

Re: How to search JSON data for specific values?

yuanliu — Tue, 10 Jan 2023 08:17:35 GMT

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

Re: How to search JSON data for specific values?

PickleRick — Tue, 10 Jan 2023 10:45:42 GMT

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 🙂

Re: How to search JSON data for specific values?

jwalzerpitt — Tue, 10 Jan 2023 13:57:42 GMT

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

Re: How to search JSON data for specific values?

jwalzerpitt — Tue, 10 Jan 2023 13:58:20 GMT

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

Re: How to search JSON data for specific values?

yuanliu — Wed, 11 Jan 2023 07:24:53 GMT

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.

Re: How to search JSON data for specific values?

jwalzerpitt — Wed, 11 Jan 2023 13:18:15 GMT

TYVM for the info - greatly appreciated!