Compare result of three different index searches

[runiyal]

I have 3 Indexers I have data. Two Indexers are the source and Third one is the target. So if I am I am tryinng to Adding any file to source, it should ultimately added to target too.

Source Index 1 - Search

index="1" <search creteria> | spath field=name | table name

Source Index 1 - Result

abc.pdf
xyz.pdf
klm.pdf

Source Index 2 - Search

index="2" <search creteria? | rex field=_raw "(?P<name>[^\\\]+)$" | table name

Source Index 2 - Result

123.pdf
456.pdf

Target Index 3 - Search

index="3"  | rex field=_raw "Converted file Name  (?.*)" | table name

Target Index 3 - Result

abc.pdf
xyz.pdf
123.pdf
456.pdf
789.pdf

Need to report -
1. If all the files added in Source 1 & 2 are there in Target 3
2. If there is any difference then generate a report
3. Also mention about the files which are in Target but never came from Source 1 & 2
4. Result should have File Name as well as the source index where it resides (although name should be custom/user-friendly)

So, from our example, we should have a report like -

FileName        Index   
klm.pdf     SrcInx1 (instead of 1)
789.pdf     TgtInx3 (instead of 3)

Your inputs will be appreciated. Thanks!

[woodcock · Accepted solution]

Try this:

(index="1" <search creteria>) OR (index="2" <search creteria>) OR index="3"
| spath field=name
| rex field=_raw "(?<name_from2>[^\\\]+)$"
| rex field=_raw "Converted file Name  (?<name_from3>.*)"
| eval name = coalesce(name, name_from2, name_from3)
| stats dc(index) AS index_count values(index) AS index BY name
| search (NOT index="3") OR NOT (index="1" OR index="2")
| replace 1 WITH SrcInx1, 2 WITH SrcInx2, 3 WITH TgtInx3 IN index

Run-anywhere Poc:

| makeresults 
| eval raw="index=1,name=abc.pdf index=1,name=xyz.pdf index=1,name=klm.pdf index=2,name=123.pdf index=2,name=456.pdf index=3,name=abc.pdf index=3,name=xyz.pdf index=3,name=123.pdf index=3,name=456.pdf index=3,name=789.pdf" 
| makemv raw 
| mvexpand raw 
| rename raw AS _raw 
| kv 

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| stats dc(index) AS index_count values(index) AS index BY name 
| search (NOT index="3") OR NOT (index="1" OR index="2")
| replace 1 WITH SrcInx1, 2 WITH SrcInx2, 3 WITH TgtInx3 IN index

[runiyal]

Hello Woodcock,

I am getting this error while running this-
Error in 'where' command: The 'not' function is unsupported or undefined.
The search job has failed due to an error. You may be able view the job in the Job Inspector.

[woodcock]

Quite correct; just swap where for search.

[runiyal]

I am getting result as raw as following -

klm.pdf ABC-07152019194038128-85238-10001.pdf ABC Firm001-00-2122019-07-15 T19:40:39.859-04:00 ABC-07152019194038128-85238-10001 Web00138283WebsiteCC ABCUnrestricted........................

I want result like just -

 FileName        Index    
 klm.pdf        SrcInx1    (instead of 1)
 789.pdf        TgtInx3    (instead of 3)

[woodcock]

See updated answer.

[runiyal]

Hello Woodcock, This is really cool. Only issue is I have to add in the | eval raw= **<>**
so that all the filename and Index can extracted from these three searches and presented to solution piece for line 10/11/12.

[woodcock]

I do not understand what you mean but in any case, you have 99% of the answer and should be able to walk the last 1% across the finish line on your own, right?

[runiyal]

Thanks woodcock for your help. I will for sure...

[woodcock]

Be sure to come back here and click Accept when you do.

[runiyal]

It worked but I am getting the whole raw like -

first-07152019194038128-85238-10001.pdfABC-07152019194038128-85238-10001.pdfABC Firm001-00-2122019-07-15T19:40:39.859-04:00ABC-07152019194038128-85238-10001Web00138283WebsiteCCAOBUnrestrictedAnnetteNovelaquestInterceptor:42] Status code : {}200 19:40:41,087 INFO [app-exec-1032][LoggingRequestInterceptor:43] Status text : {}OK 19:40:41,087 INFO [app-exec-1032][LoggingRequestInterceptor:44] =======================ABC Rest Service end================================================= 19:40:41,110 INFO [app-exec-1032][AbcController:125] The HTTP Status200 19:40:41,110 INFO [app-exec-1032][AbcController:293] Rest Service call is Successfull and started the email.first-07152019194038128-85238-10001 19:40:42,753 INFO [app-exec-1032][AbcController:298] Mail Sent to test@aol.com

I just want the file name like -
first-07152019194038128-85238-10001.pdf

I need to see the resultset in this format -

 FileName        Index    
 klm.pdf        SrcInx1    (instead of 1)
 789.pdf        TgtInx3    (instead of 3)