topic Re: How to sort based on multiple columns in Splunk Search

How to sort based on multiple columns

aditsss — Mon, 18 Sep 2023 12:16:33 GMT

Hi Team,

Below is my query

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

Re: How to sort based on multiple columns

richgalloway — Mon, 18 Sep 2023 12:20:54 GMT

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

Re: How to sort based on multiple columns

gcusello — Mon, 18 Sep 2023 12:25:05 GMT

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

Re: How to sort based on multiple columns

aditsss — Tue, 19 Sep 2023 09:33:08 GMT

@gcusello @richgalloway

Below is the query

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

Re: How to sort based on multiple columns

gcusello — Tue, 19 Sep 2023 09:37:25 GMT

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

Re: How to sort based on multiple columns

aditsss — Tue, 19 Sep 2023 10:25:06 GMT

@gcusello

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

Re: How to sort based on multiple columns

gcusello — Tue, 19 Sep 2023 10:33:33 GMT

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