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
Motivator

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

@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"
Get Updates on the Splunk Community!

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...