Hi! I'm trying to create a search that would return unique values in a record, but in one list.
The search "basesearch | table scn*"
would come up with a table where I have values across scn01 to scn20. So what I want to do is make a unique list of values combined into one column, of all of the fields values. I don't need to preserve the previous field name.
How might I do that?
Thanks!
Stephen
Hi
Check this
| makeresults
| eval scn01=23,scn02=34,scn03=55
| append
[| makeresults
| eval scn01=34,scn02=34,scn03=55]
| eval temp=""
| foreach scn*
[ eval temp=temp ." ". <<FIELD>>]
| makemv temp
| stats values(temp) as result delim=","
| nomv result
Use transpose
:
search>
| table scn*
| stats values(row1) as SCNs
Hmmm... This gets me closer, but nothing's quite right yet.
| eval scn_combine = "" | fillnull value="" | foreach scn* [append scn_combine scn*] | table scn_combine
I see the point about looping through scn(x). But I'm thinking I want to append the values to a temp table, then running another dedup on that temp table? This search tossed an error.
Thanks!
Stephen
hi @skirven,
Try this:
basesearch | eval scn_combine = "" | fillnull value="" | foreach scn* [eval scn_combine=scn_combine." ".'<<FIELD>>'] | table scn_combine
If you want scn_combine as multivalue field then:
basesearch | eval scn_combine = "" | fillnull value="" | foreach scn* [eval scn_combine=scn_combine." ".'<<FIELD>>'] | makemv scn_combine | table scn_combine
Hi
Check this
| makeresults
| eval scn01=23,scn02=34,scn03=55
| append
[| makeresults
| eval scn01=34,scn02=34,scn03=55]
| eval temp=""
| foreach scn*
[ eval temp=temp ." ". <<FIELD>>]
| makemv temp
| stats values(temp) as result delim=","
| nomv result
This gets me closer, but it's dropping all of the values into one row. I wanted to get them all in different rows, so that I could run the dedup again to get unique values.
-Stephen
This might work for what I need anyway. I was hoping to get them all in one table list, but my ultimate goal was to get it to send to a API via the JSON endpoint, so one string in that may be OK. But I couldn't get makeenv to split the values back into a list by rows.
Thanks!
Stephen
Hi
Try this
| makeresults
| eval scn01=23,scn02=34,scn03=55
| append
[| makeresults
| eval scn01=34,scn02=34,scn03=55]
| eval temp=""
| foreach scn*
[ eval temp=temp ." ". <<FIELD>>]
| makemv temp
| stats values(temp) as result
| mvexpand result
values() - Returns the list of all distinct values
AHA! mvexpand
!
| table scn*
| eval temp=""
| foreach scn*
[ eval temp=temp ." ". <<FIELD>>]
| makemv temp
| stats values(temp) as result delim=","
| makemv delim="," result
| mvexpand result
Thanks everyone!
@skirven, Use foreach loop.
For example below-
|makeresults|eval scn01="abc", scn02="bcd", scn10="ddd", scn14="ert",total="" | foreach scn* [eval total= total." <<FIELD>>"]|makemv total delim=" "|mvexpand total