Splunk Search
Highlighted

How to change the value of a field with information from another field?

New Member

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?

0 Karma
Highlighted

Re: How to change the value of a field with information from another field?

Builder

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.

0 Karma
Highlighted

Re: How to change the value of a field with information from another field?

SplunkTrust
SplunkTrust

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 
0 Karma
Highlighted

Re: How to change the value of a field with information from another field?

New Member

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!

0 Karma