Huge query followed by stats on different fields

[mohitab]

I have a query like:

search /my/huge/query/with/lot/of/evals/and/joins | stats avg(field3) group by field1

search /my/huge/query/with/lot/of/evals/and/joins | stats avg(field4) group by field2

Now, the Domain(field1)=Domain(field2). i.e., field1 and field2 takes the same set of values. Infact they are like a sort of 'foreign key' if explained in Sql terminology.

I am looking for a way to get results of this type:

| (field1 U field2) | avg(field3)  | avg(field4) |

Any ideas?

EDIT1: I am looking for a single query to achieve the results so the that my/huge/query part is processed onlu once. I don't want to run two queries separately.

[mohitab]

I changed my query as:

search /my/huge/query/with/lot/of/evals/and/joins | stats avg(field3) group by field

 search /my/huge/query/with/lot/of/evals/and/joins | stats avg(field4) group by field

I finally figured out a way. I used append command to append the results of both queries. Then I used a query like:

 stats values(field3) values(field4) by field

[sgundeti]

If you are looking only to use single query and reduce processing time, then you can try postprocess as below. Postprocess is used if your base search is trying to retrieve same events for two different results. In your case If all your fields(1 to 4) are from same base query then you can try this.

your base search :
search /my/huge/query/with/lot/of/evals/and/joins | stats count by field1,field2,field3,field4

portprocess 1

| chart avg(field3) as AVG by field1

postprocess 2

| chart avg(field4) as AVG by field2

For more info on postprocess you can refer on below link.

http://docs.splunk.com/Documentation/Splunk/6.2.1/AdvancedDev/PostProcess

OR

If you want to merge results to same chart then you can use "append " as explained by #somesoni2, I suspect the raw events are retrieved twice from disk , once for inner search and once for outer search. You can inspect the search query results.

[tachifelix]

try something like this

search /my/huge/query/with/lot/of/evals/and/joins|stats avg(field1) as avgfield1, avg(field2) as avgfield2|eval field3=mvappend(field1,field2)|mvexpand field3|table field3 avgfield1 avgfield2 | fillnull value="-"

[somesoni2]

If your queries can be merged together into one search, it would be more efficiently merged. For that you need to post both of your queries to see if they can be merged.

If it can't be merged easily, you can try this

search /my/huge/query/with/lot/of/evals/and/joins | stats avg(field3) as AvgField3 as by field1 | rename field1 as field | append [ search /my/huge/query/with/lot/of/evals/and/joins | stats avg(field4) as AvgField2 by field2 | rename field2 as as field ] | stats max(AvgField3) as AvgField3, max(AvgField4) as AvgField4 by field | fillnull value="-" 

[justin_deutsch]

Hi,

Union in Splunk like in MySQL, might be useful as might Splunk for SQL Users. You should be able to use a search like:

source=mytable1| JOIN type=inner mycolumn [ SEARCH source=mytable2 ] |stats avg(field3), avg(field4)

Union in Splunk like in MySQL: http://answers.splunk.com/answers/79394/union-in-splunk-like-in-mysql.html
Splunk for SQL Users: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/SQLtoSplunk