How to redirect DB table rows to different indexes based on the field?

[Arun_N_007]

Hi,

I have below table in my database.

Computer Application Duration
BLR0057 Calculator 30
CHN0056 MS-Word 43
DEL0078 MS-Excel 55

But I need to forward each row to different index based on Computer name.

Tried to use,

props.conf

[sourcetype::application_usage_data]
TRANSFORMS-index_extraction = index_extraction

transforms.conf

[index_extraction]
SOURCE_KEY = _raw
REGEX = .Computer="(P?\w{3}?).".*
DEST_KEY = _MetaData:Index
FORMAT = $1
WRITE_META = true

Here index names are blr, chn, del. But data is getting indexed in index name from db-inputs.conf 😞

How to achieve data routing to different indexes in DB Connect?

Regards,
Arun N

[FrankVl]

If the number of different options is rather low, it might be simpler to write separate DB Connect inputs for each option, using a where clause that selects rows where the computer field starts with a certain string and sending that input to the respective index.

[Arun_N_007]

But it will put a lot of load on the DB. We thought of fetching all rows at once.

[nickhills]

Does the index for each computer already exist?

I can see an issue with this approach in the longer term - if you add a new computer to your network, but forget to create its index your data will end up in the wrong place or dropped entirely.

Clearly if you have a larger number of computers and indexes, this will become quite troublesome to manage as your environment grows.
If you must use separate indexes, you could configure different DB queries for each compute rname, and this would allow you to be on top of the 1-1 computer-index requirements.

However - why the specific requirement for separate indexes? I cant think of any good reason why you would 'need' to use different indexes - but i also know sometimes business 'needs' drive decisions.

[Arun_N_007]

Yes, Indexes blr,chn,del are already created. We need to use separate indexes to avoid people from accessing other's data.

[nickhills]

If there a privacy issues, I think i would always prefer to create 'per customer' dbx queries, rather than rely on regex to enforce my separation - It's also easier to fix in the future!

[harsmarvania57]

Hi @Arun_N_007,

When data indexed into index from db-inputs.conf, how data looks like? Is it possible you to provide sample event (Please mask any sensitive data) ?

EDIT: If your events looks something like this Computer="BLR0057" Application="Calculator" Duration="30" after indexing then can you please try below transforms.conf on server in which DB Connect App is installed.

[index_extraction]
REGEX = Computer\=\"(\w{3}).*
DEST_KEY = _MetaData:Index
FORMAT = $1
WRITE_META = true

[Arun_N_007]

Hi @harsmarvania57,

I pushed above but not working 😞

Yes data will be in Computer="BLR0057" Application="Calculator" Duration="30" format only.

Regards,
Arun N

[harsmarvania57]

Have you applied configuration on server in which DB Connect App is installed and running?

[Arun_N_007]

Yes, Am using the single instance of Splunk.

[harsmarvania57]

Your props.conf config is worng, it should be like this

[application_usage_data]
TRANSFORMS-index_extraction = index_extraction

[Arun_N_007]

I did this also. Not working 🙂

[harsmarvania57]

Below config works perfectly fine in my environment

props.conf

[mysourcetype]
TRANSFORMS-routeall = test_route

transforms.conf

[test_route]
REGEX = Computer\=\"(\w{3}).*
DEST_KEY = _MetaData:Index
FORMAT = $1
WRITE_META = true

And after applying above configuration, restarted splunk service.

[Arun_N_007]

Yes i did the same but no luck 🙂