topic Re: Help to remove brackets and commas from data, sort into a CSV, and dedup in Getting Data In

Help to remove brackets and commas from data, sort into a CSV, and dedup

bpolsen — Wed, 30 Aug 2017 16:15:58 GMT

I have data which looks like the following:

[000003074859, 000003075752, 000003224575, 000003228286, 000003235217, 000003246379, 000003246434, 000003246725, 000003246934, 000003248574]
[0010242946, 0002363081, 000006459131, 0010275565, 000000430019, 000000465470, 000000465546, 000003228900, 000003616661, 000003648249]

I would like to:

1) Remove the brackets and commas
2) Change the CSV into one row per entry
3) dedup

So, the final data would look like:
000003074859
000003075752
000003224575
....................

Thank you very much in advance

Re: Help to remove brackets and commas from data, sort into a CSV, and dedup

cpetterborg — Wed, 30 Aug 2017 16:20:53 GMT

You could do this at search time, and have a set of events with one of the items per event. Is that sufficient? Or do you require that the data come in to be indexed with one item per event?

Re: Help to remove brackets and commas from data, sort into a CSV, and dedup

bpolsen — Wed, 30 Aug 2017 16:37:37 GMT

The data I show is the result of a search, and shows two events. I'd like to show the data as a dedup'ed series of rows, one entry per row. Thanks!

Re: Help to remove brackets and commas from data, sort into a CSV, and dedup

niketn — Wed, 30 Aug 2017 16:48:15 GMT

If the data shown here is result of search what is the fieldname?

Re: Help to remove brackets and commas from data, sort into a CSV, and dedup

niketn — Wed, 30 Aug 2017 16:55:39 GMT

Following is a run-anywhere search based on your example. Pipes till | table data generates mock data. You would require the pipes after that. Also replace with your field name instead of data

| makeresults
| eval data="[000003074859, 000003075752, 000003224575, 000003228286, 000003235217, 000003246379, 000003246434, 000003246725, 000003246934, 000003248574]"
| append 
    [| makeresults
    | eval data="[0010242946, 0002363081, 000006459131, 0010275565, 000000430019, 000000465470, 000000465546, 000003228900, 000003616661, 000003648249]"]
| table data
| eval data=replace(replace(data,"\[",""),"\]","")
| eval data=split(data,",")
| mvexpand data
| sort num(data)

Re: Help to remove brackets and commas from data, sort into a CSV, and dedup

cpetterborg — Wed, 30 Aug 2017 16:58:20 GMT

And a little simpler version thane niketnilay's:

|  makeresults 
|  eval _raw="[000003074859, 000003075752, 000003224575, 000003228286, 000003235217, 000003246379, 000003246434, 000003246725, 000003246934, 000003248574]
[0010242946, 0002363081, 000006459131, 0010275565, 000000430019, 000000465470, 000000465546, 000003228900, 000003616661, 000003648249]" 
|  rex max_match=0 "(?P<myfield>\d+)" 
|  mvexpand myfield 
|  table myfield
|  dedup myfield

Re: Help to remove brackets and commas from data, sort into a CSV, and dedup

bpolsen — Wed, 30 Aug 2017 17:08:54 GMT

Gentlemen,
Thank you so very much! I was able to cobble together a search that meets my needs, thanks to your guidance. Much appreciated!

Re: Help to remove brackets and commas from data, sort into a CSV, and dedup

niketn — Wed, 30 Aug 2017 17:13:42 GMT

@bpolsen, if one of the answer here has helped, do not forget to Accept the same to mark it as answered. Please also up vote the answered that have helped you. If you used something other than the examples here then kindly add that as answer and accept the same.

Re: Help to remove brackets and commas from data, sort into a CSV, and dedup

niketn — Wed, 30 Aug 2017 18:26:58 GMT

@cpetterborg... upvoting... obviously this is simpler working example 🙂