Splunk Search

How to extract a value from fields when using stats()?

karthi2809
Builder

Thanks in Advance
Hi Guys,

I need to extract limited values from fields:

Query :

index="mulesoft" applicationName="s-concur-api" environment=PRD priority timestamp 
| search NOT message IN ("API: START: /v1/expense/extract/ondemand/accrual*") 
| spath content.payload{} 
| mvexpand content.payload{} 
|stats values(content.SourceFileName) as SourceFileName  values(content.JobName) as JobName values(content.loggerPayload.archiveFileName) as ArchivedFileName  values(message) as message min(timestamp) AS Logon_Time, max(timestamp) AS Logoff_Time by correlationId
| rex field=message max_match=0 "Expense Extract Process started for (?<FileName>[^\n]+)" 
| rex field=message max_match=0 "API: START: /v1/expense/extract/ondemand/(?<OtherRegion>[^\/]+)\/(?<OnDemandFileName>\S+)" 
| eval OtherRegion=upper(OtherRegion) 
| eval OnDemandFileName=rtrim(OnDemandFileName,"Job") 
| eval "FileName/JobName"= coalesce(OnDemandFileName,JobName) 
| eval JobType=case(like('message',"%Concur Ondemand Started%"),"OnDemand",like('message',"%API: START: /v1/expense/extract/ondemand%"),"OnDemand",like('message',"Expense Extract Process started%"),"Scheduled") 
| eval Status=case(like('message' ,"%Concur AP/GL File/s Process Status%"),"SUCCESS", like('tracePoint',"%EXCEPTION%"),"ERROR") 
| eval Region= coalesce(Region,OtherRegion) 
| eval OracleRequestId=mvappend("RequestId:",RequestID,"ImpConReqid:",ImpConReqId) 
| eval Response= coalesce(message,error,errorMessage) 
| eval StartTime=round(strptime(Logon_Time, "%Y-%m-%dT%H:%M:%S.%QZ")) 
| eval EndTime=round(strptime(Logoff_Time, "%Y-%m-%dT%H:%M:%S.%QZ")) 
| eval ElapsedTimeInSecs=EndTime-StartTime 
| eval "Total Elapsed Time"=strftime(ElapsedTimeInSecs,"%H:%M:%S") 
| eval match=if(SourceFileDTLCount=TotalAPGLRecordsCountStaged,"Match","NotMatch") 
| rename Logon_Time as Timestamp 
| table Status JobType Response ArchivedFileName ElapsedTimeInSecs "Total Elapsed Time" correlationId 
| fields - ElapsedTimeInSecs priority match 
| where JobType!=" " 
| search Status="*"

In the response field i want to show only.I dont care about the rest :

PRD(SUCCESS): Concur AP/GL Extract V.3.02 - APAC ORACLE PAY AP Expense Report. Concur Batch ID: 376 Company Code: 200 Operating Unit: US_AB_OU
PRD(SUCCESS): Concur AP/GL Extract V.3.02 - APAC ORACLE PAY AP Expense Report. Concur Batch ID: 375 Company Code: 209 Operating Unit: US_AB_OU
PRD(SUCCESS): Concur AP/GL Extract V.3.02 - APAC ORACLE PAY AP Expense Report. Concur Batch ID: 374 Company Code: 210 Operating Unit: US_AB_OU

StatusResponseArchiveFileNamecorrelationId
SuccessAPI: START: /v1/expense/extract
After calling flow archive-ConcurExpenseFile-SubFlow
Before calling flow archive-ConcurExpenseFile-SubFlow
Calling s-ebs-api for AP Import process
Concur AP/GL File/s Process Status
Concur Ondemand Started
Expense Extract Processing Starts
Extract has no GL Lines to Import into Oracle
PRD(SUCCESS): Concur AP/GL Extract V.3.02 - APAC ORACLE PAY AP Expense Report. Concur Batch ID: 376 Company Code: 200 Operating Unit: US_AB_OU
PRD(SUCCESS): Concur AP/GL Extract V.3.02 - APAC ORACLE PAY AP Expense Report. Concur Batch ID: 375 Company Code: 209 Operating Unit: US_AB_OU
PRD(SUCCESS): Concur AP/GL Extract V.3.02 - APAC ORACLE PAY AP Expense Report. Concur Batch ID: 374 Company Code: 210 Operating Unit: US_AB_OU
PRD(SUCCESS): Concur AP/GL File/s Process Status - APAC
Records Count Validation Passed
EMEA_concur_expenses_49cde170-e057-11ee-8125-de5fb5
Labels (2)
1 Solution

