replacing a comma with a space in a token from a dashboard

[jamieadahan]

Hello All,

I have a query in my dashboard

Routing_Location="$Routing_Location$" | fillnull | stats count(_raw) AS Attempts by ANI,Routing_Location | sort -Attempts

The issue is that when someone puts in the test field for example "USA,Cellular_Verizon" no search comes up but when I put in "USA Cellular_Verizon" the search does come up I need a way to replace the comma with a space before the search take place (probably in the XML side)

I have already tried | rex field=Routing_Location mode=sed "s/(\w+)([^\w]+)(\w+)([^\w]+)(\w+)/\1 \3 \5/"
But that has no effect

Thanks in Advanced I hope someone can help!

[somesoni2 · Accepted solution]

Give this a try

[| makeresults | eval Routing_Location=replace("$Routing_Location$",","," ") | table Routing_Location] | fillnull | stats count(_raw) AS Attempts by ANI,Routing_Location | sort -Attempts

[jamieadahan]

Hi this worked well! it did take longer for my search to get results but it worked!!! thank you so much can I vote you comment as the right answer?

[somesoni2]

Here you go.

Also, for better performance, include one or more metadata fields as filters e.g. index , sourcetype, source or host. You must be searching on a finite number of index/sourcetype, so include them. query runs faster for you and less impact on your infrastructure.

[jamieadahan]

One more question I have for you. Can you explain the mix of the make results and the replace because when I looked at the documentation I didn't see anything with replace("$token$",","," ") I'm curious how it work for future reference.

Thanks so much again I've really been searching Forums for days and nothing has worked!

[nyc_jason]

Try putting double quotes around Routing_Location in the stats:

Change from this:
Routing_Location="$Routing_Location$" | fillnull | stats count(_raw) AS Attempts by ANI,Routing_Location | sort -Attempts

To this:
Routing_Location="$Routing_Location$" | fillnull | stats count(_raw) AS Attempts by ANI, "Routing_Location" | sort -Attempts

[jamieadahan]

Hi this didn't work for me. but thanks for the answer!

[richgalloway]

Have you tried | rex field=Routing_Location mode=sed "s/,/ /g"?

[jamieadahan]

Hi Rich Galloway I have tried that it hasn't worked either sadly

Routing_Location="USA,Cellular_Verizon" | fillnull | rex field=Routing_Location mode=sed "s/,/ /g" | stats count(_raw) AS Attempts by ANI, Routing_Location | sort -Attempts

Still comes up empty

[richgalloway]

The problem is not with the replacement. The problem is stats will return nothing if one of the group-by fields is null. This run-anywhere example works.

| makeresults annotate=true | eval Routing_Location="USA,Cellular_Verizon" | fillnull value="None" ANI | rex field=Routing_Location mode=sed "s/,/ /g" | stats count AS Attempts by ANI, Routing_Location | sort -Attempts