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