How to sort on multiple values

[aditsss]

Hi Team,

I have below panel query

I want to sort on the basis of busdate and start time, But results are not coming correct.Could anyone guide on this

Currently its sorting on bus date but not start time. Please guide

index="abc" sourcetype =$Regions$ source="/amex/app/gfp-settlement-raw/logs/gfp-settlement-raw.log""StatisticBalancer - statisticData: StatisticData" "CARS.UNB."|rex "totalOutputRecords=(?<totalOutputRecords>),busDt=(?<busDt>),fileName=(?<fileName>),totalAchCurrOutstBalAmt=(?<totalAchCurrOutstBalAmt>),totalAchBalLastStmtAmt=(?<totalAchBalLastStmtAmt>),totalClosingBal=(?<totalClosingBal>),totalRecordsWritten=(?<totalRecordsWritten>),totalRecords=(?<totalRecords>)"|eval totalAchCurrOutstBalAmt=tonumber(mvindex(split(totalAchCurrOutstBalAmt,"E"),0)) * pow(10,tonumber(mvindex(split(totalAchCurrOutstBalAmt,"E"),1)))|eval totalAchBalLastStmtAmt=tonumber(mvindex(split(totalAchBalLastStmtAmt,"E"),0)) * pow(10,tonumber(mvindex(split(totalAchBalLastStmtAmt,"E"),1)))|eval totalClosingBal=tonumber(mvindex(split(totalClosingBal,"E"),0)) * pow(10,tonumber(mvindex(split(totalClosingBal,"E"),1)))|table busDt fileName totalAchCurrOutstBalAmt totalAchBalLastStmtAmt totalClosingBal totalRecordsWritten totalRecords|sort busDt|appendcols[search index="abc"sourcetype =$Regions$ source="/amex/app/gfp-settlement-raw/logs/gfp-settlement-raw.log" | rex "CARS\.UNB(CTR)?\.(?<CARS_ID>\w+)"
| transaction CARS_ID startswith="Reading Control-File /absin/CARS.UNBCTR." endswith="Completed Settlement file processing, CARS.UNB."
|eval StartTime=min(_time)|eval EndTime=StartTime+duration|eval duration_min=floor(duration/60) |rename duration_min as CARS.UNB_Duration| table StartTime EndTime CARS.UNB_Duration]| fieldformat StartTime = strftime(StartTime, "%F %T.%3N")| fieldformat EndTime = strftime(EndTime, "%F %T.%3N")|appendcols[search index="600000304_d_gridgain_idx*" sourcetype =$Regions$ source="/amex/app/gfp-settlement-raw/logs/gfp-settlement-raw.log" "FileEventCreator - Completed Settlement file processing" "CARS.UNB."|rex "FileEventCreator - Completed Settlement file processing, (?<file>[^ ]*) records processed: (?<records_processed>\d+)"| rename file as Files|rename records_processed as Records| table Files Records]|appendcols[search index="600000304_d_gridgain_idx*" sourcetype =$Regions$ source="/amex/app/gfp-settlement-raw/logs/gfp-settlement-raw.log" "ReadFileImpl - ebnc event balanced successfully"| head 7
| eval True=if(searchmatch("ebnc event balanced successfully"),"✔","")
| eval EBNCStatus="ebnc event balanced successfully"
| table EBNCStatus True]|rename busDt as Business_Date|rename fileName as File_Name|rename CARS.UNB_Duration as CARS.UNB_Duration(Minutes)|table Business_Date File_Name StartTime EndTime CARS.UNB_Duration(Minutes) Records totalClosingBal totalRecordsWritten totalRecords EBNCStatus

[dural_yyz]

You only have a sort on Business Date but you never say to sort on Start Time as well.  In fact the field Start Time is evaluated after the sort is done.  If you want a sort it should be done after both fields are available in a sortable format.

| sort "Business_Date" "StartTime"

[aditsss]

@dural_yyz  tried but not working

[PickleRick]

1. Just saying "not working" doesn't say anything. We have no idea what the results should look like, what they actually look like, what data you have and so on.

2. Apart from your main question I see another issue woth your search - you sort first, then add some data with appendcols. Are you absolutely sure that you get right data in right places?

3. And finally, if you post SPL code please do so as either code block (the </> symbol at the top of the text-edit widget) or as a preformatted style so that it doesn't get butchered into this unreadable blob of text.

[aditsss]

