combine results from OR on each result

[ntshane]

Sorry for the weird title, but I couldn't figure out how else to reword it.

I have the following example data from a phone system:

CalledParty CallingParty Minutes

10300..............5105551212......... 5

5105555555 ... 10300..................8

10300................44444................. 9

10301............... 5105558888........1

etc......
I want to search based on a wildcard (eg. everything starting with 10*) by either CalledParty or CallingParty field. But I want to end up with the result:

Extension MinutesTotal

10300............ 22

10301..............1

Basically combining the counts from the Calling and Called as Extension.

However, I have only been able to put something together that produces this:

10300............... 5105551212....14

5105555555......10300................8

10301.................5105558888.....1

Sorry for the formatting, hopefully this makes some sense. Is there any way to combine these in this way? Thanks in advance.
-shane

[bmacias84 · Accepted solution]

I am not sure how much you know about Lookup Tables, but this might be a good case. I am also not sure if your fields are from muliple sources or one.

Here is a search that use a case statement and will add minutes by extention and display by hour.

...| bucket _time span=1h |eval Extension=case(isnotnull(CalledParty),CalledParty,isnotnull(CallingParty),CallingParty)|stats sum(Minutes) as TotalMinutes by Extension

This should give you some ideas. Below is some additional reading. Cheers

enriching-data-with-lookups-part-1/

CreateAndConfigureFieldLookups

lookup

[sowings]

As an addition to this answer, you could start your search with a macro to pass your single wildcard to either field:

[minutes_matching_extension(1)]
args = extension
definition = CalledParty="$extension$" OR CallingParty="$extension$" | bucket _time span=1h | eval Extension=case(isnotnull(CalledParty),CalledParty,isnotnull(CallingParty),CallingParty)|stats sum(Minutes) as TotalMinutes by Extension

Then call it like minutes_matching_extension(103*)