topic Re: Searching nested JSON to create audit dashboard in Splunk Search

Searching nested JSON to create audit dashboard

Tylerdygert — Mon, 02 Dec 2019 21:54:17 GMT

I have some test JSON data that I am having trouble searching for.
I need to create some Audit dashboards around this data and trying to find a way to search the field names has been pretty difficult. Any help would be appreciated.

I would like to be able to search something like this: | search PatientName= and addressLine1=

Here is one event that I have in splunk fully opened up:
{"Results":[{"Username":"Org FinAdmin","EntityName":"EPMS.Domain.Entities.Account","DateTime":"2019-12-02T19:03:48.1452368Z","EntityID":"200000032","ParentEntity":"","ParentEntityID":"0","ChangeType":"Modified","ChangeDetails":[{"FieldName":"AccountGroupId","OldValue":"0","NewValue":"0","$type":"AuditChangeDetail"},{"FieldName":"AccountTypeId","OldValue":"132","NewValue":"132","$type":"AuditChangeDetail"},{"FieldName":"BalanceDue","OldValue":"0","NewValue":"0","$type":"AuditChangeDetail"},{"FieldName":"BalanceDueLate120","OldValue":"0","NewValue":"0","$type":"AuditChangeDetail"},{"FieldName":"BalanceDueLate150","OldValue":"0","NewValue":"0","$type":"AuditChangeDetail"},{"FieldName":"BalanceDueLate30","OldValue":"0","NewValue":"0","$type":"AuditChangeDetail"},{"FieldName":"BalanceDueLate60","OldValue":"0","NewValue":"0","$type":"AuditChangeDetail"},{"FieldName":"BalanceDueLate90","OldValue":"0","NewValue":"0","$type":"AuditChangeDetail"},{"FieldName":"BalanceDueLateMax","OldValue":"0","NewValue":"0","$type":"AuditChangeDetail"},{"FieldName":"CreatedByProgram","OldValue":"epmsApplication","NewValue":"epmsApplication","$type":"AuditChangeDetail"},{"FieldName":"CreatedByUser","OldValue":"Org FinAdmin","NewValue":"Org FinAdmin","$type":"AuditChangeDetail"},{"FieldName":"CreatedDateTime","OldValue":"12/2/2019 7:03:47 PM","NewValue":"12/2/2019 7:03:47 PM","$type":"AuditChangeDetail"},{"FieldName":"FinancialClassId","OldValue":"0","NewValue":"0","$type":"AuditChangeDetail"},{"FieldName":"GuarantorId","OldValue":"21737061","NewValue":"21737061","$type":"AuditChangeDetail"},{"FieldName":"GuarantorName","OldValue":"","NewValue":"","$type":"AuditChangeDetail"},{"FieldName":"IsAssessFinanceCharge","OldValue":" ","NewValue":" ","$type":"AuditChangeDetail"},{"FieldName":"IsNewAccount","OldValue":" ","NewValue":" ","$type":"AuditChangeDetail"},{"FieldName":"IsPatient","OldValue":"True","NewValue":"True","$type":"AuditChangeDetail"},{"FieldName":"IsSendNewsLetter","OldValue":" ","NewValue":" ","$type":"AuditChangeDetail"},{"FieldName":"LastChargeDate","OldValue":"1/1/1940 12:00:00 AM","NewValue":"1/1/1940 12:00:00 AM","$type":"AuditChangeDetail"},{"FieldName":"LastInsurancePayment","OldValue":"1/1/1940 12:00:00 AM","NewValue":"1/1/1940 12:00:00 AM","$type":"AuditChangeDetail"},{"FieldName":"LastPaymentDate","OldValue":"1/1/1940 12:00:00 AM","NewValue":"1/1/1940 12:00:00 AM","$type":"AuditChangeDetail"},{"FieldName":"LastStatementDate","OldValue":"1/1/1940 12:00:00 AM","NewValue":"1/1/1940 12:00:00 AM","$type":"AuditChangeDetail"},{"FieldName":"MigratedOn","OldValue":null,"NewValue":null,"$type":"AuditChangeDetail"},{"FieldName":"ModifiedByProgram","OldValue":"epmsApplication","NewValue":"epmsApplication","$type":"AuditChangeDetail"},{"FieldName":"ModifiedByUser","OldValue":"Org FinAdmin","NewValue":"Org FinAdmin","$type":"AuditChangeDetail"},{"FieldName":"ModifiedDateTime","OldValue":"12/2/2019 7:03:47 PM","NewValue":"12/2/2019 7:03:48 PM","$type":"AuditChangeDetail"},{"FieldName":"MonthToDateCharges","OldValue":"0","NewValue":"0","$type":"AuditChangeDetail"},{"FieldName":"MonthToDateDirectPayments","OldValue":"0","NewValue":"0","$type":"AuditChangeDetail"},{"FieldName":"MonthToDatePayments","OldValue":"0","NewValue":"0","$type":"AuditChangeDetail"},{"FieldName":"NoteChanged","OldValue":"1/1/1940 12:00:00 AM","NewValue":"1/1/1940 12:00:00 AM","$type":"AuditChangeDetail"},{"FieldName":"RecordNotesId","OldValue":"0","NewValue":"0","$type":"AuditChangeDetail"},{"FieldName":"RecordStatus","OldValue":" ","NewValue":" ","$type":"AuditChangeDetail"},{"FieldName":"RecordStatusChangeDate","OldValue":"1/1/1940 12:00:00 AM","NewValue":"1/1/1940 12:00:00 AM","$type":"AuditChangeDetail"},{"FieldName":"ReferenceCodeId","OldValue":"0","NewValue":"0","$type":"AuditChangeDetail"},{"FieldName":"ResidenceName","OldValue":"","NewValue":"","$type":"AuditChangeDetail"},{"FieldName":"ResidentPersonCode","OldValue":"0","NewValue":"0","$type":"AuditChangeDetail"},{"FieldName":"SeparateStatementId","OldValue":"0","NewValue":"0","$type":"AuditChangeDetail"},{"FieldName":"StatusId","OldValue":"1","NewValue":"1","$type":"AuditChangeDetail"}],"$type":"Auditable"}],"$type":"AuditResults"}

