Getting Data In

How to extract a particular field from JSON event

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

0 Karma

DalJeanis
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

richgalloway
SplunkTrust
SplunkTrust

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

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

vishnu10987
New Member

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

0 Karma

vishnu10987
New Member

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

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...