Splunk Search

How to extract interesting fields from JSON data pulled from Google Places' API?

lukas_loder
Communicator

Hi!

I'm trying to get Information from Google Places into our Splunk. We want to analyze how we get rated on social media.
I'm able to use the API and get all the Information with the RAST app in a json Format.

Unfortunately, the fields which are interesting have this Name result.reviews{}.author_name . And I'm not able to use these fields in a Data model and/or in Pivot. Does somebody know this Problem? I already tried it with Field alias result.reviews{}.author_Name = _AuthorName but I don't get this one in Splunk.

{
   "html_attributions" : [],
   "result" : {
      "address_components" : [
         {
            "long_name" : "Golden Gate Bridge",
            "short_name" : "Golden Gate Bridge",
            "types" : [ "route" ]
         },
         {
            "long_name" : "California",
            "short_name" : "CA",
            "types" : [ "administrative_area_level_1", "political" ]
         },
         {
            "long_name" : "Vereinigte Staaten",
            "short_name" : "US",
            "types" : [ "country", "political" ]
         }
      ],
      "adr_address" : "\u003cspan class=\"street-address\"\u003eGolden Gate Bridge\u003c/span\u003e, \u003cspan class=\"region\"\u003eCalifornia\u003c/span\u003e, \u003cspan class=\"country-name\"\u003eVereinigte Staaten\u003c/span\u003e",
      "formatted_address" : "Golden Gate Bridge, California, Vereinigte Staaten",
      "formatted_phone_number" : "(415) 921-5858",
      "geometry" : {
         "location" : {
            "lat" : 37.819929,
            "lng" : -122.478255
         }
      },
      "icon" : "http://maps.gstatic.com/mapfiles/place_api/icons/generic_business-71.png",
      "id" : "2213bf496e53a020f356d0274a5a75a4a98907fa",
      "international_phone_number" : "+1 415-921-5858",
      "name" : "Golden Gate Bridge",
      "photos" : [
         {
            "height" : 460,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/116252465308456831481\"\u003eSirena Rios\u003c/a\u003e"
            ],
            "photo_reference" : "CnRoAAAAUNNiCRDBUFiy-B4yGw4-jrggo5DZ1FETN3V3crzIlXG5S59Rjut1tfvJqOwz8Xxs_O4hJ7uQZEIQSBFjlMhfzD0tG1ch0sfl246QUe5xrwsKVBTaf47UuT7nmNPUpU_C1x1yIBRV5V9he5wbnGjnbhIQJPJ5IAjeyUmlgNEULUqh1RoU524MuyQhy2waajQ5C3NVMn4sZKo",
            "width" : 808
         },
         {
            "height" : 360,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/102187346347125126278\"\u003ehitops61\u003c/a\u003e"
            ],
            "photo_reference" : "CnRvAAAAsvPdawhTUkj-P61vqkrFjs3A0VT8TqKTWEOWD60kVc-9piF06BkoNw4Sw2hNd5WHj1ugLSlDzfgaZx1oO5uJ5m-kxWn02FP-rv6zPZsdrtSe7sruD9hc_hW9FwGfpy7OPwqT8oGWs019tDn01_GEARIQ_tDckrOcC8ajUK829NyjMxoUog5RWHZRtCncgPuHT1Z517lp36U",
            "width" : 480
         },
         {
            "height" : 1490,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/107569050817939542355\"\u003eCharo Perez Torrego\u003c/a\u003e"
            ],
            "photo_reference" : "CqQBkwAAAPzqyTiq7YALbd3fyeDZPIZMEoj_dDY4YNqBSK5fHEcmL22ILMji8dUsUVPQi-pHz7BoVbA7LCIWapCU6ffj7nKeJJWeYTcU-ufGeMVMW3-0aJv2FAlnnEuFdQyuS-LJ-iYuABiNnDLvwaYWBW-kAFv_fYBFNJAMuOBmqPXfaxavn5QDKpFGwIU_UaS7HNpVLUhfcQJLJoNu4Y0h0kmQFu54SEGkzIOYjNt3VKrLW_q4vilwaFDVjU75AgnP0r9nhfo-26XkBbEFd",
            "width" : 1500
         },
         {
            "height" : 352,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/113191727760949412949\"\u003eVan Huu\u003c/a\u003e"
            ],
            "photo_reference" : "CnRoAAAAuVQST-Btusn_bGOYGGjd0UeTvpWinirj71HKr1hj7UjAYElOqFCQ0hMQhg2lMyjil8jNPL4YvDGx1gJvLFzHVi81Tm2HM4aZOGuHMynuMoL5Sx7G936nxqGJGg_zkL8MdlmsJea3LRCh6Ljjv9w0WBIQ6DLO_rM4PuTZRkN7aHTdehoUD6CKZwgj0q9G8erQyBf7uTyvdyc",
            "width" : 800
         },
         {
            "height" : 450,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/105984049565006804495\"\u003eTami Moen\u003c/a\u003e"
            ],
            "photo_reference" : "CnRoAAAABKsd5uTiKn-8pTVr8zPuUz-svE5XE9FVcxd37eiX4LmFYM3bZJa0d0OxGnFQnr-LIBHS6FxBae_nWDY-cw3BwGQ2_U2ePxxcoR1-JcOGqPXfEBH27O_NPp2NVd7uPTw6k1s-86ThCmWh4mcJSxHmpRIQKIuelY37zJza7gLAx_taVxoUcD3JcYV_gG_6Y4_6t5tAVf8mTJM",
            "width" : 800
         },
         {
            "height" : 480,
            "html_attributions" : [ "Von einem Google-Nutzer" ],
            "photo_reference" : "CoQBdAAAAL-Uln0CGiqS_EY1n4rjZ8KKDEFJsmNUjJbXR8pxv3ATfk0UK8owRxvRkD7IlK-i4eK3uHDgGFrdO4KQp-a4EweFCRF6M_lA7sUPp8drsZDEtZ7Lgptl6dkI0GO9_OQMPfQNccdOyd11bEzfIn_zUawoEr9ZtPCyPh3M8C-VTIOotEhAbLeGWKl18LXM5fGJb0gn3GhQ7IRKyFQB9xqtTRS4zNI8wG7iCVA",
            "width" : 640
         },
         {
            "height" : 816,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/107565519404076123865\"\u003ePrasham Sheth\u003c/a\u003e"
            ],
            "photo_reference" : "CoQBdAAAAGiDC4ydgHtY9FnctMOmjwPik_-VpE-aacy285gP41pjry2Zex_YnbdlNdwX9mdsOjMclwLhaPlO1Sz3FMVAutftKj7PgyYcENbCCBA6mRXvzGcwoQrO2fKuWQb_RrspaLjuSvroCNZEv2MmbKEtHGu0DYrEQl7zXJ_mnxTDdtFMEhCd45G5iOjhaUAWwcGHWkPFGhQQq35Lur4PNmvtsD1dRRcBNdIRZQ",
            "width" : 612
         },
         {
            "height" : 1200,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/117001986278088134060\"\u003eStephen SteveT\u003c/a\u003e"
            ],
            "photo_reference" : "CnRi-AAAAfRxD3Zu6LNmch2BUVeISNHnVyqQ0sAj9GNM5as9Wj3pk_pvW-T1hW1NCMTJCUWwF32kW_TN1yvfoVNIEce2Hi1zTXBqO4_5kIdsyT3jRuLtsLk1TK88V4TEuaOrZgMGfZeewG6ouzwSubQIhJFN98hIQ_T9hrq-U2aC3Ex0_AhWPFhoUh6jx9IGV6O2xTywH1JiPB_HYz40",
            "width" : 900
         },
         {
            "height" : 480,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/115792059750378980460\"\u003eAntónio Matos\u003c/a\u003e"
            ],
            "photo_reference" : "CnRoAAAAESKkwwsmwjbd0D8VH9xYrPY-qCN8gam0De0KC9UWPLTFP6fiCDPC6ryxLuMf-4QV7V36l6D-ZGDTcdwjqClimKExLIH3d1AezfFYaUTIEP0nVd8gcv9I5RwNztnrF5zOs79ikwihUYtENaO4hi5dxhIQY2k3rxN_HKg8reG6wQ5X4RoUpv0NDIekESH-9GRphSzhjMn8XBE",
            "width" : 640
         },
         {
            "height" : 612,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/113489281302232740823\"\u003eDavid Burkett\u003c/a\u003e"
            ],
            "photo_reference" : "CnRoAAAAVnmf2BVct9UrBiZbMdZiCAWXkPpPmUHHkQiJN72CAAto4InQeKktGQoFwiO9V7_dmleXWGwtpOx-gJpZR4R22eL4yLKkft9dhmNu7rgaIqexvbDvEWx5GSyGpy4CKepTi0XBY1RHlKN2eXcsU6DFLuhIQZeU-zzeeRYH355pSj0RDGhoUVPr3a-EeWJWK-1gM1Bf0W0XPRdA",
            "width" : 816
         }
      ],
      "place_id" : "ChIJw____96GhYARCVVwg5cT7c0",
      "rating" : 4.7,
      "reference" : "CnRmAAAAqIpRW4ANK4KvJMGBEn3lR5R2M15zUEfW0UVf3zvk5c3dGs_5PTx0WpECI5Ktm16nQ6nFRJW-PtfBCD9aD9mE8yILW1DZGIuuNx-WtdBpSW8vQ9uRAN3J68aA70h1lMtkHDySMhWwe59UvpwKXwAkdxIQsgku6hyetLN6grGLOr-iWBoU8n2KvEqDLlI65S0xooUWjQEoXa8",
      "reviews" : [
         {
            "aspects" : [
               {
                  "rating" : 3,
                  "type" : "overall"
               }
            ],
            "author_name" : "Aaron Richter",
            "author_url" : "https://plus.google.com/111270458209006049005",
            "language" : "de",
            "rating" : 5,
            "text" : "Es war atemberaubend dieser ausblick in die ferne einfach nur episch ",
            "time" : 1433583287
         },
         {
            "aspects" : [
               {
                  "rating" : 3,
                  "type" : "overall"
               }
            ],
            "author_name" : "Fabian Prinz-Arnold",
            "author_url" : "https://plus.google.com/109400312077468092625",
            "language" : "de",
            "rating" : 5,
            "text" : "Guter Kaffee. ",
            "time" : 1428647728
         },
         {
            "aspects" : [
               {
                  "rating" : 3,
                  "type" : "overall"
               }
            ],
            "author_name" : "Carolin Eberhard",
            "author_url" : "https://plus.google.com/108342380563416174190",
            "language" : "de",
            "rating" : 5,
            "text" : "coole Brücke\n",
            "time" : 1427735795
         },
         {
            "aspects" : [
               {
                  "rating" : 3,
                  "type" : "overall"
               }
            ],
            "author_name" : "Leon M.",
            "author_url" : "https://plus.google.com/109837476853552751747",
            "language" : "de",
            "rating" : 5,
            "text" : "Coole Brücke geile aussicht\n",
            "time" : 1427121695
         },
         {
            "aspects" : [
               {
                  "rating" : 2,
                  "type" : "appeal"
               },
               {
                  "rating" : 2,
                  "type" : "facilities"
               },
               {
                  "rating" : 2,
                  "type" : "service"
               }
            ],
            "author_name" : "Manuel Ihl",
            "author_url" : "https://plus.google.com/107685098902545654125",
            "language" : "de",
            "rating" : 4,
            "text" : "Ja was soll mach sagen... AMAZING... die absolute Hauptattraktion der Stadt ist definitiv die Golden Gate Bridge. Der Ausblick den man hier hat die beeindruckend und das Ambi-ente natuerlich auch.\n\nEin Tipp (wenn auch kein geheimer) - am besten bei schoenem Wetter geniessen... :)",
            "time" : 1362335658
         }
      ],
      "scope" : "GOOGLE",
      "types" : [ "establishment" ],
      "url" : "https://plus.google.com/105065405533145638947/about?hl=de-CH",
      "user_ratings_total" : 828,
      "utc_offset" : -420,
      "vicinity" : "Golden Gate Bridge",
      "website" : "http://www.goldengatebridge.org/"
   },
   "status" : "OK"
}
1 Solution

