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!

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...