topic Re: Get fields from two searches with two common fields in Splunk Search

Get fields from two searches with two common fields

srteclesmayer — Wed, 30 Sep 2020 00:04:21 GMT

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.

Re: Get fields from two searches with two common fields

harishalipaka — Wed, 10 Apr 2019 13:35:38 GMT

hi @srteclesmayer

Instead of appendcols try with |join id

Re: Get fields from two searches with two common fields

knielsen — Wed, 10 Apr 2019 13:56:31 GMT

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! 😉