How to extract sourcetype as field in dashboard?

[shruti14]

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

[scelikok]

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"

[gcusello]

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

[shruti14]

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

[shruti14]

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

[yuanliu]

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