Getting Data In

How to convert JSON into specific table format

rsharma1984
Explorer

This what we have in logs: index="xyz" INFO certvalidationtask

And this prints a JSON object which consists of a list of commonName + ExpirationDate

Stage.env e401a4ee-1652-48f6-8785-e8536524a317 [APP/PROC/WEB/0] - - 2020-02-10 16:09:01.525  INFO 22 --- [pool-1-thread-1] c.a.c.f.c.task.CertValidationTask        : {commonName='tiktok.com', expirationDate='2020-05-21 17:50:20'}{commonName='instagram.com', expirationDate='2020-07-11 16:56:37'}{commonName='blahblah.com', expirationDate='2020-12-08 11:30:42'}{commonName='advantage.com', expirationDate='2020-12-10 11:41:31'}{commonName='GHGHAGHGH', expirationDate='2021-05-19 08:34:03'}{commonName='Apple Google Word Wide exercise', expirationDate='2023-02-07 15:48:47'}{commonName='some internal cert1', expirationDate='2026-06-22 13:02:27'}{commonName='Some internal cert2', expirationDate='2036-06-22 11:23:21'}

I wanted a table which contains 2 columns -> Common Name & Expiration Date. Where if the expiration date is less than 30 days from the current date we show that in RED color, for less than 90 days we show in Yellow, everything else in Green.

Much much thanks in Advanced.

0 Karma
1 Solution

nickhills
Ultra Champion

You could do this with rex and some eval

index="xyz" INFO certvalidationtask 
|rex max_match=0 "commonName=\'(?P<commonName>[^\']+)\'\,\sexpirationDate=\'(?P<expirationDate>[^\']+)"
|eval temp=mvzip(commonName,expirationDate,"#")
|mvexpand temp
|rex field=temp "(?<commonName>.+)#(?<expirationDate>.+)"
|eval expiresInDays=floor((strptime(expirationDate, "%Y-%m-%d %H:%M:%S") - now())/86400)
|table commonName expirationDate expiresInDays

Then you can use table formatting to set your colours

If my comment helps, please give it a thumbs up!

View solution in original post

nickhills
Ultra Champion

You could do this with rex and some eval

index="xyz" INFO certvalidationtask 
|rex max_match=0 "commonName=\'(?P<commonName>[^\']+)\'\,\sexpirationDate=\'(?P<expirationDate>[^\']+)"
|eval temp=mvzip(commonName,expirationDate,"#")
|mvexpand temp
|rex field=temp "(?<commonName>.+)#(?<expirationDate>.+)"
|eval expiresInDays=floor((strptime(expirationDate, "%Y-%m-%d %H:%M:%S") - now())/86400)
|table commonName expirationDate expiresInDays

Then you can use table formatting to set your colours

If my comment helps, please give it a thumbs up!

nickhills
Ultra Champion

Whoops - overlooked its multivalued.
Edited to also use mvzip and mvexpand, otherwise it would only work for for the first cert.

If my comment helps, please give it a thumbs up!
0 Karma

rsharma1984
Explorer

Thank you Genius @nickhillscpl

Any idea on coloring?

0 Karma

nickhills
Ultra Champion

In the table view, click the little paintbrush icon at the top of each column. Select Colour-Ranges

If my comment helps, please give it a thumbs up!
0 Karma

rsharma1984
Explorer

Thanks Boss

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...