How to check results and see if there is a name that does not exist in lookup?

[greekleo89]

Hi,

I receive data from a particular product that is installed on various customers, that data is received every 5 minutes, from the jason there is a field named tname and what i am interested in is for every customer (lets say custoerName is the field) check what tname's we received in the last five minutes and compare to the .csv lookup file. I am only interested to show what is present from the returned data that is not present in the .csv.

I hope the above helps?

Thanks

[ITWhisperer]

Try something like this (assuming tdname{} has already been extracted)

your index
| rename "tdname{}" as tdname 
| search NOT 
    [| inputlookup lookup.csv
    | fields tdname]

[gcusello]

Hi @greekleo89,

please try something like this:

index=your_index [ | inputlookup your_lookup,csv | rename customerName AS tname | fields tname ]
| ...

in few words, you have to use a subsearch with the only attenbtion to rename the field in the subsearch to have the same fieldname that the main search.

Ciao.

Giuseppe

[greekleo89]

Hi Giusepe,

Thank you for your reply.

Slightly confused as to the renaming of customerName to tname.

The data received looks like this

json >>>
CustomerName: abb1  [tdname: 'aaa','bbb']
CustomerName: abb2  [tdname: 'aaa','bbb']

csv=
tdname, enabled,
aaa,true
bbb,false
ccc,true

All i am interested is to output a table showing customer x has aaa and bbb for example but these two do not appear in the csv.

[gcusello]

Hi @greekleo89,

to be sure that a search with subsearch runs, you need to have the same field in both main and subsearch, so you need to rename the fieldname in subsearch to have the same fieldname than the main seatch.

In your case, if in the main search you have a field called "tname" and in the subsearch you have a field called "customerName", you have to rename "customerName" AS "tname" to have the same fieldname.

Ciao.

Giuseppe

[greekleo89]

Hi,

Thank you, i got it to work, last question but how would i do the reverse?

So show results only when what is in the lookup does not exist in the search. Is it just swapping the order of the search?

Thanks

[PickleRick]

It's not that easy. Subsearches have their limitations so subsearching for a simple inputlookup is relatively safe but spawning a "heavy" search across your data might prove tricky - you could get incomplete results.

[greekleo89]

What way do you suggest i try.

Essentially if have a list of 4 rows with the column name as Name and the data as follows
aa
ab
ac
ad

My search does the following:

index=main source=xcalibur type=stats
|rename tdname as Name
| table Name

I get all the Names that are returned from the various customers, so how do i compare and show me only where a customer does not have any of the 4 names in the input lookup?

[ITWhisperer]

As I suggested earlier

index=main source=xcalibur type=stats
|rename tdname as Name
| table Name
| search NOT [| inputlookup lookup.csv | fields Name]

[greekleo89]

Hi,

The above search will return me what each customer has that is not on the list, what i want is to tell me ONLY if any of the entries on the list.csv are NOT in the search results.

[ITWhisperer]

Try something like this

| inputlookup lookup.csv 
| fields Name
| search NOT [search index=main source=xcalibur type=stats
| rename tdname as Name
| dedup Name
| table Name]

[PickleRick]

While syntactically correct of course, I would not use that approach in a general case. Due to subsearch limits you can get into a situation when your subsearch terminates silently and you get wrong overall results.

The proper way around would be to use the approach similar to this: https://www.duanewaddle.com/proving-a-negative/

Should have written that earlier but was on my tablet 😉

The general approach would be something like this

<your search>
| rename field_from_search as commonfield
| fields commonfield
| dedup commonfield
| table commonfield
| eval selector=1
| append [ | inputlookup lookup.csv
   | rename lookupname as commonfield
   | fields commonfield
   | eval selector=2 ]
| stats sum(selector) by commonfield

This way by checking the value of the selector field you can see whether the value was present in the original search, the lookup table or both.

You can use similar technique to compare two searches using multisearch as long as they only contain streaming commands.

[greekleo89]

Hi,

Not sure if i understand but i have just done what you suggest and the selector for the search results is 1 and csv is 2,  how would i go about showing the result because at the moment i have a table with selector 1/2 and not really an output that i need.

[PickleRick]

If you have selector=1, the value is only in the search. If you have selector=2, the value is only in the lookup. If you have selector=3, the value is in both the lookup and the search. So by filtering the proper selector values you can get only the values you want.

[greekleo89]

How would i incorporate this too?

|join customerID
[| dbxquery query="mysql query......." ]

We have a db with customer names and id's and when the data comes in from the script it also pulls the customer names/id's from the apis so we use both of this data to join them so we know for which customer we are searching....

[greekleo89]

Cool, that works but part of the way.

I didn't mention it earlier as on all the searches i was doing i could include it and it was fine but on this i cant.

I am also doing a 

|join customerID
[| dbxquery query="mysql query......." ]

We have a db with customer names and id's and when the data comes in from the script it also pulls the customer names/id's from the apis so we use both of this data to join them so we know for which customer we are searching....


if i add customerID in the table it shows me all the entries in the CSV instead of the one that is wrong.

[greekleo89]

Hi Guys,

The problem that i am now facing is that the field customerID only exists in the first search, i have tried various different way to also display the companyID on my last stats/output but i get no luck. Any help on this would be greatly appreciated.

Thanks,

Greg