Splunk Search

I am trying to combine three indexes using left join but not all the values are showing up even though the data exists.

dogaasad
New Member

index=A | stats count by host ID | eval ID=upper(ID) | rename host as HOST, ID as USERID, count as LOGIN_FAILURES |
join USERID type=full [ search index=B earliest=-1d@d groupentitlements!=None | eval Username=upper(Username) | rename Username as USERID, GivenName as FIRST_NAME, Surname as LAST_NAME, groupentitlements as ENTITLEMENT, HomeDirectory as HOME_DIRECTORY |
join USERID type=full [ search index=C earliest=-1d@d USERID | dedup USERID | eval USERID=upper(USERID) ] ] |
table HOST USERID FIRST_NAME LAST_NAME LOGIN_FAILURES HOME_DIRECTORY TITLE ASSOCIATE PRIORITY_CUSTOMER ORGANIZATION_CODE CSS_BUSINESS_GROUP ENTITLEMENT BUILDING_NAME ADDRESS_LINE1 CITY STATE_CD COUNTRY | sort - LOGIN_FAILURES

Tags (1)
0 Karma

martinpu
Communicator

I'd recommend first starting with formatting the query in Splunk by pressing CTRL and \ in the query window for readability purposes.

Try this:

index=A 
| stats count by host ID 
| eval ID=upper(ID) 
| rename host as HOST, ID as USERID, count as LOGIN_FAILURES 
| join USERID type=left 
    [ search index=B earliest=-1d@d groupentitlements!=None 
    | eval Username=upper(Username) 
    | rename Username as USERID, GivenName as FIRST_NAME, Surname as LAST_NAME, groupentitlements as ENTITLEMENT, HomeDirectory as HOME_DIRECTORY 
    | join USERID type=left 
        [ search index=C earliest=-1d@d USERID 
        | dedup USERID 
        | eval USERID=upper(USERID) ] ] 
| table HOST USERID FIRST_NAME LAST_NAME LOGIN_FAILURES HOME_DIRECTORY TITLE ASSOCIATE PRIORITY_CUSTOMER ORGANIZATION_CODE CSS_BUSINESS_GROUP ENTITLEMENT BUILDING_NAME ADDRESS_LINE1 CITY STATE_CD COUNTRY 
| sort - LOGIN_FAILURES

I changed the type of join to left, it seems to be "full" join in the query, which I dont think is a valid option

as referring to https://docs.splunk.com/Documentation/Splunk/7.2.6/SearchReference/Join

the options are:

type=(inner | outer | left) 

please let me know if this doesn't help

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