@dural_yyz  any option

[aditsss]

@PickleRick  @ITWhisperer 

when I am putting this

sort "Business_Date" "StartTime"

Its only sorting on Business_Date and not startTime

Could you please suggest

[ITWhisperer]

Try this

| sort 0 'Business_Date' 'StartTime'

[aditsss]

@ITWhisperer 

I tried the below query

|sort 0 'Business_Date' 'StartTime'

Its sorting only on StartTime not on business date

Could you please suggest

[ITWhisperer]

Please can you show an example of where the events are not sorted by these two fields?

[aditsss]

@ITWhisperer please find my below query

index="abc" sourcetype =$Regions$ source="/amex/app/gfp-settlement-raw/logs/gfp-settlement-raw.log""StatisticBalancer - statisticData: StatisticData" "CARS.UNB."|rex "totalOutputRecords=(?&lt;totalOutputRecords&gt;),busDt=(?&lt;busDt&gt;),fileName=(?&lt;fileName&gt;),totalAchCurrOutstBalAmt=(?&lt;totalAchCurrOutstBalAmt&gt;),totalAchBalLastStmtAmt=(?&lt;totalAchBalLastStmtAmt&gt;),totalClosingBal=(?&lt;totalClosingBal&gt;),totalRecordsWritten=(?&lt;totalRecordsWritten&gt;),totalRecords=(?&lt;totalRecords&gt;)"|eval totalAchCurrOutstBalAmt=tonumber(mvindex(split(totalAchCurrOutstBalAmt,"E"),0)) * pow(10,tonumber(mvindex(split(totalAchCurrOutstBalAmt,"E"),1)))|eval totalAchBalLastStmtAmt=tonumber(mvindex(split(totalAchBalLastStmtAmt,"E"),0)) * pow(10,tonumber(mvindex(split(totalAchBalLastStmtAmt,"E"),1)))|eval totalClosingBal=tonumber(mvindex(split(totalClosingBal,"E"),0)) * pow(10,tonumber(mvindex(split(totalClosingBal,"E"),1)))|table busDt fileName totalAchCurrOutstBalAmt totalAchBalLastStmtAmt totalClosingBal totalRecordsWritten totalRecords|appendcols[search index="600000304_d_gridgain_idx*"sourcetype =$Regions$ source="/amex/app/gfp-settlement-raw/logs/gfp-settlement-raw.log" | rex "CARS\.UNB(CTR)?\.(?&lt;CARS_ID&gt;\w+)"
| transaction CARS_ID startswith="Reading Control-File /absin/CARS.UNBCTR." endswith="Completed Settlement file processing, CARS.UNB."
|eval StartTime=min(_time)|eval EndTime=StartTime+duration|eval duration_min=floor(duration/60) |rename duration_min as CARS.UNB_Duration| table StartTime EndTime CARS.UNB_Duration]| fieldformat StartTime = strftime(StartTime, "%F %T.%3N")| fieldformat EndTime = strftime(EndTime, "%F %T.%3N")|appendcols[search index="abc" sourcetype =$Regions$ source="/amex/app/gfp-settlement-raw/logs/gfp-settlement-raw.log" "FileEventCreator - Completed Settlement file processing" "CARS.UNB."|rex "FileEventCreator - Completed Settlement file processing, (?&lt;file&gt;[^ ]*) records processed: (?&lt;records_processed&gt;\d+)"| rename file as Files|rename records_processed as Records| table Files Records]|appendcols[search index="600000304_d_gridgain_idx*" sourcetype =$Regions$ source="/amex/app/gfp-settlement-raw/logs/gfp-settlement-raw.log" "ReadFileImpl - ebnc event balanced successfully" | head 7
| eval True=if(searchmatch("ebnc event balanced successfully"),"✔","")
| eval EBNCStatus="ebnc event balanced successfully"
| table EBNCStatus True]|rename busDt as Business_Date|rename fileName as File_Name|rename CARS.UNB_Duration as CARS.UNB_Duration(Minutes)|table Business_Date File_Name StartTime EndTime CARS.UNB_Duration(Minutes) Records totalClosingBal totalRecordsWritten totalRecords EBNCStatus | sort 0 'Business_Date' 'StartTime'

[ITWhisperer]

Please show the results not the search

[aditsss]

HI @ITWhisperer 

As you can see in result StartTime is sorted but businedd date is coming as 11/07/2024 in front of that . It is not sorted