Extracting fields from JSON - Whois Add On

[zowa]

Hello,

I am using the Whois Add On to get Whois information from ip addresses. Here is an example:

index=pan* dest!=192.168.0.0/16 dest!=10.0.0.0/8 dest!=172.16.0.0/12 | dedup dest | head 3 | rename dest as ip | lookup whoisLookup ip OUTPUT whois | table _time ip whois

It creates a new field called whois, with the information from the online whois database:

['{"DomainName":"8.8.8.8","RegistryData":{"AbuseContact":{"Email":"arin-contact@google.com","Name":"Google Inc","Phone":"+1-650-253-0000"},"AdministrativeContact":{"Email":"arin-contact@google.com","Name":"Google Inc","Phone":"+1-650-253-0000"},"BillingContact":null,"CreatedDate":"2014-03-14T16:52:05-04:00","RawText":null,"Registrant":{"Address":"1600 Amphitheatre Parkway","City":"Mountain View","Country":"US","Name":"Google Inc.","PostalCode":"94043","StateProv":"CA"},"TechnicalContact":{"Email":"arin-contact@google.com","Name":"Google Inc","Phone":"+1-650-253-0000"},"UpdatedDate":"2014-03-14T16:52:05-04:00","ZoneContact":null}}']

I am trying to use spath to extract the fields from the JSON results, but no success yet... I´ve read somewhere that Splunk 6 extract the JSON fields automatically... Well... Any idea how to extract the fields, in order to present the results in a more organized way? That would help a lot! 🙂

Thanks!

[gkanapathy · Accepted solution]

The string is not pure JSON, and spath needs a pure JSON string. You have to strip the square brackets ([]) and single quotes (') around the JSON. e.g.,

... | eval whois = substr(whois,2) | eval whois = substr(whois,-2) | ...

or something a little more precise.

[zowa]

Thanks, you put me in the right path!

The final search was:

index=pan* dest!=192.168.0.0/16 dest!=10.0.0.0/8 dest!=172.16.0.0/12 | dedup dest | head 10 |rename dest as ip | lookup whoisLookup ip OUTPUT whois | eval whois = substr(whois, 1, len(whois)-2) | eval whois = substr(whois, 3, len(whois)) | spath input=whois | table ip org city country