Splunk Search

Get fields from two searches with two common fields

srteclesmayer
New Member

Hi,

I'm getting a trouble with this situation. I have two searches:

  • From the first one i get host and auid:

index=unix | fields host type auid hostname
| eval mainhost=host
| search type="ANOM_LOGIN_FAILURES" OR type="USER_LOGIN" OR type="LOGIN"
| stats count as Attemps, count(eval(type="ANOM_LOGIN_FAILURES")) as Failed, count(eval(type="USER_LOGIN" OR type="LOGIN")) as Success by auid mainhost
| where Failed>0 AND Success>0

  • And i want to use these two fields on subsearch to get the username linked to that auid:

| appendcols maxtime=3600
[ search index="os" host=$mainhost$ sourcetype="Unix:UserAccounts" user_id=$auid$
| table user
| dedup user]
| table auid,mainhost,Attemps,Failed,Success,user

but it doesn't work. I'm not getting any information on user field but if i change the $mainhost$ and $auid$ with the value returned by the main search, it works propertly. How can i use main fields on subsearch to filter its results?

Thank you for your time.

Tags (1)
0 Karma

knielsen
Contributor

Not sure about the host field, but in theory... well, try:

(index=unix type="ANOM_LOGIN_FAILURES" OR type="USER_LOGIN" OR type="LOGIN") OR (index="os" sourcetype="Unix:UserAccounts") | eval id=coalesce(auid, user_id) | stats count as Attemps, count(eval(type="ANOM_LOGIN_FAILURES")) as Failed, count(eval(type="USER_LOGIN" OR type="LOGIN")) as Success values(user) as user by host, id | where Failed>0 AND Success>0

And don't start using join! :winking_face:

0 Karma

harishalipaka
Motivator

hi @srteclesmayer

Instead of appendcols try with |join id

Thanks
Harish
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 ...