Re: Searching nested JSON to create audit dashboard

aberkow — Wed, 30 Sep 2020 03:13:36 GMT

Hi - have you tried leveraging the spath command? https://docs.splunk.com/Documentation/SplunkCloud/latest/SearchReference/Spath it's used pretty exclusively for this purpose. Take a look at the example below:

{code}
| makeresults count=1
| eval _raw="{\"Results\":[{\"Username\":\"Org FinAdmin\",\"EntityName\":\"EPMS.Domain.Entities.Account\",\"DateTime\":\"2019-12-02T19:03:48.1452368Z\",\"EntityID\":\"200000032\",\"ParentEntity\":\"\",\"ParentEntityID\":\"0\",\"ChangeType\":\"Modified\",\"ChangeDetails\":[{\"FieldName\":\"AccountGroupId\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"AccountTypeId\",\"OldValue\":\"132\",\"NewValue\":\"132\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"BalanceDue\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"BalanceDueLate120\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"}"
| spath input=_raw
{code}

In this example, I took most of your JSON object, escaped the quotes so I can query it, made a result to make Splunk happy, and then just ran the spath command specifying the input to be the entire object. You can also specify the output, and a specific path based on the docs I linked above. The command resulted in a row of each of your objects parsed into multivalue fields reflecting what I think you're looking for.

If you think specific information should be "tied together" (as in some multivalue fields should actually be single values while maintaining the single values of the rest of the entries), I would suggest exploring the mvzip function on the eval command https://docs.splunk.com/Documentation/Splunk/8.0.0/SearchReference/MultivalueEvalFunctions#mvzip.28X.2CY.2C.22Z.22.29 along with the mvexpand command https://docs.splunk.com/Documentation/Splunk/8.0.0/SearchReference/Mvexpand to zip together those single values and then expand them.

Hope this helps!

Re: Searching nested JSON to create audit dashboard

to4kawa — Tue, 03 Dec 2019 12:30:57 GMT

