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

dogaasad — Wed, 30 Sep 2020 00:45:16 GMT

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

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

martinpu — Mon, 03 Jun 2019 19:12:29 GMT

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

topic I am trying to combine three indexes using left join but not all the values are showing up even though the data exists. in 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.

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