topic Re: Is there an efficient way to convert booleans in my data from 0 and 1 to true and false? in Splunk Search

Is there an efficient way to convert booleans in my data from 0 and 1 to true and false?

dkrichards16 — Fri, 09 Jun 2017 17:45:09 GMT

Hi,

I'm switching from dbquery to dbxquery and I noticed that it brings in booleans as 0/1 instead of true/false. For my reports/dashboards I'd like them to read as true/false. I got the following to work, but was wondering if there was a way to do it for all booleans in a report without setting an if statement for each field:

|eval LogA=if(LogA=1,"True","False")
|eval LogB=if(LogB=1,"True","False")

I've been doing some research on isbool, but not seeing a ton of documentation on it yet. Does anyone out there have a more efficient way to do that?

Re: Is there an efficient way to convert booleans in my data from 0 and 1 to true and false?

cmerriman — Fri, 09 Jun 2017 18:13:28 GMT

i'm not sure if it is more efficient or not, but another option is |replace "1" with "True", "0" with "False" in LogA, LogB or |foreach Log* [eval <<FIELD>>=if('<<FIELD>>'=1,"True","False")]

Re: Is there an efficient way to convert booleans in my data from 0 and 1 to true and false?

DalJeanis — Fri, 09 Jun 2017 18:17:33 GMT

Macros appear to be a pita also, since there is no single documentation page that describes what eval verbs you can and cannot use, and there are only trivial examples in the documentation, but ...

HERE's the one you need.

convertbool(1)

args = fieldlist
definition = replace(replace("$fieldlist$"," (\s+)"," ")." ","(\w+)\s","| eval \1 = if(\1=1,\"True\",\"False\") ")
iseval = true

description = takes a single string of fieldnames separated by one or more spaces, and creates the code to convert each field from 1 to True or 0 to False.  Does not require a pipe character before call.

A run-anywhere example call is

| makeresults 
| eval LogA=1 | eval LogB=0 | eval LogC=1
`convertbool("LogA LogB LogC")`

Notice that the names of the fields are passed as a single string of an arbitrary number of arguments with spaces between the names. You don't have to worry if you have a single or multiple spaces between the names... that has been coded for.

Example output is

LogA    LogB    LogC    _time
True    False   True    2017-06-09 21:28:33

here's the version if you prefer the style with a pipe before the macro

definition = replace(replace(replace("$fieldlist$"," (\s+)"," ")." ","(\w+)\s","| eval \1 = if(\1=1,\"True\",\"False\") "),"^\|","")

sample call (same other code as above and same output)

| `convertbool("LogA LogB LogC")`

Update... format to call the macro turned out to be different, see above.

splunk doesn't handle booleans very well. What you have there looks fairly efficient, although it would probably be a pain to code much of.

My suggestion is to code a macro that turns...

`convertbool( LogA LogB ...)

into

 |eval LogA=if(LogA=1,"True","False")
 |eval LogB=if(LogB=1,"True","False")
 ...

Re: Is there an efficient way to convert booleans in my data from 0 and 1 to true and false?

dkrichards16 — Fri, 09 Jun 2017 18:21:58 GMT

The replace didn't created some null values but the foreach worked and shaved .668 sec off of my query. lol

Re: Is there an efficient way to convert booleans in my data from 0 and 1 to true and false?

dkrichards16 — Fri, 09 Jun 2017 18:22:38 GMT

I'm not super familiar with macros in Splunk yet but I'll keep this in mind. Thanks

Re: Is there an efficient way to convert booleans in my data from 0 and 1 to true and false?

woodcock — Fri, 09 Jun 2017 19:23:57 GMT

I would cast them in the SQL, rather than convert them in the SPL.