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.
@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
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
Unfortunately, I am still getting the error message with this search as well.
@Tylerdygert
Please check my updated answer. In case of no luck please try it with fewer events for data verification and load testing.
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.
@Tylerdygert
Can you please sample event?? So we can help you on..
Is there a good way?
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.
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.
I learned a lot. Thank you