can someone help improve the efficiency of this query

[sriyechuri]

eventtype="*"  "screen" OR "ui1" | stats count AS TotalEvents by product | appendcols [search eventtype="*"  "ui2" OR "ui3" | stats count AS subsetEvents by product] | eval percentage = 100 * subsetEvents / TotalEvents | where percentage > 1

The performance of this query is slow. I want to calculate percentages based off of subsetEvents and totalevents. TotalEvents is retrieved from "screen" or "ui" events. subsetEvents is retrieved from "screen1" or "ui1". Any help is highly appreciated.

[woodcock]

Try this:

index="YouShouldAlwaysSpecifyAnIndexOrSetOfThem" AND eventtype="*" AND (("screen" OR "ui1")  OR ("ui2" OR "ui3"))
| stats count(eval(searchmatch("screen OR ui1"))) AS TotalEvents count(eval(searchmatch("ui2 OR ui3"))) AS subsetEvents 
| eval percentage = 100 * subsetEvents / TotalEvents
| where percentage > 1

[sriyechuri]

I get this error when running this query: The eval expression for dynamic field 'eval(searchmatch("screen" OR "ui1"))' is invalid. Error='Type checking failed. 'OR' only takes boolean arguments.'

[woodcock]

I updated my answer; try again.

[jpolvino]

I'll admit I don't have a clear picture of what you're trying to achieve. When I have challenges similar to yours, I found that creating a new field can sometimes help. For example:

eventtype="*" "screen" OR "ui" OR "screen1" OR "ui1"
| eval type=if(match(_raw, "screen") OR match(_raw, "ui"),"type1", "type2")
| stats count AS TotalEvents by product,type

Again, I'm not sure what you want to do with the values coming back, but if you try this on your events, the next steps may become clear to you.

If you get stuck, post a sanitized sample of the output from the above, and I'm sure someone will be able to provide guidance.

ETA: The advice posted by Rich is good, specifying fields and index is always a good way to get better performance and make your splunk admins happy.

[sriyechuri]

Hi,

I actually want to calculate failure rate of events to create an alert.
Here is the updated query:

eventtype="" "screen" OR "ui1" | stats count AS TotalEvents by product | appendcols [search eventtype="" "ui2" OR "ui3" | stats count AS subsetEvents by product] | eval percentage = 100 * subsetEvents / TotalEvents | where percentage > 1

screen has a field say x, ui1, ui2 and ui3 are coming from same field say y.
But to calculate percentage: (screen and ui1) is taken together and (ui2 and ui3) is taken together

[richgalloway]

Start by specifying an index. That and time are the best ways to narrow the scope of your search and therefore improve performance.
Do the "screen" and "ui" keywords appear in a specific field? If so, specifying the field name will help performance a lot.
Scanning all of your data twice is not helping performance. Again, if "screen" is in a specific field, there are optimizations available.

[sriyechuri]

Hi,

Here is the updated query:

eventtype="" "screen" OR "ui1" | stats count AS TotalEvents by product | appendcols [search eventtype="" "ui2" OR "ui3" | stats count AS subsetEvents by product] | eval percentage = 100 * subsetEvents / TotalEvents | where percentage > 1

screen has a field say x, ui1, ui2 and ui3 are coming from same field say y.
But to calculate percentage: (screen and ui1) is taken together and (ui2 and ui3) is taken together

[richgalloway]

Like I mentioned in my answer, good improvements can be made by using the field name in your query.

eventtype="*" (x="screen" OR y="ui1") | stats count AS TotalEvents by product 
| appendcols [search eventtype="" (y="ui2" OR y="ui3") | stats count AS subsetEvents by product] 
| eval percentage = 100 * subsetEvents / TotalEvents | where percentage > 1

Building on that, we can eliminate the dual passes like this.

eventtype="*" (x="screen" OR y="ui1" OR y="ui2" OR y="ui3") 
| stats count(eval(x="screen)) AS TotalEvents, count(eval(match(y, "ui\d")) as subsetEvents by product 
| eval percentage = 100 * subsetEvents / TotalEvents | where percentage > 1

You can adjust the match function as needed for your real values.