How to use head command at the end if we have multiple joins in a search?

[Akumar294]

Hello Guys,

I have a search in which i am using different join commands(4 join commands) and finally at the end, i will be getting some thousands records. But what i want is - to get the most recent event by doing a head 1 or head 10 based on most recent timestamp.
Below search should just return 1 record based on latest time stamp as i have used head 1 at the end, but it seems head is not doing any job at all, i get the same number of events if i remove head 1. Does head doesn't work with join?
Please suggest

index="pi_sales_monitoring_agent_nonprod" sourcetype=PS_dev-tes-mtr*
|search "pms_01_zip_rename"
|rex field=_raw "\.(?<Job_Number>\d\d\d\d\d\d\d)\."
|transaction Job_Number mvraw=true
|search "completed message sent" AND pmall
|eval myRaw = _raw
|mvexpand myRaw 
|rename myRaw as _raw
|search "completed message sent"
|stats count as c1
|join[search index="pi_sales_monitoring_agent_nonprod" sourcetype=PS_dev-tes-mtr*  "pms_01_zip_rename"
|rex field=_raw "\.(?<Job_Number>\d\d\d\d\d\d\d)\."
|transaction Job_Number mvraw=true
|search "completed abnormally message sent" AND pmall
|eval myRaw = _raw
|mvexpand myRaw 
|rename myRaw as _raw
|search "completed abnormally message sent"
|stats count as c2]
|join[search index="pi_sales_monitoring_agent_nonprod" sourcetype=PS_dev-tes-mtr*  "pms_01_zip_rename"
|rex field=_raw "\.(?<Job_Number>\d\d\d\d\d\d\d)\."
|transaction Job_Number mvraw=true
|search ((NOT "Complete") AND (NOT "Completed abnormally")) AND pmall
|eval myRaw = _raw
|mvexpand myRaw 
|rename myRaw as _raw
|search ((NOT "Complete") AND (NOT "Completed abnormally"))
|stats count as c3]
|join[search index="pi_sales_monitoring_agent_nonprod" sourcetype=PS_dev-tes-mtr*  "pms_01_zip_rename"
|rex field=_raw "\.(?<Job_Number>\d\d\d\d\d\d\d)\."
|transaction Job_Number
|search pmall
|stats count as c4]
|head 1

[kmaron]

Have you tried using append instead of join?

[Akumar294]

Hi @kmaron , yes i have used that, but it did not worked out