How do i find common values between two different fields from two different sourcetypes???

[nikitha15]

Hi all, so the question looks pretty simple but i am not able to figure out the accurate answer. So i need to find the count of common values between two different fields from two different sourcetypes .

I have an index=main and two sourcetypes sourcetype1 and sourcetype2. These two sourcetypes each have a hostname field and i need to find the common values between the two hostname fields.

The query i used is this:

Index=main sourcetype=sourcetype1 | dedup hostname | table hostname | append [ search index=main sourcetype= sourcetype2 | dedup hostname | table hostname ] |table hostname | stats count by hostname | where count >1

---

The problem is the values in hostname field in sourcetype1 are almost 75k and values in hostname field in sourcetype2 are almost 90k. And i am getting a result of 22k by using that query. But the actual count of common values is almost 40k . So I don't understand where i am gng wrong. Can anyone plz tell me if my query is right or if there is any other approach to this i can use?? Thanks a lot.

[kamlesh_vaghela]

@nikitha15

Can you please try this?

index=main (sourcetype="sourcetype1" OR sourcetype="sourcetype2")| stats values(sourcetype) as sourcetype by hostname | where (sourcetype="sourcetype1" AND sourcetype=" sourcetype2")

[nikitha15]

I am still getting 34k as result .i need 40k. But thanks a lot , i learned a new approach.

[kamlesh_vaghela]

@nikitha15

Can you please check how many events you getting with the search??

index=main (sourcetype="sourcetype1" OR sourcetype="sourcetype2") 

I doubt the limit of search results.

https://docs.splunk.com/Documentation/Splunk/8.0.4/Admin/Limitsconf#.5Bsearchresults.5D