topic How can SPL be written more efficiently to combine 3 source types? in Splunk Search

How can SPL be written more efficiently to combine 3 source types?

wichniewicz — Thu, 09 Apr 2020 17:41:37 GMT

I am combining 3 source types. I've tried using |stats values() but can't seem to get it to work.

Example of what I currently have written but it runs too slow.

index=integration sourcetype=Incident
| join type=left Assignment_Group 
    [search index=integration sourcetype=Assignment
    | rename NAME AS Assignment_Group Team_Leader AS Leader_ID
    | join type=left Leader_ID
        [search index=integration sourcetype=ROLLUP_ORG_LEVELS
        | rename ID AS Leader_ID ]]
| dedup Incident_ID
| table Incident_ID Assignment_Group LVL3_MGR

Re: How can SPL be written more efficiently to combine 3 source types?

manjunathmeti — Thu, 09 Apr 2020 19:06:48 GMT

hi @wichniewicz,

Try this:

index=integration sourcetype=Incident OR sourcetype=Assignment OR sourcetype=ROLLUP_ORG_LEVELS
| fields  Incident_ID, Assignment_Group, NAME, TEAM_LEADER, ID, LVL3_MGR
| eval Assignment_Group=coalesce(Assignment_Group, NAME) 
| eval TEAM_LEADER=coalesce(TEAM_LEADER, ID) 
| eventstats values(LVL3_MGR) AS LVL3_MGR BY TEAM_LEADER
| eventstats values(LVL3_MGR) AS LVL3_MGR BY Assignment_Group
| stats latest(LVL3_MGR) AS LVL3_MGR, latest(Assignment_Group) AS Assignment_Group BY Assignment_Group

Re: How can SPL be written more efficiently to combine 3 source types?

to4kawa — Thu, 09 Apr 2020 21:11:55 GMT

index=integration (sourcetype=Incident OR sourcetype=Assignment OR sourcetype=ROLLUP_ORG_LEVELS)
| eval Leader_ID=coalesce(Leader_ID,Team_Leader,ID), Assignment_Group=coalesce(Assignment_Group, NAME)
| stats count(Leader_ID) as flag values(Assignment_Group) as Assignment_Group values(LVL3_MGR) as LVL3_MGR by Incident_ID
| search flag=3
| table Incident_ID Assignment_Group LVL3_MGR

Your join aims to find the Incident_ID which has Leader_ID.
This query aims to find same .