fdi01
Motivator

try like

...| spath input=json path="result.reviews{}.author_Name"|rename  result.reviews{}.author_Name as _AuthorName  |...

View solution in original post

fdi01
Motivator

try like

...| spath input=json path="result.reviews{}.author_Name"|rename  result.reviews{}.author_Name as _AuthorName  |...

lukas_loder
Communicator

Hi fdi01, you helped me!

I found the right helppage from splunk.
index=test sourcetype=Googleplaces | spath | rename result.reviews{}.rating AS ReviewRating, result.reviews{}.author_name AS ReviewName, result.reviews{}.text AS ReviewText

This one worked for me.
With this I made it to seperate column

index=test sourcetype=Googleplaces | spath | rename result.reviews{}.rating AS ReviewRating, result.reviews{}.author_name AS ReviewName, result.reviews{}.text AS ReviewText | eval x=mvzip(ReviewRating,ReviewName) | eval y=mvzip(x,ReviewText) | mvexpand y | eval y = split(y,",") | eval Rating=mvindex(y,0) | eval AuthorName=mvindex(y,1) | eval RatingText=mvindex(y,2) | table _time,Rating, AuthorName, RatingText

fdi01
Motivator

i am happy for you. you can up vote or accepted if you ok
thank. Mr lukas.loder

0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...