Splunk Search

How to rename dbxquery fields in dashboards?

Engager

I am converting many dashboards from using dbquery to dbxquery. I have a few hundred of these queries to convert, with thousands if fields involved.

One of the problems is that dbxquery either returns fields in alphabetical order (unlike dbquery which returns them in schema order), or if you specify shortnames=false you can get them in schema order, but the field names include the order number and the data type!

I am thinking I have to use shortnames=false in order to get the schema order, and try to rename the fields back to their original names. The fields come out like "(001).FIELDA.VARCHAR2", "(002).FIELDB.NUMBER" etc.

I was hoping to make a macro that would rename these fields back to FIELDA, FIELDB etc. If I include all the different datatypes, I can do:

| rename *.VARCHAR2 as *, *.NUMBER as *, etc.

I can also have a long list of:

`| rename "(001).*" as *," "(002).*" as * etc`. 

However, these matches don't work. If I remove the "." then they work, but I wind up with fields named .FIELDA, .FIELDB etc. (all starting with a dot). I also wonder if these field names have a new line character in them -- they always appear on different lines and maybe that's why the number and the dot together won't match.

My questions are: 1) can I do this with renames at all? 2) can I do this with more of a regex search? or 3) is there some other way to get dbxquery to return fields in the schema order using shortnames=true?

I have seen other postings complaining about this behavior. It's also a problem if your SQL query lists fields in a particular order -- again they get switched to alphabetical when made into Splunk fields.

Thanks, Rick

0 Karma

SplunkTrust
SplunkTrust

Okay, so the problem is that, once you rename the fields, they aren't guaranteed to be in the desired order anyway.

That means you need to, in essence, create a search that builds the rename command and one that builds the table command. Here's a starter set for you.


| makeresults | fields - _time | eval "(001).FIELDA.VARCHAR2" = "value1",   "(002).FIELDB.VARCHAR2" = "value2"  | head 1
| rename COMMENT as "replace the above with your search that gets exactly one record with all the fields"
| eval junk="junk"
| untable junk oldname fieldvalue
| fields - junk
| rex field=oldname "^[\d()]{5}\.(?<xxxx>[^.]+)\."
| fields xxxx
| format "| table " "" "" "" "" ""
| rex mode=sed field=search "s/\"//g s/xxxx=//g"
| table search
| rename COMMENT as "the field search now contains your desired table command"

| makeresults | fields - _time | eval "(001).FIELDA.VARCHAR2" = "value1",   "(002).FIELDB.VARCHAR2" = "value2" | head 1
| rename COMMENT as "replace the above with your search that gets exactly one record with all the fields"
| eval junk="junk"
| untable junk oldname fieldvalue
| rex field=oldname "^[\d()]{5}\.(?<newname>[^.]+)\."
| fields junk oldname newname 
| xyseries junk oldname newname
| fields - junk
| format "| rename " "" "," "" "" ""
| rex mode=sed field=search "s/=/ as /g"
| table search
| rename COMMENT as "the field search now contains your desired rename command"

It might be possible to put the above into a single search, but we don't see an easy way. Put the above two searches -- after you've replaced the makeresults lines with something that pulls a data record -- into square brackets and they will return SPL that contains the desired rename and table commands for that kind of record.

0 Karma

Explorer

If you're willing to explicitly write out all the field values you could use dbxquery with shortnames=true and then add to your search

| table fieldA fieldB fieldC...

If the database table changed or was re-ordered this would break though. I'm still thinking about other options.

0 Karma