Splunk Search

How to sort based on multiple columns

aditsss
Motivator

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

aditsss_0-1695034960654.png

 

Labels (3)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma

aditsss
Motivator

@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.

aditsss_0-1695115824267.png

@gcusello @richgalloway please guide

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

aditsss
Motivator

@gcusello 

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Using the Splunk Threat Research Team’s Latest Security Content

REGISTER HERE Tech Talk | Security Edition Did you know the Splunk Threat Research Team regularly releases ...

SplunkTrust | 2024 SplunkTrust Application Period is Open!

It's that time again, folks! That's right, the application/nomination period for the 2024 SplunkTrust is ...