How to join people directory csv lookup with VOIP logs to see username?

[hartfoml]

I have VOIP logs that have the cgn and cdn number as format nnnnnnnnnn or nnnnn
I have a people directory with telephone number like nnn.nnn.nnnn

how can I join the people directory (.csv lookup) with the VOIP logs to see the username from the lookup where it matches the phone number in the VOIP logs?

[martin_mueller]

You could do something like this:

index=voip phonenumber=* | eval cdn = replace(phonenumber, "\D+", "") | lookup people_directory cdn OUTPUT username

That replaces all non-digits in the phonenumber and looks that up, assuming there's a column cdn and username in the lookup.

Edit: I may have mixed up the two, your lookup has the dots? Remove dots from lookup file and you're there 🙂

[martin_mueller]

Okay, so your lookup looks like this?

Phone         RND
123.456.7890  Martin Müller

The corresponding fields in your looks would be this?

cgn         cdn         PeerAddress
1234567890  1234567890  1234567890

Looking that up directly isn't going to work. If the dots always are in the same spots then you could add them in at search time and then lookup based on that. If not then your only path is to remove the dots from the lookup, or at least add a new column with the dots removed.

[hartfoml]

Martin, Thanks for your help. Can anyone else complete the thought that Martin had above?

[hartfoml]

I also tried this in the props.conf but it is not working

[voip]
LOOKUP-voip-RDN = lookupcsv Phone OUTPUTNEW RDN

of course I did put the corresponding entry in transforms.conf

[lookupcsv]
filename = usertophone.csv

[hartfoml]

the columb's in the lookup file are many but I have two basicly the "RND" columb that is the persones full name and the "Phone" witch is the office phone number for the person.

The PeerAddress, cgn and cdn numbers in the VOIP log are all numbers with no separators. the Phone number in the lookup have "." seperators (i.e. ###.###.####)

I put this in the props.conf to get all three VOIP numbers into one field

[voip]
FIELDALIAS-Phone = PeerAddress AS Phone cdn AS Phone cgn AS Phone

[hartfoml]

I basically have two voip log types

%VOIPAAA-5-VOIP_CALL_HISTORY: CallLegType 1, ConnectionId 817DFFB3DE013E8F347402929ACC34, SetupTime 08:40:03.649 CST Tue Aug 5 2014, PeerAddress ##########, PeerSubAddress , DisconnectCause 10 , DisconnectText normal call clearing (16), ConnectTime 08:40:18.029 CST Tue Aug 5 2014, DisconnectTime 08:43:48.489 CST Tue Aug 5 2014, ...

and

%VOIPAAA-5-VOIP_FEAT_HISTORY: FEAT_VSA=fn:TWC,ft:08/05/2014 08:40:03.317,cgn:##########,cdn:##########,frs:0,fid:1284315,fcid:817DFFB3DE013E8F347402929ACC34,legID:1398DB,bguid:00817DFFB3DE013E8F347402929ACC34

[martin_mueller]

So... your log has either cgn or cdn or both? One field name each or sharing the same name? In other words, looking for sample data to help the mental image along.

Your lookup... same question basically, what's the columns in there with some sample data?

[hartfoml]

Martin, Thanks for the help, this is a good starting point. in the VOIP logs I have 10 digit numbers and 5 digit numbers, to call internal numbers you only need the 5 digits.

I have cgn and cdn numbers in the voip logs. it would be nice to see the RDN (Real Distinguished Name) for both the cgn and the cdn derived from the field "Phone" and RDN from the users.csv file

It would be nice to do this in the props.conf file but I don't know how to reconcile the difference in the number format.

If you could enter another solution that would give me a hint on a props.conf entry that would be great.