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

What@PickleRickpoints 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 fieldcontent.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 forJobType, 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="*"```

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 forindex="mulesoft" applicationName="s-concur-api" environment=PRD priority timestamp NOT message IN ("API: START: /v1/expense/extract/ondemand/accrual*")```