Splunk Search
Highlighted

How to get a comma separated List

Explorer

Hello all,

I am hoping for help creating a comma separated list.  I have tried multiple different things and all have resulted in lists, but never quite what I am needing.  

I have a list of email addresses, that I need to be listed out, comma separated so that I can automate a currently manual process of updating a DLP policy.

The list would appear as follows

input data:

Email
email1@email.com

email2@email.com

email3@email.com

email4@email.com

email5@email.com

...

...

email1124@email.com

email1125@email.com

 

The output list that I need comma separated needs to be displayed as follows

EmailAddress

email1@email.com,

email2@email.com,

email3@email.com,

email4@email.com,

email5@email.com,

...

...

email1124@email.com,

email1125@email.com

 

note that the list is comma separated however the final entry does not get a comma.  This is because Symantec DLP reconizes the comma separator as an expected new entry.  If there is no comma, the final entry is expected as the last entry. 

 

I have tied stats list (this worked) however, it limits the output to 100 (I have around 1500 email addresses).  I know that I could have the limits.conf increased from 100, but I would like to avoid this just do accomplish this one task.

 

I have also tried to string the fields to string the , however it places the comma at the end of the final value in the list.
| eval EmailAddress=Email+","
| table EmailAddress

I have also tried mvjoin which just creates a giant mv field, which would be ok, expect some of the email addresses have a - in them, which then line breaks resulting in the .csv file being sent out break and have emails not formatted correctly.

I have also tried delim with a dc and values, however it also just creates a giant mv list with commas at the end of all values including the end value.

| stats delim="," dc(Email) as EmailAddressCount, values(Email) as EmailAddress
| nomv EmailAddress
| table EmailAddress

Is there a way to create the comma separated list as requested? or is there an easier way to remove the trailing character from the LAST value?

Thank you

Labels (1)
0 Karma
Highlighted

Re: How to get a comma separated List

Explorer

To generate the source data, I used:

 

| makeresults count=1500
| streamstats count
| eval email="email"+count+"@email.com"
| fields - _time, count

 

then to combine it, it used:

 

| mvcombine email delim=","
| nomv email

 

Which results in:

 

email1@email.com,email2@email.com,email3@email.com,email4@email.com,email5@email.com,email6@email.com,emai....

 

Alternatively, you can use the following to leave the email addresses in separate  events:

 

| reverse 
| streamstats count
| eval email=if(count>1,email+",",email)
| fields - count
| reverse

 

which results in:

 

email1@email.com,
email2@email.com,
email3@email.com,
email4@email.com,
...
email1499@email.com,
email1500@email.com

 

Hopefully one of those does what you're after?

View solution in original post

Highlighted

Re: How to get a comma separated List

Explorer

| reverse | streamstats count | eval email=if(count>1,email+",",email) | fields - count | reverse

This is exactly what I needed.  Thank you

Tags (1)
0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.