Unable to get the matching files using join command.

[chinmay25]

We used the inner join command to get the matching files. However, the same command does not work with the current format of the events. Hence we extracted (rex) the data. Here is the current search that is not working. I would appreciate it if we could get alternatives to this. The total number of files is 2605. 7 files do not match and 2598 files match. We need the search to work for the matching files.

index=xyz source = FILE sourcetype = syncsort:file  JOBNAME="xyz-B" | rex field=_raw "\S{45}\s\S{11}\s\S{45}\s*\S{92}(?[A-Z0-9].*)"
| join type=inner DATA [ search index=xyz source = FILE sourcetype = syncsort:file  JOBNAME="xyz-R" | rex field=_raw "\S{45}\s\S{11}\s\S{45}\s*\S{92}(?[A-Z0-9].*)"
    | fields DATA] 
| stats count as COUNT by DATA
| addcoltotals labelfield=DATA label="Total"

[richgalloway · Accepted solution]

Try not using join. The stats command will combine events that have a common field value when you use the by clause.

index=xyz source = FILE sourcetype = syncsort:file (JOBNAME="xyz-B"  OR JOBNAME="xyz-R")
| rex field=_raw "\S{45}\s\S{11}\s\S{45}\s*\S{92}(?<DATA>[A-Z0-9].)"
| stats values(*) as * by DATA
| stats count as COUNT by DATA
| addcoltotals labelfield=DATA label="Total"

[chinmay25]

Thanks for the answer. I tried it...however, I am getting duplicates now. Its counting it twice.

[richgalloway]

I was afraid that would happen. Try the revised query.

[chinmay25]

This worked. My other concern is, if this will work if we have two different indexes?

[richgalloway]

The method works. Just change index=xyz to (index=xyz OR index=abc).

If your problem is resolved then please accept the answer to help future readers.

[chinmay25]

Sounds good.
Thanks.