How to sort based on multiple columns

[aditsss]

Hi Team,

Below is my query

search index="abc" sourcetype =$Regions$ source="/amex/app/gfp-settlement-raw/logs/gfp-settlement-raw.log" "ReadFileImpl - ebnc event balanced successfully"
| eval True=if(searchmatch("ebnc event balanced successfully"),"✔","")|head 7
| 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 -Business_Date

I am sorting on the basis of business date but my startTime and EndTime is not coming correct.

Can someone guide me

Below is the screenshot for the same

[gcusello]

Hi @aditsss,

there's something wrong in this search because there's a square parenthesis close but not the open, could you share the correct search?

Ciao.

Giuseppe

[richgalloway]

What is not correct about the StartTime and EndTime fields?  What do you expect them to be?

[aditsss]

@gcusello @richgalloway 

Below is the query

search index="abc" sourcetype =$Regions$ source="/amex/app/gfp-settlement-raw/logs/gfp-settlement-raw.log" "ReadFileImpl - ebnc event balanced successfully"
| eval True=if(searchmatch("ebnc event balanced successfully"),"✔","")|head 7
| 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 -Business_Date

The issue I am facing is when I am sorting with -businessDate  businessDate is coming correct but startTime AND EndTime is not coming correct

For example in below screenshot for BusinessDate 09/11 startTime and EndTime is coming as 09/13 it should be 09/12.

@gcusello @richgalloway please guide

[gcusello]

Hi @aditsss,

is it correct the "|head 7" in the second row?

Anyway, did you checked the data in the events?

you used the table command that doesn't group any data and only display them.

It seemes that you have wrong data.

Ciao.

Giuseppe

[aditsss]

@gcusello 

How can I used Group By command here .Can you please guide.

[gcusello]

Hi @aditsss ,

you have to use a common key to group events:

search index="abc" sourcetype =$Regions$ source="/amex/app/gfp-settlement-raw/logs/gfp-settlement-raw.log" "ReadFileImpl - ebnc event balanced successfully"
| eval True=if(searchmatch("ebnc event balanced successfully"),"✔","")|head 7
| eval 
   EBNCStatus="ebnc event balanced successfully",
   StartTime=strptime(StartTime,"%Y-%m-%d %H:%M:%S.%3N"),
   EndTime=strptime(EndTime,"%Y-%m-%d %H:%M:%S.%3N")
| rename 
   busDt as Business_Date
   fileName as File_Name
   CARS.UNB_Duration as CARS.UNB_Duration(Minutes)
| stats 
   earliest(StartTime) AS StartTime
   latest(EndTime) AS EndTime
   values("CARS.UNB_Duration(Minutes)") AS "CARS.UNB_Duration(Minutes)"
   values(Records) AS Records 
   values(totalClosingBal) AS totalClosingBal
   values(totalRecordsWritten) AS totalRecordsWritten
   values(totalRecords) AS totalRecords
   values(EBNCStatus) AS EBNCStatus
   BY Business_Date File_Name
| eval 
   StartTime=strftime(StartTime,"%Y-%m-%d %H:%M:%S.%3N"),
   EndTime=strftime(EndTime,"%Y-%m-%d %H:%M:%S.%3N")
| sort -Business_Date

if you have more values for the other fields, you can use other functions as last or first.

Ciao.

Giuseppe