Splunk Search

Help with Logic of Compound Subsearch with inputlookup

lboro_garyp
Explorer

I'm struggling to create a search using an inputlookup and multiple NOT searches.

Background: I have an inputlookup that is a list of telephone numbers, I want to search my recent telephone log files and get a list of entries from that inputlookup that haven't made or received calls.

My current query is as a follows:

 

 

| inputlookup CUCM_lboro_assigned_numbers_27_6_22.csv 
| rename DN AS phone 
| search NOT 
    [ search index=cucm cdrRecordType=1 duration>0
    | rename callingPartyNumber AS phone 
    | table phone]
AND NOT
    [ search index=cucm cdrRecordType=1 duration>0
    | rename originalCalledPartyNumber AS phone 
    | table phone]
AND NOT
    [ search index=cucm cdrRecordType=1 duration>0
    | rename finalCalledPartyNumber AS phone 
    | table phone]

 

 

The problem with it is that the three queries are being individually 'search NOT' against the inputlookup, so if a number doesn't place a call (appears as callingPartyNumber), but does receive a call (originalCalledPartyNumber or finalCalledPartyNumber), it still gets listed. I only want to see numbers that haven't made calls AND haven't received calls.

It's almost as if I need to build an intermediate data set of numbers that are returned from all three subsearches, then 'search NOT' that against the inputlookup. But I don't know how to do that.

Any suggestions?

Labels (1)
0 Karma
1 Solution

lboro_garyp
Explorer

A colleague eventually came up with the following query that does exactly what I wanted:

| inputlookup CUCM_lboro_assigned_numbers_27_6_22.csv 
| rename DN AS phone 
| search NOT 
    [ search index=cucm cdrRecordType=1 duration>0 
    | eval phone = mvappend(callingPartyNumber,originalCalledPartyNumber,finalCalledPartyNumber) 
    | mvexpand phone 
    | dedup phone 
    | table phone
    ]

As you can see, it's still creating a multi-field value out of the fields I'm interested in (callingPartyNumber, originalCalledPartyNumber and finalCalledPartyNumber), but the using mvexpand to create a new event for each field and, most importantly, then dedupe'ing those events

View solution in original post

0 Karma

lboro_garyp
Explorer

A colleague eventually came up with the following query that does exactly what I wanted:

| inputlookup CUCM_lboro_assigned_numbers_27_6_22.csv 
| rename DN AS phone 
| search NOT 
    [ search index=cucm cdrRecordType=1 duration>0 
    | eval phone = mvappend(callingPartyNumber,originalCalledPartyNumber,finalCalledPartyNumber) 
    | mvexpand phone 
    | dedup phone 
    | table phone
    ]

As you can see, it's still creating a multi-field value out of the fields I'm interested in (callingPartyNumber, originalCalledPartyNumber and finalCalledPartyNumber), but the using mvexpand to create a new event for each field and, most importantly, then dedupe'ing those events

0 Karma

lboro_garyp
Explorer

mvappend is interesting, but doesn't seem to do what I want. I'm seeing numbers returned that I know have received a call in the search period.

I'm not sure how searching a multi-field result value a list of single-field values works, particularly with the NOT involved.

If a DN in the inputlookup file is present as one or more of callingPartyNumber, originalCalledPartyNumber or finalCalledPartyNumber it shouldn't be returned as a result.

My original query was returning every number that didn't appear as any one (or more) of the fields, I'm not sure what logic is being applied to the mvappend version, as that produces a different result.

0 Karma

marysan
Communicator

Hi
you can use mvappend command to append 3 different fields as a fields:

| inputlookup CUCM_lboro_assigned_numbers_27_6_22.csv
| rename DN AS phone
| search NOT
[ search index=cucm  cdrRecordType=1 duration>0
|eval phone=mvappend(callingPartyNumber ,originalCalledPartyNumber , finalCalledPartyNumber )
|table phone]

but I doubt that "search NOT" works for you !



0 Karma
Get Updates on the Splunk Community!

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...