Creating a tabular result along with chart

[babidi]

Hello Splunk Support Community:

I am trying to find the difference between request and response from the log.

For information purpose this is the request format:

05 Jan 2018 16:45:19,951 - INFO http-bio-8080-exec-409 - 9999999: begin getAccountDefault()

For information purpose this is the response format

Jan 5 16:48:25 MyAccount ERROR: com.ebpsource.XYZ123Consumer.Customer0: - E0 00300 Customer0 - ERROR: XYZGetAccountDefault returned 0 records for account=9999999 loginId=xyz@abc.om dateFrom=12/17/2017 dateTo=01/05/2018 doLinking=true isUDLAccount=false.

What I am trying to achieve are following:

(1)- Get the difference between the Response and Request i.e if you see you above the Request starts with 05 Jan 2018 16:45:19 and likewise Response contains : Jan 5 16:48:25 , I like to get the difference for these timestamps

The End Result should look like in tabular format :

Account Number , Email Address, Function/Webservice Call Name, Request Time, Response Time, Difference
9999999 , xyz@abc.om,getAccountDefault(),05 Jan 2018 16:45:19, Jan 5 16:48:25,3 minutes and 3 seconds

The above result should be clickable to take it the log stack trace.

[micahkemp]

One possibility is to use transaction, which you can configure with the start/end criteria as well as the field to join on. Here is a run anywhere example of this in action:

| makeresults | eval _raw="Jan 5 16:48:25 MyAccount ERROR: com.ebpsource.XYZ123Consumer.Customer0: - E0 00300 Customer0 - ERROR: XYZGetAccountDefault returned 0 records for account=9999999 loginId=xyz@abc.om dateFrom=12/17/2017 dateTo=01/05/2018 doLinking=true isUDLAccount=false."
| append [| makeresults | eval _raw="05 Jan 2018 16:45:19,951 - INFO [http-bio-8080-exec-409] (Util.java:44) - 9999999: begin getAccountDefault()"]
| rex "(?<returned> records for account=(?<account>[^ ]+))"
| rex "(?<begin>- (?<account>[^:]+): begin)"
| transaction startswith=eval(isnotnull(begin)) endswith=eval(isnotnull(returned)) account

This gives you one event per session, and will contain all of the fields from the contributing events. It will also add another field named duration, which is what it sounds like.

[babidi]

To illustrate what I am looking forward is lets consider one sample request / response from the log :

REQUEST:

05 Jan 2018 16:45:19,951 - INFO http-bio-8080-exec-409 - Inbound Message

ID: 1682148
Address: http://internalservices.abc.com/ERPPeoplesoftService/service/PsciServiceImplPort/erpService?bridgeEn...
Encoding: ISO-8859-1
Http-Method: POST
Content-Type: text/xml
Headers: {Accept=[/], breadcrumbid=[ID-XYZ-INT-ESB01-407etr-com-19853-1510587197074-50-2354125], cache-control=[no-cache], connection=[keep-alive], Content-Length=[503], content-type=[text/xml], host=[internalservices.407etr.com], pragma=[no-cache], user-agent=[Apache CXF 2.5.0]}
Payload:

  <psci:getPSAccountInfoDefault xmlns:ns2="http://erp.ABC.com" xmlns:psci="http://psci.etr407.com">
     <defaultAcctNo>123456789</defaultAcctNo>
     <doLinking>true</doLinking>
     <isUdlAccount>false</isUdlAccount>
     <source>WEB</source>
     <dateFrom>12/17/2017</dateFrom>
     <dateTo>01/05/2018</dateTo>
  </psci:getPSAccountInfoDefault>

RESPONSE:

Jan 5 16:48:25 MyAccount ERROR: com.ebpsource.ABCConsumer.Customer0: - E0 00300 Customer0 - ERROR: ETRGetPSAccountInfoDefault returned 0 records for account=123456789 loginId=JOHN@ROGERS.COM

Based on the common key word in the response and request that is getPSAccountInfoDefault I would like to fetch the result in the output format as below:

Name of WS call, Acct Number, Email ID, REQUEST DATE-TIME, RESPONSE DATE-TIME
getPSAccountInfoDefault,123456789 , JOHN@ROGERS.COM, 05 Jan 2018 16:45:19,Jan 5 16:48:25

[somesoni2]

Assuming you've all field extractions setup, try something like this

<<your base search to fetch both request and response events>>
| fields _time Account_Number, Email_Address Webservice_Call_Name
| eval Request_Time=if(searchmatch("<<search terms for request>>"),strftime(_time,"%F %T"),null()
| eval Response_Time=if(isnull(Request_Time),strftime(_time,"%F %T"),null())
| stats values(*) as * range(_time) as Difference by Account_Number
| eval Difference=tostring(Difference,"duration")

To setup clickability and drilldown, refer to these links
http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/Viz/DrilldownIntro
https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/AdvancedDev/TableChartDrilldown

[babidi]

Now fields are set up as: account,login,webservice,date,time , I am using regex to pull the webservice call name in this case PSAccountInfoDefault as search criteria..but I am not sure what should be in <> in eval Request_Time

regex _raw="(?>"),strftime(_time,"%F %T"),null()
| eval Response_Time=if(isnull(Request_Time),strftime(_time,"%F %T"),null())
| stats values(*) as * range(_time) as Difference by Account_Number
| eval Difference=tostring(Difference,"duration")

[babidi]

I am not sure what should be in <> ?

I don't have field extraction and not sure how can I do it considering the format of request and response.

Can anyone assist for these?

[babidi]

By <> I dont know what it means "your base search to fetch both request and response event"

If I understand correctly my base search would be to look for the keyword: GetPSAccountInfoDefault in both response and request in addition to accountnumber , in request it is mentioned as 123456789 and in response it comes as ERROR: ETRGetPSAccountInfoDefault returned 0 records for account=123456789

how do I translate the above search search criteria and then extract fields as I never extracted fields before and get my desired output