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!

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 ...