scelikok
SplunkTrust
SplunkTrust

Hi @karthi2809,

On your screenshots I noticed you are using Verbose mode, and you see events on "Events" tab of search interface not in the query results that shown in "Statistics" tab.

I think you need to filter "Response" field values to show only success responses. You can use mvfilter function to filter Response field. I filtered Response values that starts with "PRD". You can update regex inside match to according to your need.

Please try adding below eval at the en of your search;

| eval Response=mvfilter(match(Response,"^PRD"))

 

If this reply helps you an upvote and "Accept as Solution" is appreciated.

View solution in original post

scelikok
SplunkTrust
SplunkTrust

Hi @karthi2809,

On your screenshots I noticed you are using Verbose mode, and you see events on "Events" tab of search interface not in the query results that shown in "Statistics" tab.

I think you need to filter "Response" field values to show only success responses. You can use mvfilter function to filter Response field. I filtered Response values that starts with "PRD". You can update regex inside match to according to your need.

Please try adding below eval at the en of your search;

| eval Response=mvfilter(match(Response,"^PRD"))

 

If this reply helps you an upvote and "Accept as Solution" is appreciated.

karthi2809
Builder

Thanks @scelikok 
Its working 
I am using  coalesce if the PRD success as success message if the error i want to show error msg instead of PRD Error message .So tried like below its not working 

| eval output=mvfilter(match(message,"^PRD"))
| eval Response= coalesce(error,errorMessage,output)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

