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

What’s New & Next in Splunk SOAR

Security teams today are dealing with more alerts, more tools, and more pressure than ever.  Join us on ...

Your Voice Matters! Help Us Shape the New Splunk Lantern Experience

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

September Community Champions: A Shoutout to Our Contributors!

As we close the books on another fantastic month, we want to take a moment to celebrate the people who are the ...