| makeresults 
| eval _raw="{\"Results\":[{\"Username\":\"Org FinAdmin\",\"EntityName\":\"EPMS.Domain.Entities.Account\",\"DateTime\":\"2019-12-02T19:03:48.1452368Z\",\"EntityID\":\"200000032\",\"ParentEntity\":\"\",\"ParentEntityID\":\"0\",\"ChangeType\":\"Modified\",\"ChangeDetails\":[{\"FieldName\":\"AccountGroupId\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"AccountTypeId\",\"OldValue\":\"132\",\"NewValue\":\"132\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"BalanceDue\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"BalanceDueLate120\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"BalanceDueLate150\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"BalanceDueLate30\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"BalanceDueLate60\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"BalanceDueLate90\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"BalanceDueLateMax\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"CreatedByProgram\",\"OldValue\":\"epmsApplication\",\"NewValue\":\"epmsApplication\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"CreatedByUser\",\"OldValue\":\"Org FinAdmin\",\"NewValue\":\"Org FinAdmin\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"CreatedDateTime\",\"OldValue\":\"12/2/2019 7:03:47 PM\",\"NewValue\":\"12/2/2019 7:03:47 PM\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"FinancialClassId\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"GuarantorId\",\"OldValue\":\"21737061\",\"NewValue\":\"21737061\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"GuarantorName\",\"OldValue\":\"\",\"NewValue\":\"\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"IsAssessFinanceCharge\",\"OldValue\":\" \",\"NewValue\":\" \",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"IsNewAccount\",\"OldValue\":\" \",\"NewValue\":\" \",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"IsPatient\",\"OldValue\":\"True\",\"NewValue\":\"True\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"IsSendNewsLetter\",\"OldValue\":\" \",\"NewValue\":\" \",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"LastChargeDate\",\"OldValue\":\"1/1/1940 12:00:00 AM\",\"NewValue\":\"1/1/1940 12:00:00 AM\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"LastInsurancePayment\",\"OldValue\":\"1/1/1940 12:00:00 AM\",\"NewValue\":\"1/1/1940 12:00:00 AM\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"LastPaymentDate\",\"OldValue\":\"1/1/1940 12:00:00 AM\",\"NewValue\":\"1/1/1940 12:00:00 AM\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"LastStatementDate\",\"OldValue\":\"1/1/1940 12:00:00 AM\",\"NewValue\":\"1/1/1940 12:00:00 AM\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"MigratedOn\",\"OldValue\":null,\"NewValue\":null,\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"ModifiedByProgram\",\"OldValue\":\"epmsApplication\",\"NewValue\":\"epmsApplication\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"ModifiedByUser\",\"OldValue\":\"Org FinAdmin\",\"NewValue\":\"Org FinAdmin\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"ModifiedDateTime\",\"OldValue\":\"12/2/2019 7:03:47 PM\",\"NewValue\":\"12/2/2019 7:03:48 PM\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"MonthToDateCharges\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"MonthToDateDirectPayments\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"MonthToDatePayments\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"NoteChanged\",\"OldValue\":\"1/1/1940 12:00:00 AM\",\"NewValue\":\"1/1/1940 12:00:00 AM\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"RecordNotesId\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"RecordStatus\",\"OldValue\":\" \",\"NewValue\":\" \",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"RecordStatusChangeDate\",\"OldValue\":\"1/1/1940 12:00:00 AM\",\"NewValue\":\"1/1/1940 12:00:00 AM\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"ReferenceCodeId\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"ResidenceName\",\"OldValue\":\"\",\"NewValue\":\"\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"ResidentPersonCode\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"SeparateStatementId\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"StatusId\",\"OldValue\":\"1\",\"NewValue\":\"1\",\"$type\":\"AuditChangeDetail\"}],\"$type\":\"Auditable\"}],\"$type\":\"AuditResults\"}" 
| append 
[| makeresults 
| eval _raw="{\"Results\": 
[ {\"Username\":\"foo\",\"EntityName\":\"EPMS.Domain.Entities.Account\",\"DateTime\":\"2019-12-02T20:03:48.1452368Z\",\"EntityID\":\"200000032\",\"ParentEntity\":\"\",\"ParentEntityID\":\"0\",\"ChangeType\":\"Modified\",\"ChangeDetails\": 
    [ {\"FieldName\":\"AccountGroupId\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"AccountTypeId\",\"OldValue\":\"132\",\"NewValue\":\"132\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"BalanceDue\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"BalanceDueLate120\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"BalanceDueLate150\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"BalanceDueLate30\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"BalanceDueLate60\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"BalanceDueLate90\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"BalanceDueLateMax\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"CreatedByProgram\",\"OldValue\":\"epmsApplication\",\"NewValue\":\"epmsApplication\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"CreatedByUser\",\"OldValue\":\"Org FinAdmin\",\"NewValue\":\"Org FinAdmin\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"CreatedDateTime\",\"OldValue\":\"12/2/2019 7:03:47 PM\",\"NewValue\":\"12/2/2019 7:03:47 PM\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"FinancialClassId\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"GuarantorId\",\"OldValue\":\"21737061\",\"NewValue\":\"21737061\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"GuarantorName\",\"OldValue\":\"\",\"NewValue\":\"\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"IsAssessFinanceCharge\",\"OldValue\":\" \",\"NewValue\":\" \",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"IsNewAccount\",\"OldValue\":\" \",\"NewValue\":\" \",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"IsPatient\",\"OldValue\":\"True\",\"NewValue\":\"True\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"IsSendNewsLetter\",\"OldValue\":\" \",\"NewValue\":\" \",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"LastChargeDate\",\"OldValue\":\"1/1/1940 12:00:00 AM\",\"NewValue\":\"1/1/1940 12:00:00 AM\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"LastInsurancePayment\",\"OldValue\":\"1/1/1940 12:00:00 AM\",\"NewValue\":\"1/1/1940 12:00:00 AM\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"LastPaymentDate\",\"OldValue\":\"1/1/1940 12:00:00 AM\",\"NewValue\":\"1/1/1940 12:00:00 AM\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"LastStatementDate\",\"OldValue\":\"1/1/1940 12:00:00 AM\",\"NewValue\":\"1/1/1940 12:00:00 AM\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"MigratedOn\",\"OldValue\":null,\"NewValue\":null,\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"ModifiedByProgram\",\"OldValue\":\"epmsApplication\",\"NewValue\":\"epmsApplication\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"ModifiedByUser\",\"OldValue\":\"Org FinAdmin\",\"NewValue\":\"Org FinAdmin\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"ModifiedDateTime\",\"OldValue\":\"12/2/2019 8:03:47 PM\",\"NewValue\":\"12/2/2019 8:03:48 PM\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"MonthToDateCharges\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"MonthToDateDirectPayments\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"MonthToDatePayments\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"NoteChanged\",\"OldValue\":\"1/1/1940 12:00:00 AM\",\"NewValue\":\"1/1/1940 12:00:00 AM\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"RecordNotesId\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"RecordStatus\",\"OldValue\":\" \",\"NewValue\":\" \",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"RecordStatusChangeDate\",\"OldValue\":\"1/1/1940 12:00:00 AM\",\"NewValue\":\"1/1/1940 12:00:00 AM\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"ReferenceCodeId\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"ResidenceName\",\"OldValue\":\"\",\"NewValue\":\"\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"ResidentPersonCode\",\"OldValue\":\"0\",\"NewValue\":\"0\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"SeparateStatementId\",\"OldValue\":\"0\",\"NewValue\":\"1\",\"$type\":\"AuditChangeDetail\"},{\"FieldName\":\"StatusId\",\"OldValue\":\"1\",\"NewValue\":\"1\",\"$type\":\"AuditChangeDetail\"}],\"$type\":\"Auditable\"}],\"$type\":\"AuditResults\"}"] 
| spath path=Results{}.ChangeDetails{} output=ChangeDetails 
| spath path=Results{}.Username output=Username 
| spath path=Results{}.DateTime output=DateTime 
| mvexpand ChangeDetails 
| spath input=ChangeDetails path=FieldName output=FieldName 
| spath input=ChangeDetails path=OldValue output=OldValue 
| spath input=ChangeDetails path=NewValue output=NewValue 
| table DateTime, Username, FieldName, OldValue, NewValue 
| where ((OldValue != NewValue) AND (FieldName != "ModifiedDateTime"))

Hi, @Tylerdygert
The answer has been corrected.
There are cool usage of spath and comparison method by where .

How about this?

Re: Searching nested JSON to create audit dashboard

Tylerdygert — Tue, 03 Dec 2019 15:13:03 GMT

The spath command is what I have been trying to use and having troubles with. mvzip and mvexpand and the other commands that work with it are all new to me and just havent found anything i can use yet.

Re: Searching nested JSON to create audit dashboard

Tylerdygert — Tue, 03 Dec 2019 15:17:02 GMT

This search works but I don't really understand it. Why do I need to use an eval=_raw at the start of the search? Will this work for other events that I have? Will this work for other nested fields if I need to search on those?

Thanks!

Re: Searching nested JSON to create audit dashboard

aberkow — Tue, 03 Dec 2019 16:45:52 GMT

The other commands I mentioned are just ways to deal with multivalue fields, they aren't part of the spath command. The spath command just takes in a field, which I had to create and called _raw since that's the one Splunk uses natively, and parses any JSON format it finds within that field. So for whatever field you have that has the JSON value, specify that as the input. _raw is just a representation of everything that makes up the log, and might be a good starting point if what you linked above is the actual log.

Re: Searching nested JSON to create audit dashboard

Tylerdygert — Tue, 03 Dec 2019 16:50:34 GMT

Yes, the event linked above is the raw text of the actual log. That gets parsed into nested JSON based on my props.conf. That is one of many logs though so when I am making this search I'm trying to make it work for any log that comes in. I've been attempting different things with spath but I just dont seem to get any real results. I'll keep trying different things with spath then and see if I can get it to work. Thanks.

Re: Searching nested JSON to create audit dashboard

to4kawa — Wed, 04 Dec 2019 00:57:34 GMT

Why do I need to use an eval=_raw at the start of the search?

Because _raw is the default argument of these commands spath and kv ,
and the defalut event field.

Will this work for other events that I have?

Yes, you can.

Will this work for other nested fields if I need to search on those?

Maybe you can. what is these fields?

my english is broken.
I would be happy if you could run it line by line and write a description of my query you understood.

If my understanding is that props.conf is correct, the field should be expanded like Results{}.ChangeDetails{}.FieldName

In that case, spath is not necessary. Use mvzip and kv to extract the appropriate fields.
After that, I think the search is possible.

Re: Searching nested JSON to create audit dashboard

woodcock — Wed, 04 Dec 2019 05:40:15 GMT

There are 2 basic approaches I use for dealing with nested JSON:
First:

Your Search Here | spath | eval _raw=mvzip(keyFieldName, valueFieldName, "=") | kv

Second

Your Search Here | eval foo=spath(_raw, YourPartHere).

For some reason, using eval spath() seems to work much better than | spath

Re: Searching nested JSON to create audit dashboard

Tylerdygert — Wed, 04 Dec 2019 15:14:40 GMT

Your search works great for me except for 1 thing: I need to compare NewValue vs OldValue for each field.

So, right now, I just get the NewValue for every FieldName using your search. Is there a way I can get NewValue and OldValue for every FieldName?

Also, I need to get Username out of the events as well so we know who made a change.

P.S. Your english is great and I appreciate the help!

Re: Searching nested JSON to create audit dashboard

Tylerdygert — Wed, 04 Dec 2019 15:16:32 GMT

Yes, this is what I'm learning. Either create a new value using eval after spath or put spath into an eval command to get a value.

Thanks!

Re: Searching nested JSON to create audit dashboard

woodcock — Wed, 04 Dec 2019 18:20:54 GMT

Be sure to come back and click Accept on the best answer to close the question and UpVote any helpful/useful comments/answers.

Re: Searching nested JSON to create audit dashboard

to4kawa — Thu, 05 Dec 2019 13:36:55 GMT

my answer is updated, please check it.

Re: Searching nested JSON to create audit dashboard

Tylerdygert — Thu, 05 Dec 2019 15:44:09 GMT

the results that I'm getting are all field values that aren't in my events. Looks like you set them in the first part of the subsearch?

Although, this is the format I need, Thank you very much!

Re: Searching nested JSON to create audit dashboard

woodcock — Thu, 05 Dec 2019 15:56:35 GMT

Be sure to check out the answer by @to4kawa (and UpVote it) here!
https://answers.splunk.com/answering/787043/view.html

Re: Searching nested JSON to create audit dashboard

to4kawa — Thu, 05 Dec 2019 16:00:27 GMT

I think there are multiple logs in your environment.
I used append to simulate it.

I need to compare NewValue vs OldValue for each field.

I made a difference to make it easier to understand.
I am happy to meet your expectations. Happy Splunking.

Re: Searching nested JSON to create audit dashboard

Tylerdygert — Thu, 05 Dec 2019 16:05:34 GMT

Yes, we get about 200 test events per day. This will go into a production environment when it is complete that will have thousands of events every day.

The main purpose is to be able to search on when a user makes a change to a patient record of some sort.

I took out the makeresults portions of the search and it looks like I can see all my events that way with just your spath commands.