Splunk Search

Optimize query by referring latest source data

amoldesai
Explorer

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

Tags (1)
0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

0 Karma

amoldesai
Explorer

Thanks a lot

0 Karma

somesoni2
Revered Legend

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
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...