Help rebuilding subsearch that keeps timing out

[kmaron]

So here's my issue. We are creating a chart that shows each user and which desktops they use. The desktops are divided into two categories. I need counts of users for category 2 that are NOT in category 1. I have created a query that uses a subsearch and it works great with up to 7 days worth of data. However they're asking for 30 days worth of data and when I bump it up the subsearch is timing out.

I've been trying to re-build this without a subsearch but I haven't been able to figure it out yet so I'm asking for some help from the Splunk world.

Here's my search:

index=apache_logs host="prod" GET ("URL1" OR "URL2") 
| rex field=_raw " - (?<UserID>.*?) \?desktop=(?<DesktopName>\w+)" 
| search NOT 
    [ search index=apache_logs host="prod" GET ("URL1" OR "URL2") 
    | rex field=_raw " - (?<UserID>.*?) \?desktop=(?<DesktopName>\w+)" 
    | eval DesktopName=upper(DesktopName) 
    | search DesktopName=*CAT1 
    | stats count by UserID
    | fields - count] 
| stats count by UserID DesktopName 
| chart count over UserID by DesktopName

[DalJeanis]

Use @somesoni2's, becauseit is a more complete solution, but...

[search index=apache_logs host="prod" GET ("URL1" OR "URL2") "desktop=*CAT1"

...probably would have speeded the subsearch up quite a bit.

[somesoni2]

Try this (no subsearch)

index=apache_logs host="prod" GET ("URL1" OR "URL2") 
| rex field=_raw " - (?<UserID>.*?) \?desktop=(?<DesktopName>\w+)" 
| eval DesktopName=upper(DesktopName) 
| eval Category=if(like(DesktopName,"%CAT1"),"Category1","Category2")
| stats count values(Category) as Category by UserID DesktopName
| where isnull(mvfilter(match(Category,"Category1")))
| chart count over UserID by DesktopName

[kmaron]

That's exactly what I was trying to do! Thank you!

I do have another question if you have a moment.

Is there a way to do an OR in the category eval? (eval Category=if(like(DesktopName,"%CAT1"),"Category1","Category2"))

If possible I need to look for *CAT1 OR *CAT3 to exclude them both.

[kmaron]

This actually doesn't seem to be working. I'm still getting results that are in category1