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

[wichniewicz]

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

[to4kawa]

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 .

[manjunathmeti]

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