Getting Data In

Creating a tabular result along with chart

babidi
New Member

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.

0 Karma

micahkemp
Champion

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.

0 Karma

babidi
New Member

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

0 Karma

somesoni2
Revered Legend

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

0 Karma

babidi
New Member

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

0 Karma

babidi
New Member

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?

0 Karma

babidi
New Member

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

0 Karma
Get Updates on the Splunk Community!

Exporting Splunk Apps

Join us on Monday, October 21 at 11 am PT | 2 pm ET!With the app export functionality, app developers and ...

Cisco Use Cases, ITSI Best Practices, and More New Articles from Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Build Your First SPL2 App!

Watch the recording now!.Do you want to SPL™, too? SPL2, Splunk's next-generation data search and preparation ...