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
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.
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
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.
This actually doesn't seem to be working. I'm still getting results that are in category1