topic How to extract a particular field from JSON event in Getting Data In

How to extract a particular field from JSON event

vishnu10987 — Tue, 29 Sep 2020 22:02:13 GMT

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 time_to_serve_request (not in the attached snapshot) . Please let me know how to achieve this in in SPLUNK.

Re: How to extract a particular field from JSON event

vishnu10987 — Tue, 29 Sep 2020 22:02:16 GMT

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

index="apic" sourcetype=ApiEvent catalog_name=prod org_name=humana earliest=-24h
| stats count as TxCount, count(eval(status_code="200 OK")) AS SuccessCount , count(eval(status_code!="200 OK")) as FailureCount , min(time_to_serve_request) as MinRT, avg(time_to_serve_request) as AvgRT , max(time_to_serve_request) as MaxRT ,p95(time_to_serve_request) as p95 , p99(time_to_serve_request) as p99, values(catalog_name) as "Catalog Name" by api_name
|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)"

Re: How to extract a particular field from JSON event

richgalloway — Sun, 11 Nov 2018 15:02:59 GMT

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

Re: How to extract a particular field from JSON event

vishnu10987 — Tue, 29 Sep 2020 21:57:55 GMT

{
"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": []
}

Re: How to extract a particular field from JSON event

DalJeanis — Mon, 12 Nov 2018 04:42:28 GMT

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"