Suppose that there is a log with two fields, userName and phoneNumber, the structure is like:
userName | phoneNumber
A | 0111
A | 0222
| 0111
B | 0333
| 0222
| 0333
Namely, one phone number must and only belongs to one user. In some events, both two fields would appear but in other events, only phoneNumber field exists.
My question is, how to correlate each phone number with a particular user name, so that I can understand who has the number without depending on user name fields? I expect the new fields would like this:
userName | phoneNumber
A | A, 0111
A | A, 0222
| A, 0111
B | B, 0333
| A, 0222
| B, 0333
It's quite easy if both fields exist in one event:
| eval phoneNumber = UserID. "-" .phoneNumber |
But how to make this change work for events without userName field?
Try like this (Assuming 'one phone number must and only belongs to one user' is true)
your current search giving UserID and phoneNumber field | eventstats values(UserID) as UserForPhone by phoneNumber | eval phoneNumber = UserForPhone. "-" .phoneNumber | fields - UserForPhone
That's it! It works well. The only thing is that I guess there are some minor problems in my logs, because sometimes a phone number matches 2 users, which it shouldn't.
Could you explain a bit why your answer can apply one to many mapping to all events, and, why it doesn't work if a number points to mutiple names (when this happens, phoneNumber field for that event will disappear actually).
Thank you!
Do you have any other source of data that will contain the username and their phone number? This sounds like a data quality issue. If you can find that information somewhere else you definitely have some options we can help you with.