What @PickleRick points out is that event snippets in your illustration do not contain necessary fields used in your search. (Side lesson #1: Screenshots do not help anything except in explaining expected and actual visualization.)  Let me demonstrate with the followi

First of all, none of your illustrations explains where the JSON path content.payload{} comes from.  You subsequently put this extracted field in mvexpand.  Splunk will give you an error about nonexistent field content.payload{}.  Until you can demonstrate that this JSON path exist somewhere in your data, your illustrated full search cannot succeed. (Side lesson #2: Complicated SPL does not help diagnosis.  Not only do they discourage others from reading and understanding your message, they also blur your own thought process.  Distill the search to the point where you can clearly illustrate a "yes"-"no" choice.)

Secondly, your illustrations do not produce any value for JobType, which according to your search, comes from

 

| eval JobType=case(like('message',"%Concur Ondemand Started%"),"OnDemand",
  like('message',"%API: START: /v1/expense/extract/ondemand%"),"OnDemand",
  like('message',"Expense Extract Process started%"),"Scheduled")

 

In other words, none of your illustrated JSON match any of the three conditions, therefore | where JobType!=" " will give you no result.

To illustrate the above two points, let's comment out the problematic portions of the SPL and see what comes out from your data snippets:

 

| search NOT message IN ("API: START: /v1/expense/extract/ondemand/accrual*") 
```| spath content.payload{} 
| mvexpand content.payload{} ```
|stats values(content.SourceFileName) as SourceFileName  values(content.JobName) as JobName values(content.loggerPayload.archiveFileName) as ArchivedFileName  values(message) as message min(timestamp) AS Logon_Time, max(timestamp) AS Logoff_Time by correlationId
| rex field=message max_match=0 "Expense Extract Process started for (?<FileName>[^\n]+)" 
| rex field=message max_match=0 "API: START: /v1/expense/extract/ondemand/(?<OtherRegion>[^\/]+)\/(?<OnDemandFileName>\S+)" 
| eval OtherRegion=upper(OtherRegion) 
| eval OnDemandFileName=rtrim(OnDemandFileName,"Job") 
| eval "FileName/JobName"= coalesce(OnDemandFileName,JobName) 
| eval JobType=case(like('message',"%Concur Ondemand Started%"),"OnDemand",like('message',"%API: START: /v1/expense/extract/ondemand%"),"OnDemand",like('message',"Expense Extract Process started%"),"Scheduled") 
| eval Status=case(like('message' ,"%Concur AP/GL File/s Process Status%"),"SUCCESS", like('tracePoint',"%EXCEPTION%"),"ERROR") 
| eval Region= coalesce(Region,OtherRegion) 
| eval OracleRequestId=mvappend("RequestId:",RequestID,"ImpConReqid:",ImpConReqId) 
| eval Response= coalesce(message,error,errorMessage) 
| eval StartTime=round(strptime(Logon_Time, "%Y-%m-%dT%H:%M:%S.%QZ")) 
| eval EndTime=round(strptime(Logoff_Time, "%Y-%m-%dT%H:%M:%S.%QZ")) 
| eval ElapsedTimeInSecs=EndTime-StartTime 
| eval "Total Elapsed Time"=strftime(ElapsedTimeInSecs,"%H:%M:%S") 
| eval match=if(SourceFileDTLCount=TotalAPGLRecordsCountStaged,"Match","NotMatch") 
| rename Logon_Time as Timestamp 
| table Status JobType Response ArchivedFileName ElapsedTimeInSecs "Total Elapsed Time" correlationId 
| fields - ElapsedTimeInSecs priority match 
```| where JobType!=" "
| search Status="*"```

 

StatusJobType
Response
ArchivedFileNameTotalElapsedTimecorrelationId
SUCCESS 
Before calling flow post-PInvoice-SubFlow
Concur AP/GL File/s Process Status
PRD(SUCCESS): Concur AP/GL Extract - Expense Report. Concur Batch ID: 398 Company Code: 755 Operating Unit: BZ_OU
PRD(SUCCESS): Concur AP/GL Extract - Expense Report. Concur Batch ID: 398 Company Code: 725 Operating Unit: AB_OU
  19554d60
  
After calling flow SubFlow
PRD(SUCCESS): Concur AP/GL Extract- Expense Report. Concur Batch ID: 450 Company Code: 725 Operating Unit: AB_OU
Post - Expense Extract processing to Oracle
  43b856a1
  
After calling flow post-APInvoice-SubFlow
Before calling flow post-APInvoice-SubFlow
Concur Process Status
ISG AP Response
PRD(SUCCESS): Concur AP/GL Extract - AP Expense Report. Concur Batch ID: 95
Post - Expense Extract processing to Oracle
  9a1219f2

As you can see, only one correlationId has non-null Status, and none of them have any field other than Response.  This is a common troubleshooting technique: reduce search complexity to reveal the parts that make a difference.

The following is an emulation of the data snippets you illustrated.  Play with it and compare with your real data

 

| makeresults
| eval data = mvappend("{
  \"correlationId\" : \"43b856a1\",
  \"message\" : \"Post - Expense Extract processing to Oracle\",
  \"tracePoint\" : \"FLOW\",
  \"priority\" : \"INFO\"
}",
"{
  \"correlationId\" : \"43b856a1\",
  \"message\" : \"After calling flow SubFlow\",
  \"tracePoint\" : \"FLOW\",
  \"priority\" : \"INFO\"
}",
"{
  \"correlationId\" : \"43b856a1\",
  \"message\" : \"PRD(SUCCESS): Concur AP/GL Extract-  Expense Report. Concur Batch ID: 450 Company Code: 725 Operating Unit: AB_OU\",
  \"tracePoint\" : \"FLOW\",
  \"priority\" : \"INFO\"
}",
"{
  \"correlationId\" : \"19554d60\",
  \"message\" : \"PRD(SUCCESS): Concur AP/GL Extract -  Expense Report. Concur Batch ID: 398 Company Code: 755 Operating Unit: BZ_OU\",
  \"tracePoint\" : \"FLOW\",
  \"priority\" : \"INFO\",
}",
"{
  \"correlationId\" : \"19554d60\",
  \"message\" : \"Concur AP/GL File/s Process Status\",
  \"tracePoint\" : \"FLOW\",
  \"priority\" : \"INFO\",
}",
"{
  \"correlationId\" : \"19554d60\",
  \"message\" : \"PRD(SUCCESS): Concur AP/GL Extract - Expense Report. Concur Batch ID: 398 Company Code: 725 Operating Unit: AB_OU\",
  \"tracePoint\" : \"FLOW\",
  \"priority\" : \"INFO\",
}",
"{
  \"correlationId\" : \"19554d60\",
  \"message\" : \"Before calling flow post-PInvoice-SubFlow\",
  \"tracePoint\" : \"FLOW\",
  \"priority\" : \"INFO\",
}",
"{
  \"correlationId\" : \"9a1219f2\",
  \"message\" : \"Before calling flow post-APInvoice-SubFlow\",
  \"tracePoint\" : \"FLOW\",
  \"priority\" : \"INFO\",
}",
"{
  \"correlationId\" : \"9a1219f2\",
  \"message\" : \"PRD(SUCCESS): Concur AP/GL Extract  - AP Expense Report. Concur Batch ID: 95\",
  \"tracePoint\" : \"FLOW\",
  \"priority\" : \"INFO\",
}",
"{
  \"correlationId\" : \"9a1219f2\",
  \"message\" : \"Post - Expense Extract processing to Oracle\",
  \"tracePoint\" : \"FLOW\",
  \"priority\" : \"INFO\",
}",
"{
  \"correlationId\" : \"9a1219f2\",
  \"message\" : \"Concur Process Status\",
  \"tracePoint\" : \"FLOW\",
  \"priority\" : \"INFO\",
}",
"{
  \"correlationId\" : \"9a1219f2\",
  \"message\" : \"ISG AP Response\",
  \"tracePoint\" : \"FLOW\",
  \"priority\" : \"INFO\",
}",
"{
  \"correlationId\" : \"9a1219f2\",
  \"message\" : \"After calling flow post-APInvoice-SubFlow\",
  \"tracePoint\" : \"FLOW\",
  \"priority\" : \"INFO\",
}")
| mvexpand data
| rename data AS _raw
| spath
``` data emulation for
index="mulesoft" applicationName="s-concur-api" environment=PRD priority timestamp 
  NOT message IN ("API: START: /v1/expense/extract/ondemand/accrual*")
```

 

 

