Splunk Search

InnerSearch not creating columns with eventstats

naved77
Loves-to-Learn Lots

I want to get the result and divide it into three sections as three-column such as last 15 min result, avg of 7 day and avg of one day. I have used below search but somehow I am getting values in only two columns i.e. product and sevenDayAvg. Two other column 'oneDayAvg' and 'quantity1' is coming as blank.
Below is my search:

index="in" daysago=7 "Product" AND "TotalQTY" | rex field=_raw "Product:(?<Product>[0-9]{5,15}),TotalQTY:(?<quantity>[0-9]{1,5})" 
 | eventstats sum(quantity) as totalsale BY Product |eval sevenDayAvg=totalsale/672 | append [search index=hybris_prod host=dep-prd-db-monitoring daysago=1 "Product" AND "TotalQTY" | rex field=_raw "Product:(?<Product>[0-9]{5,15}),TotalQTY:(?<quantity>[0-9]{1,5})" 
 | eventstats sum(quantity) as totalsale BY Product |eval oneDayAvg=totalsale/96] | append [search index=hybris_prod host=dep-prd-db-monitoring earliest=-15m@m now() "Product" AND "TotalQTY" | rex field=_raw "Product:(?<Product>[0-9]{5,15}),TotalQTY:(?<quantity1>[0-9]{1,5})" ] | dedup Product | table Product,quantity1,oneDayAvg,sevenDayAvg

Please note all three searches are working fine if I use them individually.

0 Karma

woodcock
Esteemed Legend

Like this:

(index="in" daysago=7 "Product" AND "TotalQTY") OR
(index=hybris_prod host=dep-prd-db-monitoring daysago=1) OR
(index=hybris_prod host=dep-prd-db-monitoring earliest=-15m@m latest=now "Product" AND "TotalQTY")
| rex field=_raw "Product:(?<Product>[0-9]{5,15}),TotalQTY:(?<quantity1>[0-9]{1,5})"
| eval Time = case(
   (_time >= relative_time(now, "-15m@m"), "Last_15_minutes",
   (_time >= relative_time(now, "-15m@m"), "Yesterday",
   true(), "7_Days_Ago")
| stats sum(quantity) as TotalSale count BY Product Time
| eval avg = TotalSale / count
0 Karma

naved77
Loves-to-Learn Lots

Tried the suggested query but here I am not getting result as required. I was looking for the query to make to make 4 column such as 'Product','sevenDayAvg','OneDayAvg' and last 15 minutes data.
Here the average per 15 minutes. for example, for one day period it should be (Totalsale for one day)/96 and for 7 day period avg should be (Totalsale in 7 day)/672.
With the query suggested by you I am getting 5 columns that is 'Product','Time','TotalSale','count' and 'Avg'.

0 Karma
Get Updates on the Splunk Community!

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...

Check out This Month’s Brand new Splunk Lantern Articles

Splunk Lantern is a customer success center providing advice from Splunk experts on valuable data insights, ...

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...