Splunk Search

Cannot solve "mvexpand: output will be truncated due to excessive memory usage."

Tylerdygert
Path Finder

Hello,
I am running into an issue with some spath and mvexpand functions in splunk. I get the following error: "output will be truncated at 3700 results due to excessive memory usage."

Doing some searching here on answers I came across this previous answer:
https://answers.splunk.com/answers/98620/mvexpand-gives-mvexpand-output-will-be-truncated-due-to-exc...

Although that solution seemed to help a lot of people it did not help me. I don't seem to see a fix anywhere else. If anyone has some advice it would be most helpful. Thanks!

Here is my search:
index=epms_audit
| 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
| stats latest(DateTime) count by Username FieldName OldValue NewValue
| rename latest(DateTime) as Time, OldValue as OriginalValue, NewValue as ChangedValue
| table Time Username FieldName OriginalValue ChangedValue

EDIT: This search works fine as long as I don't search further than 36 hours. Once I search more than a 36 hour period I get the memory error.

0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Tylerdygert

I'm not very much aware about the logic but can you please try this?

index=epms_audit
| spath path=Results{}.ChangeDetails{} output=ChangeDetails
| spath path=Results{}.Username output=Username
| spath path=Results{}.DateTime output=DateTime
| stats values(*) as * by ChangeDetails 
| spath input=ChangeDetails path=FieldName output=FieldName
| spath input=ChangeDetails path=OldValue output=OldValue
| spath input=ChangeDetails path=NewValue output=NewValue
| stats latest(DateTime) count by Username FieldName OldValue NewValue
| rename latest(DateTime) as Time, OldValue as OriginalValue, NewValue as ChangedValue
| table Time Username FieldName OriginalValue ChangedValue

UPDATED ANSWER:

YOUR_SEARCH 
| spath path=Results{} output=Results 
| eval _raw=Results 
| table _raw 
| kv 
| rename ChangeDetails{}.* as * 
| eval tmp=mvzip(mvzip(FieldName,OldValue),NewValue) 
| stats count by Username,DateTime,tmp
| eval FieldName=mvindex(split(tmp,","),0),OldValue=mvindex(split(tmp,","),1),NewValue=mvindex(split(tmp,","),2) 
| table DateTime, Username, FieldName, OldValue, NewValue 
| where ((OldValue != NewValue) AND (FieldName != "ModifiedDateTime"))

Sample:

| 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\"}"] 
| kv 
| spath path=Results{} output=Results 
| eval _raw=Results 
| table _raw 
| kv 
| rename ChangeDetails{}.* as * 
| eval tmp=mvzip(mvzip(FieldName,OldValue),NewValue) 
| stats count by Username,DateTime,tmp
| eval FieldName=mvindex(split(tmp,","),0),OldValue=mvindex(split(tmp,","),1),NewValue=mvindex(split(tmp,","),2) 
| table DateTime, Username, FieldName, OldValue, NewValue 
| where ((OldValue != NewValue) AND (FieldName != "ModifiedDateTime"))

Thanks

View solution in original post

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Tylerdygert

I'm not very much aware about the logic but can you please try this?

index=epms_audit
| spath path=Results{}.ChangeDetails{} output=ChangeDetails
| spath path=Results{}.Username output=Username
| spath path=Results{}.DateTime output=DateTime
| stats values(*) as * by ChangeDetails 
| spath input=ChangeDetails path=FieldName output=FieldName
| spath input=ChangeDetails path=OldValue output=OldValue
| spath input=ChangeDetails path=NewValue output=NewValue
| stats latest(DateTime) count by Username FieldName OldValue NewValue
| rename latest(DateTime) as Time, OldValue as OriginalValue, NewValue as ChangedValue
| table Time Username FieldName OriginalValue ChangedValue

UPDATED ANSWER:

YOUR_SEARCH 
| spath path=Results{} output=Results 
| eval _raw=Results 
| table _raw 
| kv 
| rename ChangeDetails{}.* as * 
| eval tmp=mvzip(mvzip(FieldName,OldValue),NewValue) 
| stats count by Username,DateTime,tmp
| eval FieldName=mvindex(split(tmp,","),0),OldValue=mvindex(split(tmp,","),1),NewValue=mvindex(split(tmp,","),2) 
| table DateTime, Username, FieldName, OldValue, NewValue 
| where ((OldValue != NewValue) AND (FieldName != "ModifiedDateTime"))

Sample:

| 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\"}"] 
| kv 
| spath path=Results{} output=Results 
| eval _raw=Results 
| table _raw 
| kv 
| rename ChangeDetails{}.* as * 
| eval tmp=mvzip(mvzip(FieldName,OldValue),NewValue) 
| stats count by Username,DateTime,tmp
| eval FieldName=mvindex(split(tmp,","),0),OldValue=mvindex(split(tmp,","),1),NewValue=mvindex(split(tmp,","),2) 
| table DateTime, Username, FieldName, OldValue, NewValue 
| where ((OldValue != NewValue) AND (FieldName != "ModifiedDateTime"))

Thanks

Tylerdygert
Path Finder

Unfortunately, I am still getting the error message with this search as well.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Tylerdygert

Please check my updated answer. In case of no luck please try it with fewer events for data verification and load testing.

0 Karma

Tylerdygert
Path Finder

This updated answer seems to have solved the issue, thank you very much! I am able to search any period of time now and see all results without any truncation issues.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Tylerdygert

Can you please sample event?? So we can help you on..

0 Karma

to4kawa
Ultra Champion

previous answers

Is there a good way?

0 Karma

to4kawa
Ultra Champion
index=epms_audit
| spath path=Results{}.ChangeDetails{} output=ChangeDetails
| spath path=Results{}.Username output=Username
| spath path=Results{}.DateTime output=DateTime
`comment("---- added ----")`
| fields - _raw
| fields ChangeDetails Username DateTime
`comment("--------------------")`
| mvexpand ChangeDetails
| spath input=ChangeDetails path=FieldName output=FieldName
| spath input=ChangeDetails path=OldValue output=OriginalValue 
| spath input=ChangeDetails path=NewValue output=ChangedValue
| stats latest(DateTime) as Time count by Username FieldName OriginalValue ChangedValue
| table Time Username FieldName OriginalValue ChangedValue

I'm curious about stats, but it is different from the problem, so leave it as it is.

0 Karma

Tylerdygert
Path Finder

This still gives me the same issue. The search begins to run and then stalls for a few minutes before saying that there is a memory issue with the mvexpand command and that it needs to truncate results.

Also, I had to use the stats command instead of the table command because for each event I was unable to split the username or DateTime field. Using stats allows me to get just a single DateTime and Username value for each event and create a stats table of the data.

0 Karma

to4kawa
Ultra Champion

I learned a lot. Thank you

0 Karma
Get Updates on the Splunk Community!

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...

Admin Console: A Single, Unified Interface for All Your Cloud Admin Needs

WATCH NOWJoin us to learn how the admin console can save you time and give you more control over the Splunk® ...