Need to correlate two different data sources

[msarro]

Alright, so I am trying to correlate a call data record (essentially the billing part of a telephone call) with a media description of the call. I need to end up with a table of the following values:

• Called_Number (source 1)
• Calling_Number (source 1)
• Start_Time (source 1)
• Release_Time (source 1)
• Answer_Time (source 1)
• Call_Duration (calculated Release_Time-Start_Time)
• Time_To_Answer (calculated Answer_Time-Start_Time)
• Termination_Cause (source 1)
• Redirecting_Reason (source 1)
• Delay (source 2)
• Lost_Packets (source 2)
• Jitter (source 2)
• Out_Of_Order_Packets (source 2)
• MOSLQ (source 2)
• Codec (source 2)

The only field that overlaps in the two record types are the calling and called numbers. The timestamp is unreliable since the second source lacks any kind of precision (limited to 1m).

When I try to use transaction none of the eval statements work, and half of my fields end up missing. I think this is because there are multiple calls in that 1m span of time (its a test server where numerous calls occur rapidly).

I've looked around and people mention using stats to solve things like this, but I am having trouble finding out more information. Any advice would be appreciated.

Here is a sample from source 1:

0000027234843c69c420110324001353.9890+000000,wdv-trunking,Normal,+15555555501,+15555550037,Originating,+15555555501,Public,15555552354,20110324001353.989,0+000000,Yes,20110324001357.546,20110324001405.770,016,VoIP,,15555552354,national,tf,5555552354,,local,1.2.3.4:5060,BW001354003240311-287073805@1.2.3.4,PCMU/8000/1,1.2.3.4,958a3d58-afe8410-13c4-50029-2e0209-adada88-2e0209,,,,auto_508_1,,,,,,,,,,y,public,,1647234:0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,5555555501@lab2.net,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,8.555,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Network,,,,,,,,,,,,,,,,,,,,

Here is a sample from source 2:

"Date","Interface","Source Extension","Dest. Extension","Delay","Lost","Jitter","Out-of-Order","MOS LQ","Codec"
"03/23/11 at 8:13 PM","eth 0/1","5555555501","5555558737","72 ms","0","1 ms","0","4.200","G711U"

I've sanitized the raw data... the third field in each is the calling number. As you can see the original has an e164 format number (+1) whereas the one in the second source does not.

Any help would be greatly appreciated.

[adrianathome]

Look into doing a join. Something like search1 |join commonfield [search2] | table fields,that,you,want