- Splunk Answers
- :
- Splunk Administration
- :
- Deployment Architecture
- :
- Re: DB Connect Column Aliases
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
DB Connect Column Aliases
Is anyone having an issues with assigning column aliases in a mysql db?
SELECT DISTINCT
issuestatus.pname AS "Status"
FROM issuestatus
pname
1 Open
2 Assigned
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If this is in the Database Input or Database Query feature - I found a workaround for this. Simply cast all the fields and it should work.
SELECT DISTINCT CAST(issuestatus.pname AS Char(32)) AS Status FROM issuestatus
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This workaround, well, works! I've been looking for a way to get aliases working for ages now and this finally does it. Thanks!
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I think I found the root cause of this problem in the README notes for MySQL J connector 5.1.x which states that .getColumnName() now always returns the original column name and the caller must use .getColumnLabel() to get the alias.
Downgrading from 5.1.4 to 3.14 solved the problem for me, although I'm sure it will cause other problems down the road. I have reported the issue to Splunk via our dealer.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Did you attempt to exclude the quotes around the Status?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Anyone ever find a solution for this?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Do not be resolved in a way and a separate column by editing the column?
ex.
| dbquery mysql01 limit=1000 "select users.id+0 as user_id from users"
(result)
user_id
1
2
3
How about now?
SELECT DISTINCT issuestatus.pname+"" AS "Status" FROM issuestatus
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am having a same problem as well with using the 'AS' in select statement.
You cannot join multiple tables that has same column names if you cant
use the AS.
I hope there is a solution for this.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I tried escaping the quotes as suggested, but it didn't fix the problem for me. Any other suggestions?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You need to escape the quotes that are used in the column alias.
| dbquery "database" "SELECT DISTINCT issuestatus.pname AS \"Status\" FROM issuestatus"
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, I got the same problem! Perhaps the parser is not considering convert the alias from SQL to SPL, I think...
SELECT user as username FROM mysql.user WHERE user='root';
Database Results
User
1 root
2 root
Threat Hunting Unlocked: How to Uplevel Your Threat Hunting With the PEAK Framework ...
Splunk APM: New Product Features + Community Office Hours Recap!
Index This | Forward, I’m heavy; backward, I’m not. What am I?
