topic Re: How to sort on multiple values in Dashboards & Visualizations

How to sort on multiple values

aditsss — Tue, 12 Nov 2024 13:42:04 GMT

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

Re: How to sort on multiple values

dural_yyz — Tue, 12 Nov 2024 13:53:10 GMT

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"

Re: How to sort on multiple values

aditsss — Tue, 12 Nov 2024 14:32:43 GMT

@dural_yyz tried but not working

Re: How to sort on multiple values

aditsss — Tue, 12 Nov 2024 15:09:12 GMT

@dural_yyz any option

Re: How to sort on multiple values

PickleRick — Tue, 12 Nov 2024 16:13:41 GMT

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.

Re: How to sort on multiple values

aditsss — Tue, 12 Nov 2024 16:42:06 GMT

@PickleRick @ITWhisperer

when I am putting this

sort "Business_Date" "StartTime"

Its only sorting on Business_Date and not startTime

Could you please suggest

Re: How to sort on multiple values

ITWhisperer — Tue, 12 Nov 2024 17:57:07 GMT

Try this

| sort 0 'Business_Date' 'StartTime'

Re: How to sort on multiple values

aditsss — Tue, 12 Nov 2024 19:51:14 GMT

@ITWhisperer

I tried the below query

|sort 0 'Business_Date' 'StartTime'

Its sorting only on StartTime not on business date

Could you please suggest

Re: How to sort on multiple values

ITWhisperer — Tue, 12 Nov 2024 23:35:45 GMT

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

Re: How to sort on multiple values

aditsss — Wed, 13 Nov 2024 03:53:30 GMT

@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=(?<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|appendcols[search index="600000304_d_gridgain_idx*"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="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, (?<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 | sort 0 'Business_Date' 'StartTime'

Re: How to sort on multiple values

ITWhisperer — Wed, 13 Nov 2024 08:32:59 GMT

Please show the results not the search

Re: How to sort on multiple values

aditsss — Wed, 13 Nov 2024 10:42:22 GMT

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