Getting Data In
Highlighted

How to extract a particular field from JSON event

New Member

Hi Guys ,

Below is a sample JSON event that gets logged for each transaction .

Requirement :In the attached snapshot, there is a field called latency_info under which I have task:proxy . I need to get the started time beside proxy , then substract that value from another field called timetoserve_request (not in the attached snapshot) . Please let me know how to achieve this in in SPLUNK.

0 Karma
Highlighted

Re: How to extract a particular field from JSON event

New Member

My splunk query is below . Need to add on to the below splunk query :

index="apic" sourcetype=ApiEvent catalogname=prod orgname=humana earliest=-24h
| stats count as TxCount, count(eval(statuscode="200 OK")) AS SuccessCount , count(eval(statuscode!="200 OK")) as FailureCount , min(timetoserverequest) as MinRT, avg(timetoserverequest) as AvgRT , max(timetoserverequest) as MaxRT ,p95(timetoserverequest) as p95 , p99(timetoserverequest) as p99, values(catalogname) as "Catalog Name" by apiname
|eval date=strftime(relative
time(now(), "-1d@d"), "%d-%b-%y")
| rename api_name as "API Name" , date as Date
| eval TxCount = tostring(TxCount,"commas")
| eval SuccessCount = tostring(SuccessCount,"commas")
| eval FailureCount = tostring(FailureCount,"commas")
| eval MinRT = tostring(MinRT,"commas")
| eval AvgRT=ceil(AvgRT)
| eval AvgRT = tostring(AvgRT,"commas")
| eval MaxRT = tostring(MaxRT,"commas")
| eval p95 = tostring(p95,"commas")

| eval p99 = tostring(p99,"commas")

| rename TxCount as "Transactions Count" , SuccessCount as "Success Count" , FailureCount as "Failure Count" ,MinRT as "Minimum RoundTripTime (in ms)" , AvgRT as "Average RoundTripTime (in ms)" ,MaxRT as "Maximum RoundTripTime (in ms)" ,p95 as "95thPercentile RoundTripTime (in ms)" ,p99 as "99thPercentile RoundTripTime (in ms)"
| table Date,"API Name" ,"Catalog Name", "Transactions Count" ,"Success Count" , "Failure Count" , "Minimum RoundTripTime (in ms)" , "Average RoundTripTime (in ms)" , "Maximum RoundTripTime (in ms)" , "95thPercentile RoundTripTime (in ms)" , "99thPercentile RoundTripTime (in ms)"

0 Karma
Highlighted

Re: How to extract a particular field from JSON event

New Member

