topic Re: How to extract field from data like this? in Splunk Search

How to extract field from data like this?

cflam — Sat, 27 Jan 2018 05:54:52 GMT

Hi All,

I am working on some weather RSS indexing, some of the data look like this.

King's Park| 17 degrees ; Wong Chuk Hang| 16 degrees ; Ta Kwu Ling| 17 degrees ;

How can I use rex command to extract the location name (such as King's Park) and degree (such as 17)?

seems regular expression need a special way to handle pipe, but I didn't find a detail solution/example on Internet.

Thanks!

Wallace

Re: How to extract field from data like this?

493669 — Sat, 27 Jan 2018 06:07:01 GMT

hey try this:

(?<location>[^|;]+)\|\s(?<degree>\d+)

https://regex101.com/r/oPNc69/1

Try this anywhere run search:

| makeresults |eval tt="King's Park| 17 degrees ; Wong Chuk Hang| 16 degrees ; Ta Kwu Ling| 17 degrees ;"
|makemv tt  delim=";"|mvexpand tt|rex field=tt "(?<location>[^|;]+)\|\s(?<degree>\d+)"

Re: How to extract field from data like this?

mayurr98 — Sat, 27 Jan 2018 06:19:42 GMT

Try this run anywhere search

| makeresults 
| eval raw="King's Park| 17 degrees,Wong Chuk Hang| 16 degrees,Ta Kwu Ling| 17 degrees"
| makemv raw delim=","
| mvexpand raw  
| rex field=raw "(?<location_name>[^\|]+)\|\s(?<degree>[^\s]+)"

In your environment, you should write

| rex field=_raw  "(?<location_name>[^\|]+)\|\s(?<degree>[^\s]+)"

This will work only when your event starts with King's Park| 17 degrees in this format otherwise it will give you false results.
Can you give full event, so that regex can be made accurate.

Let me know if this helps!

Re: How to extract field from data like this?

cflam — Tue, 29 Sep 2020 17:51:48 GMT

Hi All,

Thanks so much for the swift respond and sharing!

Regarding the whole event, I use RSS feed so it looks like this in Splunk.

01/27/2018 14:27:38 UTC, _time="1517034458.0", author="hkowm@hko.gov.hk", author_detail.email="hkowm@hko.gov.hk", guidislink="False", id="http://rss.weather.gov.hk/rss/CurrentWeather/20180127220200", link="http://www.weather.gov.hk/wxinfo/currwx/current.htm", links.0.href="http://www.weather.gov.hk/wxinfo/currwx/current.htm", links.0.rel="alternate", links.0.type="text/html", published="Sat, 27 Jan 2018 14:02:00 GMT", published_parsed="2018-01-27T14:02:00Z", summary=" At 10 p.m. at the Hong Kong Observatory : Air temperature : 16 degrees Celsius Relative Humidity : 84 per cent The air temperatures at other places were: Hong Kong Observatory| 16 degrees ; ---|--- King's Park| 15 degrees ; Wong Chuk Hang| 16 degrees ; Ta Kwu Ling| 16 degrees ; Lau Fau Shan| 16 degrees ; Tai Po| 16 degrees ; Sha Tin| 16 degrees ; Tuen Mun| 16 degrees ; Tseung Kwan O| 15 degrees ; Sai Kung| 16 degrees ; Cheung Chau| 16 degrees ; Chek Lap Kok| 17 degrees ; Tsing Yi| 17 degrees ; Shek Kong| 17 degrees ; Tsuen Wan Ho Koon| 15 degrees ; Tsuen Wan Shing Mun Valley| 16 degrees ; Hong Kong Park| 16 degrees ; Shau Kei Wan| 15 degrees ; Kowloon City| 16 degrees ; Happy Valley| 16 degrees ; Wong Tai Sin| 16 degrees ; Stanley| 16 degrees ; Kwun Tong| 15 degrees ; Sham Shui Po| 17 degrees ; Kai Tak Runway Park| 16 degrees ; Yuen Long Park| 17 degrees ; Tai Mei Tuk| 15 degrees . ", summary_detail.base="http://rss.weather.gov.hk/rss/CurrentWeather.xml", summary_detail.language="None", summary_detail.type="text/html", summary_detail.value=" At 10 p.m. at the Hong Kong Observatory : Air temperature : 16 degrees Celsius Relative Humidity : 84 per cent The air temperatures at other places were: Hong Kong Observatory| 16 degrees ; ---|--- King's Park| 15 degrees ; Wong Chuk Hang| 16 degrees ; Ta Kwu Ling| 16 degrees ; Lau Fau Shan| 16 degrees ; Tai Po| 16 degrees ; Sha Tin| 16 degrees ; Tuen Mun| 16 degrees ; Tseung Kwan O| 15 degrees ; Sai Kung| 16 degrees ; Cheung Chau| 16 degrees ; Chek Lap Kok| 17 degrees ; Tsing Yi| 17 degrees ; Shek Kong| 17 degrees ; Tsuen Wan Ho Koon| 15 degrees ; Tsuen Wan Shing Mun Valley| 16 degrees ; Hong Kong Park| 16 degrees ; Shau Kei Wan| 15 degrees ; Kowloon City| 16 degrees ; Happy Valley| 16 degrees ; Wong Tai Sin| 16 degrees ; Stanley| 16 degrees ; Kwun Tong| 15 degrees ; Sham Shui Po| 17 degrees ; Kai Tak Runway Park| 16 degrees ; Yuen Long Park| 17 degrees ; Tai Mei Tuk| 15 degrees . ", tags.0.label="None", tags.0.scheme="None", tags.0.term="R", title="Bulletin updated at 22:02 HKT 27/01/2018", title_detail.base="http://rss.weather.gov.hk/rss/CurrentWeather.xml", title_detail.language="None", title_detail.type="text/plain", title_detail.value="Bulletin updated at 22:02 HKT 27/01/2018"

Re: How to extract field from data like this?

493669 — Sat, 27 Jan 2018 17:47:11 GMT

Hi @cflam [Splunk],
Try below rex:

...|rex field=_raw max_match=0 "\;(\s---\|---)*\s(?<location>[^\|]+)\|\s(?<degree>\d+)"| eval reading=mvzip(location, degree) | mvexpand reading| makemv reading delim=","| eval location=mvindex(reading, 0)     
 | eval degree=mvindex(reading, 1)|table degree location|dedup degree location

Let me know if it works...
Thanks

Re: How to extract field from data like this?

mayurr98 — Sat, 27 Jan 2018 21:06:00 GMT

hey try this :

index=<your_index> | rex field=_raw max_match=0 "((\s---\|---\s)|(;\s))(?<location_name>[^\|]+)\|\s(?<degree>[^\s]+)\sdegrees"

let me know if this helps!