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
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
Thanks a lot
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