Splunk Search

Help with Logic of Compound Subsearch with inputlookup

lboro_garyp
Path Finder

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
Path Finder

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
Path Finder

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
Path Finder

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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...