I'm looking to extract out the sensor types that are designated by "sensor","q":"water"

[dbcase]

Hi,

I have the below data. I'm looking to extract out the sensor types which are designated by "sensor","q":"water" (i.e. its a sensor (type) and its a water (subtype) sensor). I'm really only interested in the subtype so I came up with this regex to extract it. q.:.(?<sensor_type>\w+). So far so good as this query shows what it extracted. index=wholesale_app CustomAnalytic Properties.index=7 | rex field=Properties.args{} "q.:.(?<sensor_type>\w+)"|stats count by sensor_type This query builds a table that looks like this

sensor_type count
door    40
glassBreak  17
motion  1
router  2
smoke   3
water   49
window  28

This is where the problem comes in. If you notice in the data there are some sensors that are there but not in the table. For example sensor","q":"carbonMonoxide"

In Regex101 the regex captures carbonMonoxide but in Splunk no joy. What am I missing?

     {"d":[{"d":"TCA203","f":"8_9_01_000300_201710171890","h":"5","i":"109c97261be931","j":101,"m":"Technicolor","t":"ts","x":"zigbee"},{"d":"SMCWA01-Z","f":"0x00040005","h":"1","i":"119","j":3075,"m":"SMC","n":"audible24Hr","s":"000d6f00028a0710.1","t":"sensor","q":"water","x":"zigbee"},{"d":"SMCDW02-Z","f":"0x00040005","h":"1","i":"117","j":602,"m":"SMC","n":"entryExit","s":"000d6f0004365388.1","t":"sensor","q":"window","x":"zigbee"},{"d":"SMCMT03-Z","f":"0x0004000a","h":"1","i":"113","j":103,"m":"SMC","n":"interiorFollower","s":"000d6f00027c43fd.1","t":"sensor","q":"motion","x":"zigbee"},{"d":"SMCDW02-Z","f":"0x00040005","h":"1","i":"111","j":101,"m":"SMC","n":"entryExit","s":"000d6f0003e5e908.1","t":"sensor","q":"door","x":"zigbee"},{"d":"SMCGB02-Z","f":"0x00040005","h":"1","i":"1111","j":15914,"m":"SMC","n":"perimeter","s":"000d6f000184d551.1","t":"sensor","q":"glassBreak","x":"zigbee"},{"d":"SMCSM02-Z","f":"0x0004000a","h":"1","i":"1110","j":4159,"m":"SMC","n":"fire24Hr","s":"000d6f00039c7dac.1","t":"sensor","q":"smoke","x":"zigbee"},{"d":"MCT-442 SMA","f":"0x0004000b","h":"1","i":"1112","j":18222,"m":"Visonic","n":"audible24Hr","s":"000d6f0001647cc5.1","t":"sensor","q":"carbonMonoxide","x":"zigbee"},{"d":"RC8026","f":"3.0.01.32","i":"12101.253","j":6753,"m":"iControl","s":"1410ACM017259","t":"camera","x":"wifi"},{"d":"SerComm_Camera_8021","f":"1.0.24","i":"12101.73","j":3190,"m":"iControl","s":"00C0025D9839","t":"camera","x":"wifi"},{"d":"4257050-ZHAC","f":"0x15005010","h":"3","i":"13000d6f0003bf587f.1","j":2506,"m":"CentraLite","t":"lighting","x":"zigbee"},{"d":"4257050-ZHAC","f":"0x15005010","h":"3","i":"13000d6f00031b25b9.1","j":1227,"m":"CentraLite","t":"lighting","x":"zigbee"},{"d":"3157100","f":"0x04075010","h":"3","i":"14000d6f0002f7e83f.1","j":6812,"m":"Centralite","t":"thermostat","x":"zigbee"},{"d":"CT30S","f":"0x00000592","h":"199","i":"14002446000008bb78.10","j":6482,"m":"RTCOA","t":"thermostat","x":"zigbee"},{"d":"WNR1000V2-VC","f":"V1.2.2.86","i":"15101.net.2C:B0:5D:7E:EB:E4","j":1448,"m":"NETGEAR","t":"peripheral","q":"router","x":"wifi"},{"d":"WN2000RPT","f":"1.0.0.24IC","i":"15101.net.E4:F4:C6:D7:A1:3D","j":181,"m":"Netgear","t":"peripheral","q":"wifiRepeater","x":"wifi"},{"d":"YRD210 PB DB","f":"0x00840850","h":"17","i":"16000d6f0002fbbcd3.1","j":161,"m":"Yale","t":"doorLock","x":"zigbee"}],"i":[{"i":"109c97261be931.0","m":"instance/panel","t":"TouchScreen","l":""},{"i":"119.0","m":"instance/sensor","t":"Zone","l":""},{"i":"117.0","m":"instance/sensor","t":"Zone","l":""},{"i":"113.0","m":"instance/sensor","t":"Zone","l":""},{"i":"111.0","m":"instance/sensor","t":"Zone","l":""},{"i":"1111.0","m":"instance/sensor","t":"Zone","l":""},{"i":"1110.0","m":"instance/sensor","t":"Zone","l":""},{"i":"1112.0","m":"instance/sensor","t":"Zone","l":""},{"i":"12101.253.0","m":"instance/camera","t":"Camera","l":""},{"i":"12101.73.0","m":"instance/camera","t":"Camera","l":""},{"i":"12101.253.1","m":"instance/cameraMotion","t":"CameraMotion","l":""},{"i":"13000d6f0003bf587f.1.0","m":"instance/lightSwitch","t":"Light","l":""},{"i":"13000d6f00031b25b9.1.0","m":"instance/lightDimmer","t":"Light","l":""},{"i":"14000d6f0002f7e83f.1.0","m":"instance/thermostat","t":"Thermostat","l":""},{"i":"14002446000008bb78.10.0","m":"instance/thermostat","t":"Thermostat","l":""},{"i":"15101.net.2C:B0:5D:7E:EB:E4.0","m":"instance/peripheral","t":"Peripheral","l":""},{"i":"15101.net.E4:F4:C6:D7:A1:3D.0","m":"instance/peripheral","t":"Peripheral","l":""},{"i":"16000d6f0002fbbcd3.1.0","m":"instance/doorLock","t":"DoorLock","l":""}]}  

