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

[karthi2809]

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

Status	Response	ArchiveFileName	correlationId
Success	API: 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

[scelikok · Accepted solution]

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"))

[karthi2809]

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)

[yuanliu]

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="*"```

Status	JobType	Response	ArchivedFileName	TotalElapsedTime	correlationId
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]

Try filtering before the stats command

[karthi2809]

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

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: *"

[karthi2809]

Same result its not showing any values in the table

[ITWhisperer]

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

[karthi2809]

{
  "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",
}

[PickleRick]

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

[ITWhisperer]

Is this a single event or multiple events?

[karthi2809]

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

[ITWhisperer]

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

[karthi2809]

Its showing in the events

[PickleRick]

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