Splunk Search

How to create table with two variables? Regex

JoshuaJohn
Contributor

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"}
0 Karma

niketn
Legend

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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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 ...