{
"apiid": "5b592adee4b07bf1bfccef38",
"app
type": "PRODUCTION",
"bytesreceived": 17,
"response
body": "",
"clientid": "4abbf92b-38dc-4561-ba7d-a0eff545048f",
"billing": {
"amount": 0,
"provider": "none",
"currency": "USD",
"model": "free",
"trial
perioddays": 0
},
"datetime": "2018-11-12T02:15:40.513Z",
"time
toserverequest": 281,
"uripath": "/humana/prod/FormularyManagementServices/DrugList/AttributeValues",
"log
policy": "activity",
"endpointurl": "N/A",
"product
id": "5b592adee4b07bf1bfccef39",
"host": "127.0.0.1",
"clientip": "133.27.0.2",
"app
id": "5b312baee4b036bba7e4a948",
"clientgeoip": {
"timezone": "Asia/Tokyo",
"ip": "133.27.0.2",
"latitude": 35.3261,
"continent
code": "AS",
"cityname": "Chigasaki",
"country
code2": "JP",
"countryname": "Japan",
"country
code3": "JP",
"regionname": "Kanagawa",
"location": [139.4039, 35.3261],
"postal
code": "253-0041",
"longitude": 139.4039,
"regioncode": "14"
},
"request
protocol": "https",
"developerorgid": "5a7102d4e4b0db876ea9228b",
"transactionid": "465613565",
"immediate
clientip": "133.27.0.2",
"product
name": "formularymanagementserviceproduct",
"plan
name": "formularymanagementserviceloadtest",
"product
title": "FormularyManagementServiceProduct",
"tags": [],
"catalog
id": "59403e01e4b0b0769e4fc3cc",
"spacename": ["product"],
"api
name": "formularymanagementserviceapi",
"org
id": "59403e00e4b0b0769e4fc3c0",
"planversion": "1.0",
"status
code": "200 OK",
"requestmethod": "POST",
"developer
orgname": "product-org",
"http
useragent": "",
"@version": "1",
"response
httpheaders": [],
"org
name": "humana",
"latencyinfo": [{
"task": "Start",
"started": 0
}, {
"task": "security-appID",
"started": 9
}, {
"task": "PlanRateLimits",
"started": 10
}, {
"task": "proxy",
"started": 11
}],
"headers": {
"http
wshaprtwlmversion": "-1",
"http
via": "1.1 AQAAAKrkLHM-",
"httpversion": "HTTP/1.1",
"http
connection": "Keep-Alive",
"requestmethod": "POST",
"http
host": "localhost:9700",
"requesturi": "/bulk",
"httpxforwardedserver": "louapplpa1993.humana.com",
"content
type": "text/plain",
"httpxglobaltransactionid": "271f465d5be8e24c1bc0b32d",
"httpxforwardedhost": "133.27.28.194:9443",
"http
xforwardedfor": "133.27.0.238",
"requestpath": "/bulk",
"httporganization": "admin",
"http
xclientip": "127.0.0.1",
"contentlength": "1897"
},
"catalog
name": "prod",
"productversion": "1.0",
"rateLimit": {
"rate-limit": {
"limit": "-1",
"count": "-1"
},
"LoadTestRate": {
"shared": "true",
"period": 86400,
"unit": "day",
"reject": "false",
"limit": 10000,
"count": 364,
"interval": 1
},
"rate-limit-1": {
"limit": "-1",
"count": "-1"
},
"rate-limit-2": {
"limit": "-1",
"count": "-1"
},
"per-minute": {
"limit": "-1",
"count": "-1"
}
},
"debug": [],
"api
version": "1.0",
"bytessent": 175014,
"app
name": "FormularyEnterpriseApp",
"gateway
geoip": {
"timezone": "Asia/Tokyo",
"ip": "133.27.0.113",
"latitude": 35.3261,
"continentcode": "AS",
"city
name": "Chigasaki",
"countrycode2": "JP",
"country
name": "Japan",
"countrycode3": "JP",
"region
name": "Kanagawa",
"location": [139.4039, 35.3261],
"postalcode": "253-0041",
"longitude": 139.4039,
"region
code": "14"
},
"@timestamp": "2018-11-12T02:15:40.669Z",
"requestbody": "",
"request
httpheaders": [],
"resource
id": "formularymanagementserviceapi:1.0:post:/DrugList/AttributeValues",
"gateway
ip": "133.27.0.113",
"spaceid": ["59a0382be4b043d3d3a65e13"],
"plan
id": "formularymanagementserviceproduct:1.0:formularymanagementserviceloadtest",
"developerorgtitle": "Product Org",
"query_string": []
}

0 Karma
Highlighted

Re: How to extract a particular field from JSON event

SplunkTrust
SplunkTrust

There is no attached snapshot. Please copy-and-paste your example JSON event into a comment.

---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

Re: How to extract a particular field from JSON event

SplunkTrust
SplunkTrust

One way would be to put something like this between the initial search and the stats command

 | spath output=started path=latency_info{}.started 
 | spath output=task path=latency_info{}.task 
 | eval mycount=mvcount(started)
 | eval myfan=mvrange(0,mycount)
 | mvexpand myfan
 | eval started=mvindex(started,myfan)
 | eval task=mvindex(task,myfan)
 | where task="proxy"

After the above code, there will be one record left for each individual record, and that record will have task="proxy" and started= whatever the number was, in this case , 11.

If the JSON was already properly extracted as per spath, then multivalue fields will exist called latency_info{}.started and latency_info{}.task, and it might be possible to collapse the above code into this...

 | eval mycount=mvcount('latency_info{}.started')
 | eval myfan=mvrange(0,mycount)
 | mvexpand myfan
 | eval started=mvindex('latency_info{}.started',myfan)
 | eval task=mvindex('latency_info{}.task',myfan)
 | where task="proxy"

