How to find a value from index1/table1 in index2/table2?

[sjansma]

I have made two indexes and set the values into a table. How can i find a value from table1 in table2 and present de value wich is not found in table2.
In table 2 are more results as in table1. I just want to see the value from table1 wich is not found in table2.

This is my search:

index=MySearch1 | stats by MessageID | dedup MessageID | table MessageID | rename MessageID as messageid | append [search index=MySearch2" | stats by RefToMessageID | dedup RefToMessageID | table RefToMessageID | sort -_time] | rename RefToMessageID as reftomessageid | foreach messageid [eval match=if(messageid!=reftomessageid, "NOK", "OK")] | stats values(messageid) values(reftomessageid) values(match)

Only one value(match) gives "OK", the others are empty. What am i doing wrong?

[DavidHourani]

Hi @sjansma,

What you're trying to do is very ressource intensive, better avoid using append and subsearches when possible.

Try this one liner :

 index=MySearch1 OR  index=MySearch2 | stats count values(index) as index by MessageID | search NOT index=MySearch2

What this does is grab all events from both indexes, checks the number of time each messageID appeared and in which source it was shown, then finally gives all the events that are not in MySearch2 but are in MySearch1.

Let me know if this helps !

Cheers,
David

[sjansma]

This is not working. I got no result where i expect result. Maybe due to that for both searches i use the same index. Search1 gives must give al list with MessageID's , search2 give al iist with RefToMessageID's. I am looking for the MessageID's wich has not a RefToMessageID (MessageID = RefToMessageID)

[DavidHourani]

oh, okay my search will only works for two different indexes. If both are in the same index then @woodcock's answer is the way to go 😄

[woodcock · Accepted solution]

Try this:

index=MySearch1 NOT [search index=MySearch2 | stats count BY RefToMessageID | table RefToMessageID | rename RefToMessageID AS MessageID]
| stats count BY MessageID
| dedup MessageID
| table MessageID
| rename MessageID AS messageid

[sjansma]

This is working

[kamlesh_vaghela]

@sjansma

Can you please try this?

index=MySearch1 
| stats count by MessageID 
| eval flag=1 
| append 
    [ search index=MySearch2 
    | stats count by RefToMessageID 
    | rename RefToMessageID as MessageID
    | eval flag=2] 
| stats values(flag) as flag by MessageID 
| where flag=2 AND flag!=1

Thanks

[sjansma]

This is working. But in the answer of @DavidHourani he says thats it's better not to use 'append' because it should be very resource intensive. ??