Optimize query by referring latest source data

[amoldesai]

Hi,

Here are the three sources that I have for the below query that I need to optimize :
a) tech_detail.gz b) group_member.gz c) tech_summary.gz.
Data is populated on daily basis for the above sources.

As seen below in the query, I am considering only the latest available data in the month for "tech_detail" source using "stats latest(source) as source". My requirement is to have only the latest source data events for other two sources too(group_memeber.gz and tech_summary.gz) so as improve the performance by using the smaller set of latest data instead of all the data that I have presently use

I could not get how "stats latest(source) as source by date_month" can be used for the other two sources. How can the query be modified to use the latest source data file in the month for the other two sources(group_member.gz and tech_summary.gz) in the query .

$indexString$ sourcetype=cache-v2 source="*tech_detail*" [search $indexString$ sourcetype=cache-v2 source="*tech_detail*" |eval date_month=strftime(_time,"%b")|stats latest(source) as source by date_month|stats values(source) as source] | join deviceId[search $indexString$ sourcetype=cache-v2 source="*group_member*" groupId="$group$" ] | join RuleId [search $indexString$ sourcetype=cache-v2 source="*tech_summary*" ] | rex mode=sed field="PrimaryTechnology" "s/^*//" |
rex mode=sed field="PrimaryTechnology" "s/^.*//" |stats values(PrimaryTechnology) as PrimaryTechnology | mvexpand PrimaryTechnology

Thanks

[amoldesai]

Thanks a lot

[somesoni2 · Accepted solution]

Try like this

$indexString$ sourcetype=cache-v2 [| tstats count WHERE $indexString$ sourcetype=cache-v2 source="*tech_detail*" by source _time span=1mon |stats latest(source) as source by _time |table source] 
| join deviceId[search $indexString$ sourcetype=cache-v2 source="*group_member*" groupId="$group$" [search $indexString$ sourcetype=cache-v2 source="*group_member*" groupId="$group$"  | bucket span=1mon _time |stats latest(source) as source by  _time |table source] ] 
| join RuleId [search $indexString$ sourcetype=cache-v2 source="*tech_summary*"  [| tstats count WHERE  $indexString$ sourcetype=cache-v2 source="*tech_summary*"  by source _time span=1mon |stats latest(source) as source by _time |table source] ] | rex mode=sed field="PrimaryTechnology" "s/^*//" | 
rex mode=sed field="PrimaryTechnology" "s/\^.*//" |stats values(PrimaryTechnology) as PrimaryTechnology | mvexpand PrimaryTechnology