ITWhisperer
SplunkTrust
SplunkTrust

Try filtering before the stats command

karthi2809
Builder

@ITWhisperer  As mentioned i filter before stats.But in the events its showing the values correctly but not showing any table values

Query:

index="mulesoft" applicationName="s-concur-api" environment=PRD (*(SUCCESS): Concur AP/GL Extract V.3.02 - *. Concur Batch ID: * Company Code: * Operating Unit: *) OR (*(SUCCESS): Concur AP/GL Extract V.3.02 - *. Concur Batch ID: *)
| search NOT message IN ("API: START: /v1/expense/extract/ondemand/accrual*") 
| spath content.payload{} 
| mvexpand content.payload{} 
| stats values(content.SourceFileName) as SourceFileName values(content.JobName) as JobName values(content.loggerPayload.archiveFileName) as ArchivedFileName values(content.payload{}) as response values(content.Region) as Region values(content.ConcurRunId) as ConcurRunId values(content.HeaderCount) as HeaderCount values(content.SourceFileDTLCount) as SourceFileDTLCount values(content.APRecordsCountStaged) as APRecordsCountStaged values(content.GLRecordsCountStaged) as GLRecordsCountStaged values(content.TotalAPGLRecordsCountStaged) as TotalAPGLRecordsCountStaged values( content.ErrorMsg) as errorMessage values(content.errorMsg) as error values("content.payload{}.AP Import flow processing results{}.requestID") as RequestID values("content.payload{}.GL Import flow processing results{}.impConReqId") as ImpConReqId values(message) as message min(timestamp) AS Logon_Time, max(timestamp) AS Logoff_Time by correlationId 

 

