Hi all,
I have to extract sourcetype as field in Dashboard. There are multiple sourcetype like :
oracle:audit:json, oracle:audit:json11,oracle:audit:json12,sourcetype=oracle:audit:sql11,sourcetype=oracle:audit:sql12
I have written regex :
rex mode=sed field=sourcetype "s/oracle:audit:(.*)\d\d/\1/g"
Its working fine for all the sourcetype :
oracle:audit:json11,oracle:audit:json12,sourcetype=oracle:audit:sql11,sourcetype=oracle:audit:sql12
But when the data is coming with oracle:audit:json, its not giving the result in Dashboard. Main search query is giving result.
macros definition : definition = (sourcetype=oracle:audit:json OR sourcetype=oracle:audit:json11 OR sourcetype=oracle:audit:json12 OR sourcetype=oracle:audit:sysaud OR sourcetype=oracle:audit:sysaud11 OR sourcetype=oracle:audit:sysaud12 OR sourcetype=oracle:audit:sql11 OR sourcetype=oracle:audit:sql12)
I have written macros also where i have passed all the sourcetype but getting no result or partial result in dashboard for sourcetype oracle:audit:json
Hi @shruti14,
Your regex is not capturing anything because it expects a two digits number. Please try below;
| rex mode=sed field=sourcetype "s/oracle:audit:(.*)(?:\d\d)?/\1/g"
Hi @shruti14,
it isn't so clear for me your need: each event can have only one sourcetype, so if you have more sourcetypes, this means that you have a grouping search, can you share your search?
Then do you want to extract all sourcetypes or only one, if only one, which one?
did you tried with "| mvexpand sourcetype"?
Ciao.
Giuseppe
index="abc" `abc_sourcetype` host="$Database$"
| eval sourcetype=lower(sourcetype)
| rex mode=sed field=sourcetype "s/oracle:audit:(.*)\d\d/\1/g"
| rex mode=sed field=USERHOST "s/\..*//g"
| eval ACTION_NAME=if(isnull(ACTION_NAME) OR ACTION_NAME="", "TBD", upper(ACTION_NAME))
| eval DBUSERNAME=if(isnull(DBUSERNAME) OR DBUSERNAME="" OR DBUSERNAME="TBD", "TBD", upper(DBUSERNAME))
| eval OS_USERNAME=if(isnull(OS_USERNAME) OR OS_USERNAME="" OR OS_USERNAME="TBD", "TBD", lower(OS_USERNAME))
| eval RETURN_CODE=if(isnull(RETURN_CODE) OR RETURN_CODE="", 0, RETURN_CODE)
| eval OBJECT_NAME=if(isnull(OBJECT_NAME) OR OBJECT_NAME="", "TBD", upper(OBJECT_NAME))
| eval USERHOST=if(isnull(USERHOST) OR USERHOST="", "TBD", lower(USERHOST))
| eval TERMINAL=if(isnull(TERMINAL) OR TERMINAL="" OR TERMINAL="TBD", "TBD", upper(TERMINAL))
| eval query=if(isnull(query) OR query="", "TBD", lower(query))
| stats dc(time) as Events, latest(time) as LastSeen, earliest(time) as FirstSeen by Database, sourcetype, ACTION_NAME, DBUSERNAME, OS_USERNAME, USERHOST,OBJECT_NAME, RETURN_CODE, query, TERMINAL
| convert ctime(*Seen) timeformat="%m-%d-%Y %H:%M:%S"
here abc_sourcetype is macros
so this search is working fine when sourcetype is in format oracle:audit:json12/11 but failing to load data for oracle:audit:json
how we can make it common i guess problem is regex i have wriiten its working only when sourcetype is like :
oracle:audit:json12 oracle:audit:sql11 but when oracle:audit:json its not able to extract json/sql value
oracle:audit:json12
oracle:audit:json11
oracle:audit:json
oracle:audit:sql11
can you help me with regex which can extract json and sql from able sourcetypes
My regex is oracle:audit:(.*)\d\d
If I read your intentions correctly, you want to drop any trailing numbers from sourcetype. The simplest expression would be
| rex field=sourcetype mode=sed "s/\d*$//"
Here is an emulation that you can run and compare with real data
| makeresults
| fields - _time
| eval sourcetype=split("oracle:audit:json12
oracle:audit:sql11
oracle:audit:json12
oracle:audit:json11
oracle:audit:json
oracle:audit:sql", "
")
| mvexpand sourcetype
``` data emulation above```
| eval orig = sourcetype ``` for display ```
| rex field=sourcetype mode=sed "s/\d*$//"
orig | sourcetype |
oracle:audit:json12 | oracle:audit:json |
oracle:audit:sql11 | oracle:audit:sql |
oracle:audit:json12 | oracle:audit:json |
oracle:audit:json11 | oracle:audit:json |
oracle:audit:json | oracle:audit:json |
oracle:audit:sql | oracle:audit:sql |