currently a field displays customer info like this: Tim Fortner single Ohio, and does not display duplicates of the customer. The goal is to find customers that change their marital status if it changes. So I want to display a field only if its marital status has changed, ex. Tim Fortner single Ohio(displays)
Tim Fortner married Ohio(displays)
Bret Kingsly married New York(does not display)
Bret Kingsly married New York(does not display)
| rex field=message "\<FirstName\>(?<fname>\w+)\<"
| rex field=message "\<LastName\>(?<lname>\w+)"
| rex field=message "\<MaritalStatus\>(?<married>\w+)"
| dedup married fname lname mdc.QuoteID
| rename mdc.State AS State
| table _time, State, mdc.QuoteID, fname, lname, married
Is that even possible to do?
you could try adding this:
...|sort 0 fname lname married|streamstats count by fname lname married |eventstats max(count) as keep by fname lname|search keep=1
this was how i got it:
|makeresults|eval data="name=Tim status=single state=ohio,name=Tim status=married state=OH,name=Bret status=married state=NY,name=Bret status=married state=NY"|makemv data delim=","|mvexpand data |eval _raw=data|kv|sort 0 name status|streamstats count by name status|eventstats max(count) as keep by name|search keep=1
Thanks for the input! Seems to be working just fine for your data, still doesnt seem to be filtering out the customers that didnt change their marital status on my end unfortunately. Idealy we want it to detect and display customers info if they changed their marital status from what they previously had it, like your's does.
can you give me examples of field values as they appear in your table (stripped of any confidential information, of course). I see you did up there, but in your query, there appears to be a QuoteId and _time. if you could give me some examples of all the values, i can try to work on that.
2017-06-16 11:30:51.210 DE 99999999 JONATHAN Snow Single
2017-06-16 11:30:39.948 AL 99999999 Kevin SMITH Single
2017-06-16 11:30:30.482 VA 99999999 AMANDA Bynes Divorced
2017-06-16 11:30:29.844 IL 99999999 Good MORALES Divorced
Here are a few examples