How to show only single values of different fields in a single table?

[hwakonwalk]

I am very new to Splunk and have a requirement to show current values of multiple fields in a single table, my data goes as:

Heart_rate, respiration_rate, body_temprature
76          14                39

I want to show the details in the table as:

VitalName           CurrentValue     MaxValue (1hour) MinValue (1hour)  maxValue (12hours) minValue(12hrs)
HeartRate           76               80               65                81                 64
RespirationRate     14               15               13                16                 12
BodyTemprature      39               40               36                41                 35

Please help me out with a sample search

[DalJeanis · Accepted solution]

Assuming that your data looks like this -

_time HeartRate RespirationRate BodyTemprature

This would be the initial search that should get you the most recent value, and the max and min for the 1hour and 12 hour windows.

[your search here]
earliest=-12h
| sort 0 _time
| streamstats window=1h
    min(HeartRate) as MinHart01, min(RespirationRate) as MinResp01, min(BodyTemprature) as MinTemp01,
    max(HeartRate) as MaxHart01, max(RespirationRate) as MaxResp01, max(BodyTemprature) as MaxTemp01
| stats 
    min(HeartRate) as MinHart12, min(RespirationRate) as MinResp12, min(BodyTemprature) as MinTemp12,
    max(HeartRate) as MaxHart12, max(RespirationRate) as MaxResp12, max(BodyTemprature) as MaxTemp12
    latest(MinHeart01) as MinHart01, latest(MinResp01) as MinResp01, latest(MinTemp01) as MinTemp01,
    latest(MaxHeart01) as MaxHart01, latest(MaxResp01) as MaxResp01, latest(MaxTemp01) as MaxTemp01
    latest(HeartRate) as CurHart, latest(RespirationRate) as CurResp, latest(BodyTemprature) as CurTemp,

That gets you all your 15 values.

Explanation - the streamstats does a running total of your 1 hour window and attaches the value to each transaction. It only really mattered for the very last transaction, so we could have done a subsearch/lookup instead, but the search wouldn't look any less complicated for you.

After the eventstats calculates the smaller window, the stats command then looks across the entire 12 hour window, and gets the min/max for that. I've regularized the names so they look like - (Cur|Min|Max) (Hart|Resp|Temp) (01|12)

This version is efficient enough for searching a single patient. On the other hand, if this search is intended to get this data for ALL your current patients, or something like that, then let me know that and I can look at making it more efficient for that purpose. Also, we'd need to identify the patient with some kind of ID field in the streamstats and stats commands.

---

Okay, this next part takes the values we collected above and formats them into your report. I'm sure there is a more elegant way, but I couldn't find it off the top of my head and a quick google search. Once again, this code is aimed so that you will understand exactly what it does, not so that it is the most efficient way. Okay?

| eval MySplitField1=mvappend("Hart","Resp","Temp")
| mvexpand MySplitField1
| eval VitalName=case(MySplitField1="Hart","HeartRate", MySplitField1="Resp","RespirationRate", MySplitField1="Temp","BodyTemprature")
| eval CurrentValue = case(MySplitField1=="Hart",CurHart, MySplitField1=="Resp",CurResp, MySplitField1=="Temp",CurTemp)
| eval Max01 = case(MySplitField1=="Hart",MaxHart01, MySplitField1=="Resp",MaxResp01, MySplitField1=="Temp",MaxTemp01)
| eval Max12 = case(MySplitField1=="Hart",MaxHart12, MySplitField1=="Resp",MaxResp12, MySplitField1=="Temp",MaxTemp12)
| eval Min01 = case(MySplitField1=="Hart",MinHart01, MySplitField1=="Resp",MinResp01, MySplitField1=="Temp",MinTemp01)
| eval Min12 = case(MySplitField1=="Hart",MinHart12, MySplitField1=="Resp",MinResp12, MySplitField1=="Temp",MinTemp12)
| rename Max01 as "MaxValue (1hour)", Min01 as "MinValue (1hour)", 
                  Max12 as "MaxValue (12hours)", Min12 as "MinValue (12hours)"
| table VitalName, CurrentValue, "MaxValue (1hour)", "MinValue (1hour)",  "MaxValue (12hours)", "MinValue (12hours)"

Explanation of above code -

We need to split up the single statistics line into three different lines, one for each type of data. The simple way to do this is to create a multivalue field with the three VitalName values, and then use mvexpand to create one record for each one of the values.

We then use each of the values to load ONLY the data for that VitalName into the event fields we are going to actually report on.

Finally, we rename the fields to whatever you are looking for in the title.

Final NOTE - I have left the spelling of BodyTemprature the way you had it. You can do a global change all in the code to BodyTemperature if it is correctly spelled in your database.

---

updated to fix = to ==, fix some quotes and tighten the formatting to save people from scrolling endlessly. 😉

[hwakonwalk]

It worked very well for me, thank you very much DalJeanis!
I appreciate that you provided me with resolution with proper explanation, I am able to understand it and I am sure that there is lot for me to learn about Splunk, I hope someday I am also able to contribute to Splunk and its community
Now I have a question about hardcoded time values (1hour and 12hours), will this time span hold true even if user searches in real time (5 minutes or 30 minutes Window)?

[DalJeanis]

Sorry, I missed the question a few weeks back when you asked it.

The hard code above is as per the original problem statement. it would work fine with autorefresh - not quite realtime but close.

If you wanted a version specific to a real time search , it would probably be a little different, especially if the user was going to be selecting different time ranges. Don't change the overall time range if you want to keep that 12 hours of information. Just give them a shorter time they can poll across.

First, change the stats command in line 7 to streamstats window=12h. Add another streamstats for the new duration, XX, which you'll have to pass in as a parameter. Name all the fields using the same naming conventions, maybe MinHartXX and so on.

| streamstats window=12h
     min(HeartRate) as MinHart12, min(RespirationRate) as MinResp12, min(BodyTemprature) as MinTemp12,
     max(HeartRate) as MaxHart12, max(RespirationRate) as MaxResp12, max(BodyTemprature) as MaxTemp12
| streamstats window=1h
     min(HeartRate) as MinHart01, min(RespirationRate) as MinResp01, min(BodyTemprature) as MinTemp01,
     max(HeartRate) as MaxHart01, max(RespirationRate) as MaxResp01, max(BodyTemprature) as MaxTemp01
| streamstats window=$XX$
     min(HeartRate) as MinHartXX, min(RespirationRate) as MinRespXX, min(BodyTemprature) as MinTempXX,
     max(HeartRate) as MaxHartXX, max(RespirationRate) as MaxRespXX, max(BodyTemprature) as MaxTempXX
 | stats 
     latest(MinHeart01) as MinHart01, latest(MinResp01) as MinResp01, latest(MinTemp01) as MinTemp01,
     latest(MaxHeart01) as MaxHart01, latest(MaxResp01) as MaxResp01, latest(MaxTemp01) as MaxTemp01
     latest(HeartRate) as CurHart, latest(RespirationRate) as CurResp, latest(BodyTemprature) as CurTemp

Then you're going to have to decode the new duration at the end into the field names/titles. Not sure of any elegant way to do that..