Archive

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

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

Path Finder

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!