topic Re: LOOKUP QUERY - TWO FIELDS in Splunk Search

How can I validate if the information of two fields of an index exist in a lookup table ?

DG3bran — Mon, 31 Jul 2023 18:48:11 GMT

hello engineers good afternoon

I have a problem I hope you can help me to solve it.

How can I do to validate if the information of two fields of an index exist in a lookup table ?

I need to create two lookup files ?

I was thinking to unite in the same column the information of the two fields and that to consult it to the lookup table.

Re: LOOKUP QUERY - TWO FIELDS

yuanliu — Mon, 31 Jul 2023 02:11:05 GMT

The use case needs more clarity. What exactly do you mean by "exist in a lookup table?" There are too many possibilities, including but not limited to:

The two field names merely exist in the lookup as column names?
The two fields in some events have matching values in the lookup?

The best way to describe a use case is to start with a data illustration, a lookup illustration, and a result illustration.

Re: LOOKUP QUERY - TWO FIELDS

gcusello — Mon, 31 Jul 2023 06:03:34 GMT

Hi @DG3bran,

as @yuanliu said, your requirement isn't so clear:

let me summarize:

you have a search extracting two fields (field1 and field2),
you want to check if the values in these two fields are present in a lookup.

The questions are:

in the lookup you have two different fields (columns) containing the values to check or they are in one column?
do you want to check the presence of both the fields or at least one?

if they are in two columns, you have to use:

index=your_index [ | inputlookup your_lookup | fields field1 field2 ]

if they are in one column (called e.g. my_field) to check in two fields, you can try:

index=your_index ([ | inputlookup your_lookup | rename my_field AS field1 ] OR [ | inputlookup your_lookup | rename my_field AS field2 ])

Ciao.

Giuseppe

Re: LOOKUP QUERY - TWO FIELDS

DG3bran — Tue, 01 Aug 2023 22:52:23 GMT

Hi @gcusello thankyou for you help

you're right, I am not clear, I will try to explain it much better.

I need to perform two queries, in the first one to get a value from the field "Call.OrigParty.CallingPartyAddr" like this

index="cdr_cfs_index"
"Call.TermParty.TrunkGroup.TrunkGroupId"="2230" "Call.OrigParty.CallingPartyAddr"="*"
| lookup DIDSMCM Call.OrigParty.CallingPartyAddr OUTPUT Call.OrigParty.CallingPartyAddr AS foundInLookup
| where isnull(foundInLookup) | stats count by Call.OrigParty.CallingPartyAddr

In this query it shows me all the numbers that are not in the lookup file and up to this point we are doing well.

But I need the result of the first query to now parse a new field "Call.CallForwardInfo.LastRedirectingAddr"

I tried the following query:

index="cdr_cfs_index"
"Call.TermParty.TrunkGroup.TrunkGroupId"="2230" "Call.OrigParty.CallingPartyAddr"="*" "Call.CallForwardInfo.LastRedirectingAddr"="*"
| lookup DIDSMCM Call.OrigParty.CallingPartyAddr OUTPUT Call.OrigParty.CallingPartyAddr AS foundInLookup
| where isnull(foundInLookup) | stats count by Call.OrigParty.CallingPartyAddr Call.CallForwardInfo.LastRedirectingAddr

but it matches everything in the second field omitting the information from the first query.

I would like to know if there is a way to perform the first query and from the result to retrieve another field that is related to the first result and compare it with the lookup.

Re: LOOKUP QUERY - TWO FIELDS

yuanliu — Wed, 02 Aug 2023 04:05:33 GMT

It is still unclear what "is related to" means. Why is output from | stats count by Call.OrigParty.CallingPartyAddr Call.CallForwardInfo.LastRedirectingAddr "wrong"? Without telling us what the expected result is, you are asking volunteers to read your mind. (Also, if possible, post tables in text. Screenshot is terrible to work with.)

Re: LOOKUP QUERY - TWO FIELDS

gcusello — Wed, 02 Aug 2023 05:33:33 GMT

Hi @DG3bran,

let me understand: the first search is to have the list of all "Call.OrigParty.CallingPartyAddr" present in the index but not in the lookup, is it correct?

if this is your requirement, you can simplify your first search in this way:

index="cdr_cfs_index" "Call.OrigParty.CallingPartyAddr"="*" "Call.TermParty.TrunkGroup.TrunkGroupId"="2230" NOT [ | inputlookup DIDSMCM | fields Call.OrigParty.CallingPartyAddr | stats count by Call.OrigParty.CallingPartyAddr

Instead it isn't clear for me what you want from the second search: is the new field "Call.CallForwardInfo.LastRedirectingAddr" a field from the lookup or from the index?

do you want the count of events by both Call.OrigParty.CallingPartyAddr and Call.CallForwardInfo.LastRedirectingAddr or by only the new field?

you can put the first search condition in the main search (using the solution I hinted for it).

Ciao.

Giuseppe

Re: LOOKUP QUERY - TWO FIELDS

DG3bran — Wed, 02 Aug 2023 22:35:07 GMT

thank you for your time

This index, contains information of phone calls.

In the first query it works fine, now from the result of this field "Call.OrigParty.CallingPartyAddr" only some numbers also contain the field "Call.CallForwardInfo.LastRedirectingAddr" but only some, I would like to get those numbers from this field and go to compare again to the lookup.

The problem I have is that if I call both fields in the same query I get a match on the condition that both fields are present, and I stop seeing the numbers that only meet the first example.

Re: LOOKUP QUERY - TWO FIELDS

yuanliu — Thu, 03 Aug 2023 02:47:10 GMT

The problem I have is that if I call both fields in the same query I get a match on the condition that both fields are present, and I stop seeing the numbers that only meet the first example.

Let me try to understand. So, the problem is that the following two queries return different datasets.

1	index="cdr_cfs_index" "Call.TermParty.TrunkGroup.TrunkGroupId"="2230" "Call.OrigParty.CallingPartyAddr"="*"
2	index="cdr_cfs_index" "Call.TermParty.TrunkGroup.TrunkGroupId"="2230" "Call.OrigParty.CallingPartyAddr"="" "Call.CallForwardInfo.LastRedirectingAddr"=""**

Is that correct? In other words, the lookup is just a confounding factor.

Have you tried OR operator? Like

index="cdr_cfs_index" "Call.TermParty.TrunkGroup.TrunkGroupId"="2230" ("Call.OrigParty.CallingPartyAddr"="*" OR "Call.CallForwardInfo.LastRedirectingAddr"="*") | lookup DIDSMCM Call.OrigParty.CallingPartyAddr OUTPUT Call.OrigParty.CallingPartyAddr AS foundInLookup | where isnull(foundInLookup) | stats count by Call.OrigParty.CallingPartyAddr Call.CallForwardInfo.LastRedirectingAddr

Here, it is not clear what your expected output is, so I cannot tell if it meets your requirement or even if it will give you any result at all. But at least that will give you all events that search 1 returns for lookup command.