How write qery instead of using Join?

[karthi2809]

Thank in Advance 
I have three source type Micro, application, CsID and i want to fetch details from these three source type .In Micro source type i want to run every 11 hours and other two source type with all time.
1.In micro source type i want to populate 16 fields in this i have cluster, namespace, microservice etc.
2.In Application source type i have cluster, namespace and csid.
3.In csid source type i have only csid but i want populate all the fields from this source type .

How to merge all those without join. Its working in join but while using in dashboard its very slow .I will paste sample query which i tried but i cant able to get data from csid source type and in micro field name as cluster but in other sourcetype as ose_cluster and we also map using lookup file for ose_cluster.

(index=int* (sourcetype="micro" OR  sourcetype="application" OR sourcetype="csiID" NOT "test_cluster"))
| eval creation_time_epoch=creation_time
| eval creation_time_human=strftime(creation_time, "%Y-%m-%d %H:%M:%S")
|eval ose_cluster=coalesce(ose_cluster,cluster)
|eval csiId=trim(toString(csiId)),namespace=trim(lower(namespace)) 

|stats  values(*) as * by ose_cluster namespace |mvexpand csiId
|stats latest(*) as *  by ose_cluster  csiId 
|lookup inventory.csv ose_cluster OUTPUT sector as cluster_sector  datacenter region env
 |fillnull value="NA" 
 | table ose_cluster namespace type microservice creation_time_epoch creation_time_human cluster_sector app_sector datacenter region env csiId acronym applicationName applicationManager soeid
supportManager status org level3 level4 level5

[bowesmana]

What sort of data is contained in application and csid sourcetypes that requires an all time search. That implies that it is most likely some sort of incremental data set containing information relating to the application and CsID instances.

If that is so, then totally agree with @PickleRick in that you'd be better off building an incremental lookup - if you are only running the micro sourcetype every 11 hours, then you could probably build the lookup from application and csid at the same interval, but slightly earlier so it remains current - that's likely to significantly simplify the search.

Note that if you do separate the search you need to make sure that the ending time range of the lookup generation matches the ending time range of the micro search to ensure that you won't get csids in the micro data that was not built into the lookup.

Given your values+latest stats commands, what exactly are you trying to achieve here and what is your desired outcome? In addition to not having _time (* does not match _ fields) it may be that you could avoid the whole mvexpand logic entirely.

[PickleRick]

Ouch.  Your search is very heavy.

1. Exclusion is way slower than inclusion. Splunk has to read each event to decide whether it has the "test_cluster" term or not.

2. Those stats will most probably produce something different than you want. Especially that use of latest() after aggregation with values() will yield something very strange.  Not to mention that if you do values(*), you're not retaining the _time field so calling latest() makes no sense.

3. The mvexpand command is memory expensive so if you have a big result set it will eat up a lot of resources.

4. Running this (see my remark about mvexpand) over all time... that's gonna hurt. Badly.

So while the overall idea of stats might be sound, with a big data set it might turn out to be more reasonable to do it in steps - summarize one part of your data, create a lookup with it. Apply the lookup to another part and so on. Yes, you'd have to do it asynchronously.

5. As a side note - I always advise to use fieldformat instead of creating a separate text field with time representation