Compare fields from two different searches in 5min bucket

[splunknoob4]

I have two different searches which each get _time and username.
I am trying to append these two searches, and compare values in username field by _time (5 min buckets), and create a new table that shows these fields:
_time username_search1 username_search2 count(where username matches in time bucket)
I haven't really had any success with transaction,eval or stats, but then again im not a power user.

Search cleaned:
1:
index=x sourcetype=y username=*
| bin _time span=5m
| table _time username

2:
index=a sourcetype=b username=*
| bin _time span=5m
| table _time username

[gcusello]

Hi @splunknoob4 ,

let me understand:

you want to have in one search the count of usernames from index x, the cont of usernames from index a and the total count, is it correct?

in this case you should run something like this:

(index=x sourcetype=y) OR (index=a sourcetype=b) username=*
| bin _time span=5m
| stats 
    count(eval(index=x)) AS x_count
    count(eval(index=a)) AS a_count
    count 
    BY _time username

Ciao.

Giuseppe

[splunknoob4]

I might be misunderstanding but no, not exactly.
I have two different searches i want to combine with append, and run a stats to count matches on username by _time.

search1 gives me a table like this:

Time	Username1
2026-01-22 09:20:00	johnsmith
2026-01-22 09:25:00	lucywilliams

search2:

Time	Username1
2026-01-22 09:20:00	richardevans
2026-01-22 09:25:00	lucywilliams

index=x sourcetype=y username=""
|bin _time span=5m
|append[search index=a sourcetype=b username=""
|bin _time span=5m
]

I want a final table that gives me counts on matches by username in the same time bucket from the two searches.

Time	Username1	Username2	Count
2026-01-22 09:25:00	lucywilliams	lucywilliams	1

To give a bit more context, search1 looks in an index for vpn clients to our business (working from home etc), search2 looks in index for admin logins on for example servers or other objects. From a security standpoint this goes againts company policy, so im trying to create a search which can be used in a report/dashboard for improving information to the sysadmins and security personel.

[gcusello]

Hi @splunknoob4 ,

let me understood: you want the total count of events for each time period (5 min) and the list of users, is this correct?

append isn't a good solution because it uses a subsearch, and this means that you have the limit of 50,000 results, so use AND in the main search and divide resulta using eval:

(index=x sourcetype=y) OR (index=a sourcetype=b) username=*
| bin _time span=5m
| eval 
     username1=if(index=x,username,""),
     username2=if(index=a,username,"")
| stats 
    values(username1) AS username1
    values(username2) AS username2
    count 
    BY _time

Ciao.

Giuseppe

[splunknoob4]

i forgot to answer your first question.
I want stats on matches by username1=username2 during 5 min buckets of the two searches.

I want to end up with a table that only shows me usernames that appear in both searches within the same timebucket

[PickleRick]

Oh, and BTW, while I don't know the underlying problem you're trying to solve, often people say about two occurrences being within the same 5-minute bucket when they really mean that they want one thing happening not longer than 5 minutes after the other one. And those things are not the same.

Binning events to 5 minute buckets means that you can have two events just a few seconds apart landing in different buckets. If you want to find the time difference you probably prefer streamstats with a time window instead of binning.

[gcusello]

Hi @splunknoob4 ,

ok, it's more clear, please try:

(index=x sourcetype=y) OR (index=a sourcetype=b) username=*
| bin _time span=5m
| stats 
    dc(username) AS username_count
    count 
    BY _time username
| where username_count>1

Ciao.

Giuseppe

[splunknoob4]

i still cant use eval or rex to normalize my fields at search with OR so that wont work.

(index=x sourcetype=y 
|eval xxxx
| rex xxxx
)
OR
(index=a sourcetype=b
|eval xxxx
|rex xxxx
)
as far as i know, this type of spl is not valid

[PickleRick · Accepted solution]

No. But you can do something else.

Search for all events

(index=x sourcetype=y) OR (index=x sourcetype=0)

Then create additional unique fields only for one of those subsets (repeat if needed for either of them)

| eval fieldx=if(searchmatch("index=x sourcetype=y"),field1,null())

And now you can selectively do your regex from any of those fields

| rex field=fieldx "(?<whateverx>matching_pattern)"

And finally if you had "the same" field extracted into two separate fields using those "partial" regexes, combine them into a single field

| eval whatever=coalesce(whateverx,whatevery)

[gcusello]

Hi @splunknoob4 ,

the best solution is to normalizze fields outside this  search and then use my search.

if you cannot do thi, you have to use append:

(index=x sourcetype=y)
| eval username=xxx
| append [ search 
     index=a sourcetype=b
     | eval username=yyy ]
| bin _time span=5m
| stats 
    dc(username) AS username_count
    count 
    BY _time username
| where username_count>1

but remember the limit of 50,000 results from the subsearch.

Ciao.

Giuseppe

[splunknoob4]

I know there are more efficient ways to get the results im looking for.
I also need to use rex and eval to normalize the fields in both my searches, since they look quite different at index time.

I don't think i can use rex and eval in a search like that?

[gcusello]

hi @splunknoob4 ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[yuanliu]

I also need to use rex and eval to normalize the fields in both my searches, since they look quite different at index time.

I don't think i can use rex and eval in a search like that?

---

Yes, you can.  @PickleRick gives you the formula.  If you need concrete help in a data analytics forum, you need to illustrate input data.  How are the two indices different?  Which exact part of the events in the two gives you the user name?  Which rex command do you use with each index?  Without knowing the rex you actually use, here is a generic formula to combine what @gcusello and @PickleRick have said:

(index=x sourcetype=y) OR (index=a sourcetype=b)
| rex "(<?user_from_index_x_sourcetype_y>matches username in index x, sourcetype y)"
| rex "(<?user_from_index_a_sourcetype_b>username in index x, sourcetype y will be extracted)"
| eval username = coalesce(user_from_index_x_sourcetype_y, user_from_index_a_sourcetype_b)
| bin _time span=5m
| stats 
    values(username) AS username
    count 
    BY _time
| where mvcount(username) > 1

Normally, I would consider the job done here.  username will always contain two users, one from each index.   But if you want to record which user comes from which index, how many counts from each user contribute total count, such information can be retained, too.