Splunk Search

Help rebuilding subsearch that keeps timing out

kmaron
Motivator

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
0 Karma

DalJeanis
Legend

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.

0 Karma

somesoni2
Revered Legend

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
0 Karma

kmaron
Motivator

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.

0 Karma

kmaron
Motivator

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

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...