Splunk query for Visualization

[uagraw01]

Hello Splunkers!

I want a below visualization as per attached screenshot. I have mentioned complete SPL also. Please let me know how to achieve it.

index=ABC sourcetype="stalogmessage" | fields _raw | spath output=statistical_element "StaLogMessage.StatisticalElement" | spath output=statistical_subject "StaLogMessage.StatisticalElement.StatisticalSubject" | fields - _raw | spath input=statistical_element output=statistical_item "StatisticalItem" | spath input=statistical_item output=StatisticalId "StatisticalId" | spath input=statistical_item output=Value "Value" | spath input=statistical_subject output=SubjectType "SubjectType" | mvexpand SubjectType | where SubjectType="ORDER_RECIPE" | lookup detail_lfl.csv StatisticalID as StatisticalId SubjectType as SubjectType OUTPUTNEW SymbolicName Unit | mvexpand Unit | search Unit="%" | mvexpand SymbolicName | where SymbolicName="UTILISATION"
| mvexpand Value
| mvexpand StatisticalId
| table StatisticalId Value Unit

[uagraw01]

index=ABC sourcetype="stalogmessage" 
| fields _raw 
| spath output=statistical_element "StaLogMessage.StatisticalElement" 
| spath output=statistical_subject "StaLogMessage.StatisticalElement.StatisticalSubject" 
| fields - _raw
| mvexpand statistical_element  
| mvexpand statistical_subject
| spath input=statistical_element output=statistical_item "StatisticalItem"
| spath input=statistical_item output=StatisticalId "StatisticalId"
| spath input=statistical_item output=Value "Value"
| spath input=statistical_subject output=SubjectType "SubjectType"
| where SubjectType="ORDER_RECIPE"
| stats count by StatisticalId Value SubjectType _time
| lookup detail_lfl.csv StatisticalID as StatisticalId SubjectType as SubjectType OUTPUTNEW SymbolicName
| mvexpand SymbolicName
| where SymbolicName="UTILISATION" 
|  strcat "raw" "," SymbolicName group_name
| stats min(Value) AS min_value, max(Value) AS max_value, sum(Value) AS sum_value, count AS count BY SymbolicName group_name StatisticalId _time
| eval min_value=coalesce(min_value,value), max_value=coalesce(max_value,value), sum_value=coalesce(sum_value,value), count=coalesce(count,1) 
| fields StatisticalId min_value max_value sum_value count group_name _time
| dedup StatisticalId _time group_name
| fields - _virtual_ _cd_ 
| fillnull value="" 
| timechart span=1h minspan=3600s eval(round(min(min_value),2)) AS "Minimum", eval(round(max(max_value),2)) AS "Maximum", eval(round(sum(sum_value),2)) AS summed, eval(round(sum(count),2)) AS counted 
| eval "Average" = round(summed/counted, 2) 
| fields - summed counted

As I am using above query to visualize the graph in Maximum , minimum and average. But my values are looking different.

 Expected result I want :

@bowesmana Please help me what I need to fix in the query to achieve expected results. 

[bowesmana]

At the very basic level you are producing a timechart with a span=1h and you want a time chart with a daily set of numbers, so that's wrong.

However, it's impossible to say what is going on here, you have so much going on in the search.

You have to go back to basics, which means start with the basic data and at EACH step of your SPL make sure the data is giving you what you expect.

So, take a very small sample set of data and run the SPL line by line, check the output after each line. When you are happy that the data is giving you the correct output from 1 line of SPL, add in the next line.

[bowesmana]

What is the x-axis you need.

You have 3 fields output in your search

| table StatisticalId Value Unit

and there is a lot of mvexpand logic going on... and that seems like you are going to multiple your data significantly as there's no correlation between each of the MV values you are expanding.

That aside, the basic command to create the chart would be something like

| chart max(Value) over Unit by StatisticalId

which would put Unit on the x-axis. Swap Unit and StatisticalId to make StatisticalId the x-axis

[uagraw01]

@bowesmana  In x-axis I am having time.

[dataisbeautiful]

HI @uagraw01 

Try this: Data display > Data value display: All.