How to merge Splunk queries to a single query?

[GRC]

I need a help from you. Could you please help me to generate a single query from these 3 separate queries ?

The index is same in 1 & 2 queries. The source types of all 3 are different. Thank you.

1. index="abc_oracle" source=audit_19c sourcetype="audit"

| eval "Database Modifications:" = "Modification on " + host, "Date and Time" = TIMESTAMP, "Type" = SQL_TEXT, "User" = DB_USER , "Source" = sourcetype

| search "Database Modifications:"="Modification on *"

NOT select | rex field=_raw "SQL_TEXT=\S(?P<Type>\W?......)\s"

| rex field=_raw "DB_USER=(?P<UserName>..........)"

| table "Date and Time", "Database Modifications:" ,"Type", "User", "Source"



2. index="abc_oracle" source=audit_row_19c sourcetype="audit"

| eval "Database Modifications:" = "Modification on " + host, "Date and Time" = TIMESTAMP, "Type" = SQL_TEXT, "User" = DB_USER , "Source" = sourcetype

| search "Database Modifications:"="Modification on *"

NOT select | rex field=_raw "SQL_TEXT=\S(?P<Type>\W?......)\s"

| rex field=_raw "DB_USER=(?P<UserName>..........)"

| table "Date and Time", "Database Modifications:" ,"Type", "User", "Source"



3. index="abc_11g" source=oracle_11g sourcetype="audit"

| eval "Database Modifications:" = "Modification on " + host, "Date and Time" = TIMESTAMP_qab, "Type" = SQL_TEXT, "User" = DB_USER , "Source" = sourcetype

| search "Database Modifications:"="Modification on *"

NOT select | rex field=_raw "SQL_TEXT=\S(?P<Type>\W?......)\s"

| rex field=_raw "DB_USER=(?P<UserName>..........)"

| table "Date and Time", "Database Modifications:" ,"Type", "User", "Source"

Thank you

[bowesmana · Accepted solution]

Seems like the only difference is the TIMESTAMP_qab for the abc_11g index data, so this should do it, using an if statement for the timestamp setting

sourcetype="audit" (index="abc_oracle" (source=audit_19c OR source=audit_row_19c)) OR (index="abc_11g" source=oracle_11g)
| eval "Database Modifications:" = "Modification on " + host,
       "Date and Time" = if(index="abc_11g", TIMESTAMP_qab, TIMESTAMP),
       "Type" = SQL_TEXT,
       "User" = DB_USER,
       "Source" = sourcetype
| search "Database Modifications:"="Modification on *" NOT select
| rex field=_raw "SQL_TEXT=\S(?P<Type>\W?......)\s"
| rex field=_raw "DB_USER=(?P<UserName>..........)"
| table "Date and Time", "Database Modifications:" ,"Type", "User", "Source"

I am not sure what your 'search "Data..." statement is doing. I am assuming some rows may end up with NULL values, otherwise it's superfluous. If you are ending up with NULL, then you could probably filter them out earlier in the piece.

[GRC]

Hi @bowesmana ,

This query is working. Thank you so much !

Really appreciate your help. 

Cheers

[PickleRick]

They have the same sourcetype, just different sources. If I see correctly (I'm still before my morning coffee ;)), the resto of the searches after the initial conditions seems pretty much the same. So you can just use alternative

index=abc_oracle sourcetype=audit (source=audit_19c OR source=audit_row_19c OR source=oracle_11g)

Notice the parentheses and capital ORs

[GRC]

Hi @PickleRick ,

One index is missing. But I got what you said. This works. Thank you so much, I really appreciate your help.

Cheers