Getting a time series Ratio of data from two different sources

[echambervisa]

I have two different data files which are related by a single named field.   Lets call that field common_field.  From one set of data files, I can get the count per common_field and from the other set of data files I can get the count of errors per common_field.

I want to create a query which will give me the ratio of error count to total count per common_field. 

I have tried to use subsearches but it is not working. 

[PickleRick]

In general, avoid subsearches if you can. Anyway, if I understand you corrrectly, you can do something like that:

(Conditions for search1) OR (Conditions for search2)
| stats count(field_from_search1) AS overall_count count(field_from_search2) AS error_count BY common_field
| eval ratio=error_count/overall_count

That's the general idea.

[echambervisa]

I do not understand how the field_from_search1 is designated in the (Conditions for search1) and, likewise, for the search2.   Assuming the string "success" and "error" are the indicators in the lines pulled, I tried the following and it did not work:

common_field !=NULL ("success" common_field as successes) OR ("error" common_field as errors)
| stats count(errors) as errorCount count(successes) as successCount by common_field 
| eval ratio=(100*errorCount)/1+errorCount+successCount 
| timechart avg(ratio) by common_field

 The "1+" is to avoid division by zero. 

What is wrong with the above?  I think I have the field-extraction in the first line wrong but, if so, I do not know the correct syntax to use there.

[PickleRick]

Yes, in the first part you can only do a search. You can't do any operations on fields.

Ok, my mistake. I assumed you had two completely separate searches (from two different sets of data) returning some fields.

In your case, you could (and that's the simplest solution probably) just do one search, flag it (with some | eval selector=1), append another search (again, flagging it with |eval selector=2) and then do "stats count(eval(selector=1)) as s1 count(eval(selector=2)) as s2 by common_field" and you're good to go.

Subsearches however should be avoided so it's better to find all events (in your case it would be something like:

common_field=* (success OR error)

and do some clever trick to clasify the event to one class of another. If it was based on a field, it would be better, because you could just do a conditional eval based on a simple key=value if. If it's a free-text based however you'd need to use like(_raw,"error") to match events containing the word "error".

So your search would end up something like that:

common_field=* (success OR error)
| stats count as total count(eval(_raw,"%error%")) as errors by common_field

[echambervisa]

I do see one error in the eval expression which is corrected below. 

common_field !=NULL ("success" common_field as successes) OR ("error" common_field as errors)
| stats count(errors) as errorCount count(successes) as successCount by common_field 
| eval ratio=(100*errorCount)/(1+errorCount+successCount)
| timechart avg(ratio) by common_field

 However, that is not where the mystery lies!