ITWhisperer
SplunkTrust
SplunkTrust

Try filtering like this

index="mulesoft" applicationName="s-concur-api" environment=PRD "*(SUCCESS): Concur AP/GL Extract V.3.02 - *. Concur Batch ID: * Company Code: * Operating Unit: *" OR "*(SUCCESS): Concur AP/GL Extract V.3.02 - *. Concur Batch ID: *"
0 Karma

karthi2809
Builder

Same result its not showing any values in the table

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Please can you share some sample events that we can test with - please share them in a code block

0 Karma

karthi2809
Builder
{
  "correlationId" : "43b856a1",
  "message" : "Post - Expense Extract processing to Oracle",
  "tracePoint" : "FLOW",
  "priority" : "INFO"
}
{
  "correlationId" : "43b856a1",
  "message" : "After calling flow SubFlow",
  "tracePoint" : "FLOW",
  "priority" : "INFO"
}
{
  "correlationId" : "43b856a1",
  "message" : "PRD(SUCCESS): Concur AP/GL Extract-  Expense Report. Concur Batch ID: 450 Company Code: 725 Operating Unit: AB_OU",
  "tracePoint" : "FLOW",
  "priority" : "INFO"
}
{
  "correlationId" : "19554d60",
  "message" : "PRD(SUCCESS): Concur AP/GL Extract -  Expense Report. Concur Batch ID: 398 Company Code: 755 Operating Unit: BZ_OU",
  "tracePoint" : "FLOW",
  "priority" : "INFO",
}
{
  "correlationId" : "19554d60",
  "message" : "Concur AP/GL File/s Process Status",
  "tracePoint" : "FLOW",
  "priority" : "INFO",
}
{
  "correlationId" : "19554d60",
  "message" : "PRD(SUCCESS): Concur AP/GL Extract - Expense Report. Concur Batch ID: 398 Company Code: 725 Operating Unit: AB_OU",
  "tracePoint" : "FLOW",
  "priority" : "INFO",
}
{
  "correlationId" : "19554d60",
  "message" : "Before calling flow post-PInvoice-SubFlow",
  "tracePoint" : "FLOW",
  "priority" : "INFO",
}
{
  "correlationId" : "9a1219f2",
  "message" : "Before calling flow post-APInvoice-SubFlow",
  "tracePoint" : "FLOW",
  "priority" : "INFO",
}
{
  "correlationId" : "9a1219f2",
  "message" : "PRD(SUCCESS): Concur AP/GL Extract  - AP Expense Report. Concur Batch ID: 95",
  "tracePoint" : "FLOW",
  "priority" : "INFO",
}
{
  "correlationId" : "9a1219f2",
  "message" : "Post - Expense Extract processing to Oracle",
  "tracePoint" : "FLOW",
  "priority" : "INFO",
}
{
  "correlationId" : "9a1219f2",
  "message" : "Concur Process Status",
  "tracePoint" : "FLOW",
  "priority" : "INFO",
}
{
  "correlationId" : "9a1219f2",
  "message" : "ISG AP Response",
  "tracePoint" : "FLOW",
  "priority" : "INFO",
}
{
  "correlationId" : "9a1219f2",
  "message" : "After calling flow post-APInvoice-SubFlow",
  "tracePoint" : "FLOW",
  "priority" : "INFO",
}
0 Karma

PickleRick
SplunkTrust
SplunkTrust

These events don't seem to match the fields you're using in your search.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Is this a single event or multiple events?

0 Karma

karthi2809
Builder

Multiple events i sent for three correlationId 43b856a1,19554d60,9a1219f2

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Your initial search (as it stands) doesn't appear to be able to pick up these events. Please can you clarify your events and search

0 Karma

karthi2809
Builder

Its showing in the events

0 Karma

PickleRick
SplunkTrust
SplunkTrust

You're not showing us the events. You're showing bits and pieces from separate events.

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...