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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...