show parent query and subquery at the same time

[piercazzo]

Ok the title is a bit confusing.
I must graph the access logs of a server, I must present the top 10 called URLs, but also I have to present the failed calls to those URLs and compute the percentage of failures in a third column.
I think this means to concatenate two queries, one of which is a subquery of the first one, but I still have to see the parent query.

Now this is the parent query which gives me the basic results, the top the called URLs

index="bea" host="SI*" source="e:\\*\\access.log" | stats count AS URL by ACCESSLOG_URL | sort -URL | head 10 

I should take the results and find the total failed calls from the total and present both values in the same report, I just can't figure out how to do that.
Any suggesion?
Thanks
P

[sideview]

I think your search can be easily combined into a single search with a little eval and a little help from addtotals -

index="bea" host="SI" source="e:\\access.log" | eval status=if(ACCESSLOG_STATUS="4" OR ACCESSLOG_STATUS="5","failed","normal") | chart count over URL by status | addtotals | sort - Total | head 10

This will give you a table like so:

URL         failed    normal    Total
firstUrl        50      1234     1284
secondURL       60      1000     1060
thirdURL        10       700      710

etc.

[jonuwz · Accepted solution]

Example :

index=_internal source=*web_access.log | stats count count(eval(match(status,"^[45]"))) as failed by uri

This shows you the number of hits, and the number of failed requests per uri.

Its up to you how you sort it, working out the %age failed is trivial

[piercazzo]

Great, thanks!

[piercazzo]

The failed calls are matched by

index="bea" host="SI*" source="e:\\access.log" ACCESSLOG_STATUS="4" OR ACCESSLOG_STATUS="5*" | timechart span="1d" count(ACCESSLOG_URL)

[sideview]

Can you also list out the search language you would use to get the failed calls ? There's a good chance it can be done with a disjunction, a little eval and a little stats. (and no subsearches).

[Ayn]

To me this sounds like almost exactly like the example given in the docs for how/when to use subsearches.

http://docs.splunk.com/Documentation/Storm/latest/User/Useasubsearch

[Ayn]

I'm sure it doesn't. Did you actually read and follow the example I linked to?

[piercazzo]

Not working, I just can't see any matching results when using this string

index="bea" host="SI*" source="e:\\access.log" [search index="bea" host="SI" source="e:\*\access.log" | chart count by ACCESSLOG_URL]