Splunk Search

Searching nested JSON to create audit dashboard

Tylerdygert
Path Finder

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

0 Karma
1 Solution

to4kawa
Ultra Champion
| 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?

View solution in original post

Get Updates on the Splunk Community!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...