Splunk Search

Using Rex to combine multiple fields in separate columns

MJA411
Explorer

Hello Splunk Community!

 

I was hoping if someone can help me out here. I have been having problems adding a third field to an existing query that generates statistical data for SSL expiring in the next 90 days. 

I am able to get the fields "name" and "expirationDate" to display but cannot add a field "subject" to the equation here.

Current search query is:

 

 

index="test" sourcetype="test:test1:json" source="test.test2" 
| spath path=ssl output=fred 
| rex field=fred max_match=0 "\"name\":\"(?<name>.*?)\"}" 
| rex field=fred max_match=0 "\"expirationDate\":(?<expirationDate>.*?)," 
| dedup name 
| eval bob = mvzip(name,expirationDate) 
| mvexpand bob 
| rex field=bob "(?<name>.*),(?<expirationDate>.*)" 
| eval t=now() 
| where  expirationDate >= t AND expirationDate <= (t + 7776000)
| eval expiry=strftime(expirationDate, "%F %T.%3N") 
| table host name expiry

 

 

Expected output is:

 

host            name              expiry

abc              test1              2021-07-09 10:10:10.000

 

I want to add a new field "subject" which I did the following but whenever "expirationDate" is added to the equation I am getting no results. 

 

index="test" sourcetype="test:test1:json" source="test.test2" 
| spath path=ssl output=fred
| rex field=fred max_match=0 "\"name\":\"(?<name>.*?)\"}" 
| rex field=fred max_match=0 "\"expirationDate\":(?<expirationDate>.*?)," 
| rex field=fred max_match=0 "\"subject\":(?<subject>.*?),"
| dedup subject name
| eval bob = mvzip(name,expirationDate,subject) 
| mvexpand bob 
| rex field=bob "(?<name>.*),(?<expirationDate>.*),(?<subject>.*)" 
| eval t=now() 
| where  expirationDate >= t AND expirationDate <= (t + 7776000)
| eval expiry=strftime(expirationDate, "%F %T.%3N") 
| table host name expiry subject

 

 

Grateful if you could point out my mistake here! I believe it's a wrong expression but cannot figure it out (it works fine without the conversion of the expiration date, but it comes under 1 row, which is also no ideal as I am hoping to separate entries into different rows.

 

Thanks,

MJA

Labels (6)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@MJA411 

Can you please try this?

index="test" sourcetype="test:test1:json" source="test.test2" 
| spath path=ssl output=fred
| rex field=fred max_match=0 "\"name\":\"(?<name>.*?)\"}" 
| rex field=fred max_match=0 "\"expirationDate\":(?<expirationDate>.*?)," 
| rex field=fred max_match=0 "\"subject\":(?<subject>.*?),"
| dedup subject name
| eval bob = mvzip(mvzip(name,expirationDate),subject)
| mvexpand bob 
| rex field=bob "(?<name>.*),(?<expirationDate>.*),(?<subject>.*)" 
| eval t=now() 
| where  expirationDate >= t AND expirationDate <= (t + 7776000)
| eval expiry=strftime(expirationDate, "%F %T.%3N") 
| table host name expiry subject

 

My Sample Search :

| makeresults 
| eval _raw="[{\"name\":\"Test Name\",\"expirationDate\":\"2021-07-09 10:10:10.000\",\"subject\":\"subject one\"},{\"name\":\"Test Name\",\"expirationDate\":\"2021-07-09 10:10:10.000\",\"subject\":\"subject one\"}]" 
| spath
| rename {}.* as *
| dedup subject name 
| eval bob = mvzip(mvzip(name,expirationDate),subject)
| mvexpand bob 
| rex field=bob "(?<name>.*),(?<expirationDate>.*),(?<subject>.*)" 
| table host name expirationDate subject bob

 

If you have any other type of sample data then pleas share here.

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

View solution in original post

MJA411
Explorer

Hi KV,

 

Amazing! Just what I was looking for 🙂 I believe I was missing another mvzip command to tie the fields!

Please accept this as a solution to my query and thank you so much for your help here.

 

Regards,

MJA

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@MJA411 

Can you please try this?

index="test" sourcetype="test:test1:json" source="test.test2" 
| spath path=ssl output=fred
| rex field=fred max_match=0 "\"name\":\"(?<name>.*?)\"}" 
| rex field=fred max_match=0 "\"expirationDate\":(?<expirationDate>.*?)," 
| rex field=fred max_match=0 "\"subject\":(?<subject>.*?),"
| dedup subject name
| eval bob = mvzip(mvzip(name,expirationDate),subject)
| mvexpand bob 
| rex field=bob "(?<name>.*),(?<expirationDate>.*),(?<subject>.*)" 
| eval t=now() 
| where  expirationDate >= t AND expirationDate <= (t + 7776000)
| eval expiry=strftime(expirationDate, "%F %T.%3N") 
| table host name expiry subject

 

My Sample Search :

| makeresults 
| eval _raw="[{\"name\":\"Test Name\",\"expirationDate\":\"2021-07-09 10:10:10.000\",\"subject\":\"subject one\"},{\"name\":\"Test Name\",\"expirationDate\":\"2021-07-09 10:10:10.000\",\"subject\":\"subject one\"}]" 
| spath
| rename {}.* as *
| dedup subject name 
| eval bob = mvzip(mvzip(name,expirationDate),subject)
| mvexpand bob 
| rex field=bob "(?<name>.*),(?<expirationDate>.*),(?<subject>.*)" 
| table host name expirationDate subject bob

 

If you have any other type of sample data then pleas share here.

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

Get Updates on the Splunk Community!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...