topic Parsing JSON fields from log files and create dashboard charts in Dashboards & Visualizations

Parsing JSON fields from log files and create dashboard charts

girishkhadke — Thu, 24 Sep 2015 05:34:00 GMT

I have following file containing JSON :
Aug 22 13:50:15 192.168.10.100 {"NETFLOW" : [{"IPSA":"00000000","IPDA":"00000000","L4SP":"0000","L4DP":"0000","PROT":"17","MPLS":"00000","PKTS":"00000001","BYTS":"00000042"},{"IPSA":"ce8f722f","IPDA":"b5da2748","L4SP":"d52a","L4DP":"d52a","PROT":"6","MPLS":"00000","PKTS":"00000003","BYTS":"00000116"}]}

The JSON contains array of netflows.

Every line of JSON is preceded by timestamp and IP address from which the record originated.

I want to create a PIE chart containing count of different values of protocols (field : PROT in JSON). (e.g. In above PROT:17 is one netflow record and PROT:6 is another). The JSON itself is array of such elements and we would have the JSON line logged every second.

I am completely new to Splunk (Using Splunk Enterprise)and from my initial reading looks like I can do it by defining field extraction. But I am completely confused on how to use it. Also the IPSA field is HEX and I would want to convert it into DECIMAL and I do not know how to do it in splunk.

Can somebody help me in directing how basically the JSON field extraction can be achieved such that I can create PIVOT and use it to create charts.

Re: Parsing JSON fields from log files and create dashboard charts

girishkhadke — Thu, 24 Sep 2015 05:37:45 GMT

I am using Splunk 6.2.5

Re: Parsing JSON fields from log files and create dashboard charts

girishkhadke — Sun, 27 Sep 2015 20:21:05 GMT

Is there anybody that can answer this question?

Re: Parsing JSON fields from log files and create dashboard charts

muebel — Mon, 28 Sep 2015 01:20:21 GMT

For the JSON field extractions, I think INDEXED_EXTRACTIONS configuration in props.conf might lead you in the right path. The spec for props has more info here : http://docs.splunk.com/Documentation/Splunk/latest/Admin/Propsconf

To convert from hex to decimal, you can use the tonumber eval function described here : http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/CommonEvalFunctions

Re: Parsing JSON fields from log files and create dashboard charts

Suryadeep — Mon, 28 Sep 2015 09:54:15 GMT

@girishkhadke

I worked upon a similar json data format except for the HEX to Number conversion,
I took the following steps to obtain pie-charts , I guess you can make a similar attempt too.

My JSon format :---- P.S. The original format is huge, so i kept this short, you may find field names missing with the query associated below.

{
"thread": "7",
"level": "INFO" ,
"eventTime": "2015-08-13 15:05:51.1162752",
"message":
{
"date": "Thu Aug 13 2015 15:05:50 GMT-0500 (Central Daylight Time)",
"id": "btnvoe",
"outerText": "EMPLOYMENT",
"eventType": "click",
"transactionId": "9c9a713a-ae01-4299-8577-ee9293730f0c",
"browserName": "Chrome",
"browserVersion": "44",
"pageName": "Verification",
"oSNameVersion": "Windows 7",
}
}

I guess you don't need to go for separate field extractions which usually results into regex patterns.
You can try SPATH command , basically it works like pulling out fields in the form of Object DOT attribute name.

Here's my query.

index=csfindex_apilogger message.pageName=Instant OR message.pageName=InstantIncome message.id=btnNext message.appName=CSF-Poc
|table _time,message.sessionId,message.userName,message.id,message.pageName
|spath

|rename message.sessionId as sessionId,message.userName as userName,message.id as id
|where len(sessionId)>0
|eval userName= if(len(userName)=0 or isnull(userName),"Unknown user",userName)
|dedup sessionId,userName
|chart count(sessionId) as TotalSessions over userName
|sort-TotalSessions

|streamstats count as rank
|search rank>=1 rank<=4

after using spath just pullout whatever json fields you need and table them for further operations.
Here you can also declare an eval command to switch the HEX to number , refer their documentation for the appropriate command set.

And for creating pies , one must have 1 count field against a group by