Using Rex to combine multiple fields in separate columns

[MJA411]

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

[MJA411]

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

[kamlesh_vaghela · Accepted solution]

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