Dashboards & Visualizations

table creation from data

lostcauz3
Path Finder

i have data in an index=xyz in json format like with http status code from specific applications

this below is a single event data

{
  "Application1": "200",
  "Application2": "200",
  "Application3": "200"
}
 
 
 
i want the data to be visualized like


ApplicationStatusreltime
application12003 hours ago
application 2200

3 hours ago

 
how can i get output like this ?
Labels (2)
1 Solution

tscroggins
Influencer

@lostcauz3 

Here are two possibilities using foreach and transpose:

...
| stats latest(_time) as _time latest(Application*) as Application*
| foreach Application* 
    [ eval Application=mvappend(Application, "<<FIELD>>|".<<FIELD>>) ] 
| mvexpand Application
| eval reltime=tostring(now()-_time, "duration")." ago", Application=split(Application, "|"), Status=mvindex(Application, 1), Application=mvindex(Application, 0)
| table Application Status reltime
...
| stats latest(_time) as _time latest(Application*) as Application*
| transpose 0 column_name=Application
| eval reltime=case(Application=="_time", 'row 1')
| filldown reltime
| eval reltime=tostring(now()-reltime, "duration")." ago"
| search Application=Application*
| rename "row 1" as Status

In both examples, reltime is formatted as a duration (d+HH:MM:SS) with the suffix "ago." If you want to show approximate seconds, minutes, hours, or days ago:

| eval reltime=now()-reltime, reltime=coalesce(case(reltime>=86400, floor(reltime/86400). " days", reltime<86400 AND reltime>=3600, floor(reltime/3600)." hours", reltime<3600 AND reltime>=60, floor(reltime/60)." minutes"), reltime." seconds")." ago"

View solution in original post

tscroggins
Influencer

@lostcauz3 

Here are two possibilities using foreach and transpose:

...
| stats latest(_time) as _time latest(Application*) as Application*
| foreach Application* 
    [ eval Application=mvappend(Application, "<<FIELD>>|".<<FIELD>>) ] 
| mvexpand Application
| eval reltime=tostring(now()-_time, "duration")." ago", Application=split(Application, "|"), Status=mvindex(Application, 1), Application=mvindex(Application, 0)
| table Application Status reltime
...
| stats latest(_time) as _time latest(Application*) as Application*
| transpose 0 column_name=Application
| eval reltime=case(Application=="_time", 'row 1')
| filldown reltime
| eval reltime=tostring(now()-reltime, "duration")." ago"
| search Application=Application*
| rename "row 1" as Status

In both examples, reltime is formatted as a duration (d+HH:MM:SS) with the suffix "ago." If you want to show approximate seconds, minutes, hours, or days ago:

| eval reltime=now()-reltime, reltime=coalesce(case(reltime>=86400, floor(reltime/86400). " days", reltime<86400 AND reltime>=3600, floor(reltime/3600)." hours", reltime<3600 AND reltime>=60, floor(reltime/60)." minutes"), reltime." seconds")." ago"
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

What Is Splunk? Here’s What You Can Do with Splunk

Hey Splunk Community, we know you know Splunk. You likely leverage its unparalleled ability to ingest, index, ...

Level Up Your .conf25: Splunk Arcade Comes to Boston

With .conf25 right around the corner in Boston, there’s a lot to look forward to — inspiring keynotes, ...

Manual Instrumentation with Splunk Observability Cloud: How to Instrument Frontend ...

Although it might seem daunting, as we’ve seen in this series, manual instrumentation can be straightforward ...