[woodcock · Accepted solution]

You definitely need max_match=0 but I also had problems with the curly-braces so you may also need rename Properties.args{} AS Properties.args or similar. In any case, this seems to work:

| makeresults 
| eval "Properties.args"="{{\"d\":[{\"d\":\"TCA203\",\"f\":\"8_9_01_000300_201710171890\",\"h\":\"5\",\"i\":\"109c97261be931\",\"j\":101,\"m\":\"Technicolor\",\"t\":\"ts\",\"x\":\"zigbee\"},{\"d\":\"SMCWA01-Z\",\"f\":\"0x00040005\",\"h\":\"1\",\"i\":\"119\",\"j\":3075,\"m\":\"SMC\",\"n\":\"audible24Hr\",\"s\":\"000d6f00028a0710.1\",\"t\":\"sensor\",\"q\":\"water\",\"x\":\"zigbee\"},{\"d\":\"SMCDW02-Z\",\"f\":\"0x00040005\",\"h\":\"1\",\"i\":\"117\",\"j\":602,\"m\":\"SMC\",\"n\":\"entryExit\",\"s\":\"000d6f0004365388.1\",\"t\":\"sensor\",\"q\":\"window\",\"x\":\"zigbee\"},{\"d\":\"SMCMT03-Z\",\"f\":\"0x0004000a\",\"h\":\"1\",\"i\":\"113\",\"j\":103,\"m\":\"SMC\",\"n\":\"interiorFollower\",\"s\":\"000d6f00027c43fd.1\",\"t\":\"sensor\",\"q\":\"motion\",\"x\":\"zigbee\"},{\"d\":\"SMCDW02-Z\",\"f\":\"0x00040005\",\"h\":\"1\",\"i\":\"111\",\"j\":101,\"m\":\"SMC\",\"n\":\"entryExit\",\"s\":\"000d6f0003e5e908.1\",\"t\":\"sensor\",\"q\":\"door\",\"x\":\"zigbee\"},{\"d\":\"SMCGB02-Z\",\"f\":\"0x00040005\",\"h\":\"1\",\"i\":\"1111\",\"j\":15914,\"m\":\"SMC\",\"n\":\"perimeter\",\"s\":\"000d6f000184d551.1\",\"t\":\"sensor\",\"q\":\"glassBreak\",\"x\":\"zigbee\"},{\"d\":\"SMCSM02-Z\",\"f\":\"0x0004000a\",\"h\":\"1\",\"i\":\"1110\",\"j\":4159,\"m\":\"SMC\",\"n\":\"fire24Hr\",\"s\":\"000d6f00039c7dac.1\",\"t\":\"sensor\",\"q\":\"smoke\",\"x\":\"zigbee\"},{\"d\":\"MCT-442 SMA\",\"f\":\"0x0004000b\",\"h\":\"1\",\"i\":\"1112\",\"j\":18222,\"m\":\"Visonic\",\"n\":\"audible24Hr\",\"s\":\"000d6f0001647cc5.1\",\"t\":\"sensor\",\"q\":\"carbonMonoxide\",\"x\":\"zigbee\"},{\"d\":\"RC8026\",\"f\":\"3.0.01.32\",\"i\":\"12101.253\",\"j\":6753,\"m\":\"iControl\",\"s\":\"1410ACM017259\",\"t\":\"camera\",\"x\":\"wifi\"},{\"d\":\"SerComm_Camera_8021\",\"f\":\"1.0.24\",\"i\":\"12101.73\",\"j\":3190,\"m\":\"iControl\",\"s\":\"00C0025D9839\",\"t\":\"camera\",\"x\":\"wifi\"},{\"d\":\"4257050-ZHAC\",\"f\":\"0x15005010\",\"h\":\"3\",\"i\":\"13000d6f0003bf587f.1\",\"j\":2506,\"m\":\"CentraLite\",\"t\":\"lighting\",\"x\":\"zigbee\"},{\"d\":\"4257050-ZHAC\",\"f\":\"0x15005010\",\"h\":\"3\",\"i\":\"13000d6f00031b25b9.1\",\"j\":1227,\"m\":\"CentraLite\",\"t\":\"lighting\",\"x\":\"zigbee\"},{\"d\":\"3157100\",\"f\":\"0x04075010\",\"h\":\"3\",\"i\":\"14000d6f0002f7e83f.1\",\"j\":6812,\"m\":\"Centralite\",\"t\":\"thermostat\",\"x\":\"zigbee\"},{\"d\":\"CT30S\",\"f\":\"0x00000592\",\"h\":\"199\",\"i\":\"14002446000008bb78.10\",\"j\":6482,\"m\":\"RTCOA\",\"t\":\"thermostat\",\"x\":\"zigbee\"},{\"d\":\"WNR1000V2-VC\",\"f\":\"V1.2.2.86\",\"i\":\"15101.net.2C:B0:5D:7E:EB:E4\",\"j\":1448,\"m\":\"NETGEAR\",\"t\":\"peripheral\",\"q\":\"router\",\"x\":\"wifi\"},{\"d\":\"WN2000RPT\",\"f\":\"1.0.0.24IC\",\"i\":\"15101.net.E4:F4:C6:D7:A1:3D\",\"j\":181,\"m\":\"Netgear\",\"t\":\"peripheral\",\"q\":\"wifiRepeater\",\"x\":\"wifi\"},{\"d\":\"YRD210 PB DB\",\"f\":\"0x00840850\",\"h\":\"17\",\"i\":\"16000d6f0002fbbcd3.1\",\"j\":161,\"m\":\"Yale\",\"t\":\"doorLock\",\"x\":\"zigbee\"}],\"i\":[{\"i\":\"109c97261be931.0\",\"m\":\"instance/panel\",\"t\":\"TouchScreen\",\"l\":\"\"},{\"i\":\"119.0\",\"m\":\"instance/sensor\",\"t\":\"Zone\",\"l\":\"\"},{\"i\":\"117.0\",\"m\":\"instance/sensor\",\"t\":\"Zone\",\"l\":\"\"},{\"i\":\"113.0\",\"m\":\"instance/sensor\",\"t\":\"Zone\",\"l\":\"\"},{\"i\":\"111.0\",\"m\":\"instance/sensor\",\"t\":\"Zone\",\"l\":\"\"},{\"i\":\"1111.0\",\"m\":\"instance/sensor\",\"t\":\"Zone\",\"l\":\"\"},{\"i\":\"1110.0\",\"m\":\"instance/sensor\",\"t\":\"Zone\",\"l\":\"\"},{\"i\":\"1112.0\",\"m\":\"instance/sensor\",\"t\":\"Zone\",\"l\":\"\"},{\"i\":\"12101.253.0\",\"m\":\"instance/camera\",\"t\":\"Camera\",\"l\":\"\"},{\"i\":\"12101.73.0\",\"m\":\"instance/camera\",\"t\":\"Camera\",\"l\":\"\"},{\"i\":\"12101.253.1\",\"m\":\"instance/cameraMotion\",\"t\":\"CameraMotion\",\"l\":\"\"},{\"i\":\"13000d6f0003bf587f.1.0\",\"m\":\"instance/lightSwitch\",\"t\":\"Light\",\"l\":\"\"},{\"i\":\"13000d6f00031b25b9.1.0\",\"m\":\"instance/lightDimmer\",\"t\":\"Light\",\"l\":\"\"},{\"i\":\"14000d6f0002f7e83f.1.0\",\"m\":\"instance/thermostat\",\"t\":\"Thermostat\",\"l\":\"\"},{\"i\":\"14002446000008bb78.10.0\",\"m\":\"instance/thermostat\",\"t\":\"Thermostat\",\"l\":\"\"},{\"i\":\"15101.net.2C:B0:5D:7E:EB:E4.0\",\"m\":\"instance/peripheral\",\"t\":\"Peripheral\",\"l\":\"\"},{\"i\":\"15101.net.E4:F4:C6:D7:A1:3D.0\",\"m\":\"instance/peripheral\",\"t\":\"Peripheral\",\"l\":\"\"},{\"i\":\"16000d6f0002fbbcd3.1.0\",\"m\":\"instance/doorLock\",\"t\":\"DoorLock\",\"l\":\"\"}]}   d\":[{\"d\":\"TCA203\",\"f\":\"8_9_01_000300_201710171890\",\"h\":\"5\",\"i\":\"109c97261be931\",\"j\":101,\"m\":\"Technicolor\",\"t\":\"ts\",\"x\":\"zigbee\"},{\"d\":\"SMCWA01-Z\",\"f\":\"0x00040005\",\"h\":\"1\",\"i\":\"119\",\"j\":3075,\"m\":\"SMC\",\"n\":\"audible24Hr\",\"s\":\"000d6f00028a0710.1\",\"t\":\"sensor\",\"q\":\"water\",\"x\":\"zigbee\"},{\"d\":\"SMCDW02-Z\",\"f\":\"0x00040005\",\"h\":\"1\",\"i\":\"117\",\"j\":602,\"m\":\"SMC\",\"n\":\"entryExit\",\"s\":\"000d6f0004365388.1\",\"t\":\"sensor\",\"q\":\"window\",\"x\":\"zigbee\"},{\"d\":\"SMCMT03-Z\",\"f\":\"0x0004000a\",\"h\":\"1\",\"i\":\"113\",\"j\":103,\"m\":\"SMC\",\"n\":\"interiorFollower\",\"s\":\"000d6f00027c43fd.1\",\"t\":\"sensor\",\"q\":\"motion\",\"x\":\"zigbee\"},{\"d\":\"SMCDW02-Z\",\"f\":\"0x00040005\",\"h\":\"1\",\"i\":\"111\",\"j\":101,\"m\":\"SMC\",\"n\":\"entryExit\",\"s\":\"000d6f0003e5e908.1\",\"t\":\"sensor\",\"q\":\"door\",\"x\":\"zigbee\"},{\"d\":\"SMCGB02-Z\",\"f\":\"0x00040005\",\"h\":\"1\",\"i\":\"1111\",\"j\":15914,\"m\":\"SMC\",\"n\":\"perimeter\",\"s\":\"000d6f000184d551.1\",\"t\":\"sensor\",\"q\":\"glassBreak\",\"x\":\"zigbee\"},{\"d\":\"SMCSM02-Z\",\"f\":\"0x0004000a\",\"h\":\"1\",\"i\":\"1110\",\"j\":4159,\"m\":\"SMC\",\"n\":\"fire24Hr\",\"s\":\"000d6f00039c7dac.1\",\"t\":\"sensor\",\"q\":\"smoke\",\"x\":\"zigbee\"},{\"d\":\"MCT-442 SMA\",\"f\":\"0x0004000b\",\"h\":\"1\",\"i\":\"1112\",\"j\":18222,\"m\":\"Visonic\",\"n\":\"audible24Hr\",\"s\":\"000d6f0001647cc5.1\",\"t\":\"sensor\",\"q\":\"carbonMonoxide\",\"x\":\"zigbee\"},{\"d\":\"RC8026\",\"f\":\"3.0.01.32\",\"i\":\"12101.253\",\"j\":6753,\"m\":\"iControl\",\"s\":\"1410ACM017259\",\"t\":\"camera\",\"x\":\"wifi\"},{\"d\":\"SerComm_Camera_8021\",\"f\":\"1.0.24\",\"i\":\"12101.73\",\"j\":3190,\"m\":\"iControl\",\"s\":\"00C0025D9839\",\"t\":\"camera\",\"x\":\"wifi\"},{\"d\":\"4257050-ZHAC\",\"f\":\"0x15005010\",\"h\":\"3\",\"i\":\"13000d6f0003bf587f.1\",\"j\":2506,\"m\":\"CentraLite\",\"t\":\"lighting\",\"x\":\"zigbee\"},{\"d\":\"4257050-ZHAC\",\"f\":\"0x15005010\",\"h\":\"3\",\"i\":\"13000d6f00031b25b9.1\",\"j\":1227,\"m\":\"CentraLite\",\"t\":\"lighting\",\"x\":\"zigbee\"},{\"d\":\"3157100\",\"f\":\"0x04075010\",\"h\":\"3\",\"i\":\"14000d6f0002f7e83f.1\",\"j\":6812,\"m\":\"Centralite\",\"t\":\"thermostat\",\"x\":\"zigbee\"},{\"d\":\"CT30S\",\"f\":\"0x00000592\",\"h\":\"199\",\"i\":\"14002446000008bb78.10\",\"j\":6482,\"m\":\"RTCOA\",\"t\":\"thermostat\",\"x\":\"zigbee\"},{\"d\":\"WNR1000V2-VC\",\"f\":\"V1.2.2.86\",\"i\":\"15101.net.2C:B0:5D:7E:EB:E4\",\"j\":1448,\"m\":\"NETGEAR\",\"t\":\"peripheral\",\"q\":\"router\",\"x\":\"wifi\"},{\"d\":\"WN2000RPT\",\"f\":\"1.0.0.24IC\",\"i\":\"15101.net.E4:F4:C6:D7:A1:3D\",\"j\":181,\"m\":\"Netgear\",\"t\":\"peripheral\",\"q\":\"wifiRepeater\",\"x\":\"wifi\"},{\"d\":\"YRD210 PB DB\",\"f\":\"0x00840850\",\"h\":\"17\",\"i\":\"16000d6f0002fbbcd3.1\",\"j\":161,\"m\":\"Yale\",\"t\":\"doorLock\",\"x\":\"zigbee\"}],\"i\":[{\"i\":\"109c97261be931.0\",\"m\":\"instance/panel\",\"t\":\"TouchScreen\",\"l\":\"\"},{\"i\":\"119.0\",\"m\":\"instance/sensor\",\"t\":\"Zone\",\"l\":\"\"},{\"i\":\"117.0\",\"m\":\"instance/sensor\",\"t\":\"Zone\",\"l\":\"\"},{\"i\":\"113.0\",\"m\":\"instance/sensor\",\"t\":\"Zone\",\"l\":\"\"},{\"i\":\"111.0\",\"m\":\"instance/sensor\",\"t\":\"Zone\",\"l\":\"\"},{\"i\":\"1111.0\",\"m\":\"instance/sensor\",\"t\":\"Zone\",\"l\":\"\"},{\"i\":\"1110.0\",\"m\":\"instance/sensor\",\"t\":\"Zone\",\"l\":\"\"},{\"i\":\"1112.0\",\"m\":\"instance/sensor\",\"t\":\"Zone\",\"l\":\"\"},{\"i\":\"12101.253.0\",\"m\":\"instance/camera\",\"t\":\"Camera\",\"l\":\"\"},{\"i\":\"12101.73.0\",\"m\":\"instance/camera\",\"t\":\"Camera\",\"l\":\"\"},{\"i\":\"12101.253.1\",\"m\":\"instance/cameraMotion\",\"t\":\"CameraMotion\",\"l\":\"\"},{\"i\":\"13000d6f0003bf587f.1.0\",\"m\":\"instance/lightSwitch\",\"t\":\"Light\",\"l\":\"\"},{\"i\":\"13000d6f00031b25b9.1.0\",\"m\":\"instance/lightDimmer\",\"t\":\"Light\",\"l\":\"\"},{\"i\":\"14000d6f0002f7e83f.1.0\",\"m\":\"instance/thermostat\",\"t\":\"Thermostat\",\"l\":\"\"},{\"i\":\"14002446000008bb78.10.0\",\"m\":\"instance/thermostat\",\"t\":\"Thermostat\",\"l\":\"\"},{\"i\":\"15101.net.2C:B0:5D:7E:EB:E4.0\",\"m\":\"instance/peripheral\",\"t\":\"Peripheral\",\"l\":\"\"},{\"i\":\"15101.net.E4:F4:C6:D7:A1:3D.0\",\"m\":\"instance/peripheral\",\"t\":\"Peripheral\",\"l\":\"\"},{\"i\":\"16000d6f0002fbbcd3.1.0\",\"m\":\"instance/doorLock\",\"t\":\"DoorLock\",\"l\":\"\"}]}"
| rex field=Properties.args max_match=0 "q.:.(?<sensor_type>\w+)"|stats count by sensor_type

[elliotproebstel]

Are you using max_match to specify how many matches you'd like to preserve? I'm wondering if the variety of events you have in your index somehow line up to have the above extractions each as the first sensor_type match, because rex defaults to a single match. If you change the statement to use max_match=0, does that fix it?