how to do a match field between index and summary index

[Cheng2Ready]

How do you run a match a field ID between two indexes?
without using a sub search(due to limit of 10000 results)
without using Join command resource intensive
and there is about 140,000+ results so running join will take forever to load.

I tried the following below but doesn't seem to work:

index=xxx  source type=xxx 
| eval source_index="a"
| append
[search index=summary_index
| eval source_index="b"
| fields ID]
| stats values(source_index) as sources by trace
| where mvcount(sources) > 1
| timechart span=1h values(count) AS "Customers per Hour"



Trying to match between the main search and the summary search Unique ID accounts field and if it matches we want it to give us a count of how many ID there is which will translate customers per hour.

[ITWhisperer]

Try something like this

(index=xxx  source type=xxx) OR (index=summary_index)
| eventstats values(index) as sources by trace
| where mvcount(sources) > 1
| timechart span=1h values(count) AS "Customers per Hour"

[Cheng2Ready]

@ITWhisperer 
im getting this no results

[livehybrid]

Hi @Cheng2Ready 

You'd be much better off using a stats here i think, and loading in both searches at the start, something like this might work, but it would be good if you could confirm the field which links them? Is it trace?

(index=xxx  source type=xxx) OR (index=summary_index)
| stats values(index) as sources by trace
| where mvcount(sources) > 1

 In your search you would struggle to achieve timechart because you dont have _time at this point? If possible please give us further info we can help with this.

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[Cheng2Ready]

@livehybrid 
Question: In your search you would struggle to achieve timechart because you dont have _time at this point?
Respond: I see how can I achieve this?



Question: If possible please give us further info we can help with this.
but it would be good if you could confirm the field which links them? Is it trace?

Answer: Yes it is trace

[livehybrid]

Hi @Cheng2Ready 

Without the original data its a little hard to say, but you could try a timechart instead of stats:

(index=xxx  source type=xxx) OR (index=summary_index)
| timechart span=1d values(index) as sources by trace
| where mvcount(sources) > 1

Update the span=1d according to your needs.

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[Cheng2Ready]

@livehybrid 
for your comment"Without the original data its a little hard to say"
Will this work?

index="xxxx"   field.type="xxx" OR index=Summary_index
| eventstats values(index) as sources by trace
| where mvcount(sources) > 1

| spath output=yyyId path=xxxId input=_raw
| where isnotnull(yyyId) ANDyyyId!=""
| bin _time span=5m AS hour_bucket
| stats latest(_time) as last_activity_in_hour, count by hour_bucket, yyyId
| stats count by hour_bucket
| sort hour_bucket
| rename hour_bucket AS _time
| timechart span=5m values(count) AS "Unique Customers per Hour"

Still doesnt return any results

[Cheng2Ready]

@livehybrid  Yes its Trace ID