Splunk Search

regex in subsearch

flaviadonno
Explorer

Hi all,
I am trying to join 2 tables using a subsearch. The searches work as single search but not in the following subsearch format. Can you help me identify what is wrong?

index=* dataowner_id="BLA" stage=IT logtype="EL" 
| rex field=rec.msg ".*?TRANS: (?<trdate>\d{2}\.\d{2}\.\d{4} \d{2}:\d{2}:\d{2}) \w* (?<transaction>\d*) (?<userid>\w*) (?<cluster>\w*) (?<buid>\w*) (?<action>.*?) \]\]>" 
| search transaction=3456 AND action="special action" 
| convert timeformat="%d.%m.%Y %H:%M:%S" mktime(trdate) AS convtime 
| stats min(convtime) AS firsttime by userid 
| table userid firsttime 
| join type=outer userid [ search dataowner_id="BLA" stage=IT logtype="EL" 
    | rex field=rec.msg ".*?TRANS: (?<trdate>\d{2}\.\d{2}\.\d{4} \d{2}:\d{2}:\d{2}) \w* (?<transaction>\d*) (?<userid>\w*) (?<queue>.*?) \]\]>" 
    | search transaction=2345 
    | convert timeformat="%d.%m.%Y %H:%M:%S" mktime(trdate) AS convtime 
    | stats max(convtime) AS secondtime by userid 
    | table userid secondtime]

Thanks!

Tags (1)
0 Karma
1 Solution

lguinn2
Legend

How many events are returned by each search when you run them independently? You may be hitting the subsearch maximums.

I think this will be much faster and do the same thing:

index=* dataowner_id="BLA" stage=IT logtype="EL" 
| rex field=rec.msg ".*?TRANS: (?<trdate>\d{2}\.\d{2}\.\d{4} \d{2}:\d{2}:\d{2}) \w* (?<transaction>\d*) (?<userid>\w*) (?<cluster>\w*) (?<buid>\w*) (?<action>.*?) \]\]>" 
| eval eventStatus = case(transaction=3456 AND action="special action", "Start Time",
                      transaction=2345,"End Time",
                      1=1,"Other")
| where eventStatus != "Other"
| eval convTime = strptime(trdate,"%d.%m.%Y %H:%M:%S")
| stats min(convTime) AS firsttime max(convTime) as lasttime by userid eventStatus
| eval firsttime = if(eventStatus="Start Time",firsttime,null())
| eval lasttime = if(eventStatus="End Time",lasttime,null())
| stats min(firsttime) as firsttime max(lasttime) as lasttime  by userid

First, it performs the same initial search and field extraction. Then it categorizes each event based on the second search. Events that do not math either criteria are categorized as "Other" and then dropped.

In the next part of the processing, it calculates the epoch time for the trdate field and then calculates the minimum and maximum date for each user AND category. But since it only needs the firstttime for the starting category and the lasttime for the ending category, it sets the unneeded values to null.

Finally, it calculates the min and max time for each user. You don't need the table command.

Finally, rec.msg is not a valid Splunk field name. It may be accepting the name in some contexts, but not others.

View solution in original post

lguinn2
Legend

How many events are returned by each search when you run them independently? You may be hitting the subsearch maximums.

I think this will be much faster and do the same thing:

index=* dataowner_id="BLA" stage=IT logtype="EL" 
| rex field=rec.msg ".*?TRANS: (?<trdate>\d{2}\.\d{2}\.\d{4} \d{2}:\d{2}:\d{2}) \w* (?<transaction>\d*) (?<userid>\w*) (?<cluster>\w*) (?<buid>\w*) (?<action>.*?) \]\]>" 
| eval eventStatus = case(transaction=3456 AND action="special action", "Start Time",
                      transaction=2345,"End Time",
                      1=1,"Other")
| where eventStatus != "Other"
| eval convTime = strptime(trdate,"%d.%m.%Y %H:%M:%S")
| stats min(convTime) AS firsttime max(convTime) as lasttime by userid eventStatus
| eval firsttime = if(eventStatus="Start Time",firsttime,null())
| eval lasttime = if(eventStatus="End Time",lasttime,null())
| stats min(firsttime) as firsttime max(lasttime) as lasttime  by userid

First, it performs the same initial search and field extraction. Then it categorizes each event based on the second search. Events that do not math either criteria are categorized as "Other" and then dropped.

In the next part of the processing, it calculates the epoch time for the trdate field and then calculates the minimum and maximum date for each user AND category. But since it only needs the firstttime for the starting category and the lasttime for the ending category, it sets the unneeded values to null.

Finally, it calculates the min and max time for each user. You don't need the table command.

Finally, rec.msg is not a valid Splunk field name. It may be accepting the name in some contexts, but not others.

lguinn2
Legend

Thanks for the heads-up. I edited the answer, so hopefully it will run if you cut-and-paste it!

0 Karma

flaviadonno
Explorer

Wow! Cool. There was only one ")" missing at the end of the case command. Otherwise, your search did the job. Thanks a lot for the fast and effective answer.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...