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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...