topic Re: How to create table with two variables? Regex in Splunk Search

How to create table with two variables? Regex

JoshuaJohn — Fri, 17 Mar 2017 15:26:11 GMT

Hi, I am very rusty with my splunk. I have this query:

index=nitros_prod_stores_servers sourcetype=_json OR sourcetype=xs_json host=isp** | rex field=_raw "locId(?.*)+w" | rex field=_raw "macaddress(?.*)+w"| stats locId by macaddress | dedup

I want to use regex to grab this location number 0775 then use regex to grab this mac address 00-16-7F-EE-DD-17. Then have a list populate showing which mac addresses are in which locations and remove the duplicates.

Location numbers and mac addresses will all be different there are many events that need to be sorted in this way. (Here are a few examples):

{"bdy":{"msg":"NitrosApplication_OnLaunched event triggered.","metricName":"AppStart","metricValue":"NitrosApplication_OnLaunched","measuredTime":"00:00:00.7181610"},"hdr":{"level":"Information","timestamp":"2017-03-17T15:00:55.9692895Z","lineNum":0,"userId":"a211ba03eb3aa1","loc":"Store","locId":"0775","ip":"10.434.24.4","hostName":"W-W10ME-7534513","macaddress":"00-16-7F-EE-DD-17","eventid":0,"appVersion":"10.0.2","appName":"L"},"ver":"0.1"}

{"bdy":{"msg":"Background Task 'DevicePowerCheckBackgroundTask' is Running..."},"hdr":{"level":"Information","timestamp":"2017-03-17T15:00:55.842Z","fxsrc":"Run","lineNum":53,"loc":"Store","locId":"0320","ip":"10.439.3.11","hostName":"K-W10ME-054232","macaddress":"00-13-7F-13-33-29","eventid":0,"appVersion":"3.0.2","appName":"L"},"ver":"0.1"}

{"bdy":{"msg":"SplashPage loaded on back click.","metricName":"PageLoad","metricValue":"SplashPage","measuredTime":"00:00:00.0006669"},"hdr":{"level":"Information","timestamp":"2017-03-17T15:00:55.3022117Z","lineNum":0,"loc":"Store","locId":"0466","ip":"10.111.11.7","hostName":"K-W10ME-3727099","macaddress":"00-15-7E-GE-D2-11","eventid":0,"appVersion":"16.2.0","appName":"L"},"ver":"0.1"}

Re: How to create table with two variables? Regex

gcusello — Fri, 17 Mar 2017 15:43:51 GMT

Hi JoshuaJohn,
does this search run?
probably after stats there is an aggregation function (as count or dc...) and dedup has a field.
Try something like this:

index=nitros_prod_stores_servers sourcetype=_json OR sourcetype=xs_json host=isp* 
| rex "\"locId\":\"(?<locId>[^\"]*)\",\"ip\":\"[^\"]*\",\"hostName\":\"[^\"]*\",\"macaddress\":\"(?<macaddress>[^\"]*)\""
| stats count by locId macaddress

Test regex in https://regex101.com/r/EJd50g/1
Bye.
Giuseppe

Re: How to create table with two variables? Regex

niketn — Fri, 17 Mar 2017 16:53:44 GMT

Since your sourcetype is json can you try spath? Following is one of your test data with run anywhere example. Moreover, I would expect Splunk to perform automatic field extraction for json sourcetype using KV_MODE=json in props.conf. Have you looked at interesting fields in verbose mode? (Splunk documentation on KV_MODE: http://docs.splunk.com/Documentation/Splunk/latest/Admin/Propsconf)

| makeresults
| eval jsonData="{\"bdy\":{\"msg\":\"NitrosApplication_OnLaunched event triggered.\",\"metricName\":\"AppStart\",\"metricValue\":\"NitrosApplication_OnLaunched\",\"measuredTime\":\"00:00:00.7181610\"},\"hdr\":{\"level\":\"Information\",\"timestamp\":\"2017-03-17T15:00:55.9692895Z\",\"lineNum\":0,\"userId\":\"a211ba03eb3aa1\",\"loc\":\"Store\",\"locId\":\"0775\",\"ip\":\"10.434.24.4\",\"hostName\":\"W-W10ME-7534513\",\"macaddress\":\"00-16-7F-EE-DD-17\",\"eventid\":0,\"appVersion\":\"10.0.2\",\"appName\":\"L\"},\"ver\":\"0.1\"}"
| spath input=jsonData path=hdr.macaddress output=macaddress