Run-anywhere demo code...

 | makeresults |eval _raw="{\"api_id\": \"5b592adee4b07bf1bfccef38\",\"app_type\": \"PRODUCTION\",\"bytes_received\": 17,\"response_body\": \"\",\"client_id\": \"4abbf92b-38dc-4561-ba7d-a0eff545048f\",\"billing\": {\"amount\": 0,\"provider\": \"none\",\"currency\": \"USD\",\"model\": \"free\",\"trial_period_days\": 0},\"datetime\": \"2018-11-12T02:15:40.513Z\",\"time_to_serve_request\": 281,\"uri_path\": \"/humana/prod/FormularyManagementServices/DrugList/AttributeValues\",\"log_policy\": \"activity\",\"endpoint_url\": \"N/A\",\"product_id\": \"5b592adee4b07bf1bfccef39\",\"host\": \"127.0.0.1\",\"client_ip\": \"133.27.0.2\",\"app_id\": \"5b312baee4b036bba7e4a948\",\"client_geoip\": {\"timezone\": \"Asia/Tokyo\",\"ip\": \"133.27.0.2\",\"latitude\": 35.3261,\"continent_code\": \"AS\",\"city_name\": \"Chigasaki\",\"country_code2\": \"JP\",\"country_name\": \"Japan\",\"country_code3\": \"JP\",\"region_name\": \"Kanagawa\",\"location\": [139.4039, 35.3261],\"postal_code\": \"253-0041\",\"longitude\": 139.4039,\"region_code\": \"14\"},\"request_protocol\": \"https\",\"developer_org_id\": \"5a7102d4e4b0db876ea9228b\",\"transaction_id\": \"465613565\",\"immediate_client_ip\": \"133.27.0.2\",\"product_name\": \"formularymanagementservice_product\",\"plan_name\": \"formularymanagementservice_loadtest\",\"product_title\": \"FormularyManagementService_Product\",\"tags\": [],\"catalog_id\": \"59403e01e4b0b0769e4fc3cc\",\"space_name\": [\"product\"],\"api_name\": \"formularymanagementservice_api\",\"org_id\": \"59403e00e4b0b0769e4fc3c0\",\"plan_version\": \"1.0\",\"status_code\": \"200 OK\",\"request_method\": \"POST\",\"developer_org_name\": \"product-org\",\"http_user_agent\": \"\",\"@version\": \"1\",\"response_http_headers\": [],\"org_name\": \"humana\",\"latency_info\": [{\"task\": \"Start\",\"started\": 0}, {\"task\": \"security-appID\",\"started\": 9}, {\"task\": \"PlanRateLimits\",\"started\": 10}, {\"task\": \"proxy\",\"started\": 11}],\"headers\": {\"http__ws_haprt_wlmversion\": \"-1\",\"http_via\": \"1.1 AQAAAKrkLHM-\",\"http_version\": \"HTTP/1.1\",\"http_connection\": \"Keep-Alive\",\"request_method\": \"POST\",\"http_host\": \"localhost:9700\",\"request_uri\": \"/_bulk\",\"http_x_forwarded_server\": \"louapplpa1993.humana.com\",\"content_type\": \"text/plain\",\"http_x_global_transaction_id\": \"271f465d5be8e24c1bc0b32d\",\"http_x_forwarded_host\": \"133.27.28.194:9443\",\"http_x_forwarded_for\": \"133.27.0.238\",\"request_path\": \"/_bulk\",\"http_organization\": \"admin\",\"http_x_client_ip\": \"127.0.0.1\",\"content_length\": \"1897\"},\"catalog_name\": \"prod\",\"product_version\": \"1.0\",\"rateLimit\": {\"rate-limit\": {\"limit\": \"-1\",\"count\": \"-1\"},\"LoadTestRate\": {\"shared\": \"true\",\"period\": 86400,\"unit\": \"day\",\"reject\": \"false\",\"limit\": 10000,\"count\": 364,\"interval\": 1},\"rate-limit-1\": {\"limit\": \"-1\",\"count\": \"-1\"},\"rate-limit-2\": {\"limit\": \"-1\",\"count\": \"-1\"},\"per-minute\": {\"limit\": \"-1\",\"count\": \"-1\"}},\"debug\": [],\"api_version\": \"1.0\",\"bytes_sent\": 175014,\"app_name\": \"FormularyEnterprise_App\",\"gateway_geoip\": {\"timezone\": \"Asia/Tokyo\",\"ip\": \"133.27.0.113\",\"latitude\": 35.3261,\"continent_code\": \"AS\",\"city_name\": \"Chigasaki\",\"country_code2\": \"JP\",\"country_name\": \"Japan\",\"country_code3\": \"JP\",\"region_name\": \"Kanagawa\",\"location\": [139.4039, 35.3261],\"postal_code\": \"253-0041\",\"longitude\": 139.4039,\"region_code\": \"14\"},\"@timestamp\": \"2018-11-12T02:15:40.669Z\",\"request_body\": \"\",\"request_http_headers\": [],\"resource_id\": \"formularymanagementservice_api:1.0:post:/DrugList/AttributeValues\",\"gateway_ip\": \"133.27.0.113\",\"space_id\": [\"59a0382be4b043d3d3a65e13\"],\"plan_id\": \"formularymanagementservice_product:1.0:formularymanagementservice_loadtest\",\"developer_org_title\": \"Product Org\",\"query_string\": []}\""

 | rename COMMENT as "The above just enters your sample data"
 | spath output=started path=latency_info{}.started 
 | spath output=task path=latency_info{}.task 
 | eval mycount=mvcount(started)
 | eval myfan=mvrange(0,mycount)
 | mvexpand myfan
 | eval started=mvindex(started,myfan)
 | eval task=mvindex(task,myfan)
 | where task="proxy"
0 Karma