topic Re: Can you help me answer a question with the chart command? in Splunk Search

Can you help me answer a question with the chart command?

krusovice — Fri, 21 Dec 2018 03:18:16 GMT

Hi there,

I have this query formed and I can't the get expected result, but it's very close to what I want. The result of chart followed by transpose command is looking good, but under "column", I'm getting the value as NULL instead of "sourcetype". May I know how to fix it?

index=* sourcetype=*
| eval date_hour=if(len(date_hour)==1,"0".date_hour.":00",date_hour.":00")
| rex  "Total1\:\s(?<total1>[\d+]+)" 
| rex  "Total2\:\s(?<total2>[\d+]+)" 
| stats count(total1) count(total2) by date_hour
| eval granttotal = total1+total2
| fields - _time
| chart values(granttotal) over date_hour by sourcetype
| transpose header_field=date_hour

column       02:00         03:00         04:00
NULL           96.64         80.00         89.70

Re: Can you help me answer a question with the chart command?

inventsekar — Fri, 21 Dec 2018 04:16:56 GMT

fillnull works fine with chart command.. not sure about transpose. anyhow, lets try:

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Fillnull

Re: Can you help me answer a question with the chart command?

krusovice — Fri, 21 Dec 2018 05:43:43 GMT

Thanks for reply, I've moved the fillnull before and after transpose, it is not working.

Re: Can you help me answer a question with the chart command?

inventsekar — Fri, 21 Dec 2018 06:44:56 GMT

for this search, what output you get(without the transpose):
index=* sourcetype=* | eval date_hour=if(len(date_hour)==1,"0".date_hour.":00",date_hour.":00") | rex "Total1\:\s(?[\d+]+)" | rex "Total2\:\s(?[\d+]+)" | stats count(total1) count(total2) by date_hour | eval granttotal = total1+total2 | fields - _time | chart values(granttotal) over date_hour by sourcetype

Re: Can you help me answer a question with the chart command?

krusovice — Fri, 21 Dec 2018 07:02:18 GMT

I'm having result as below:
date_hour NULL
06:00 78.00
07:00 73.68

Re: Can you help me answer a question with the chart command?

kamlesh_vaghela — Fri, 21 Dec 2018 12:02:22 GMT

@krusovice

Can you please try this?

index=* sourcetype=* 
| eval date_hour=if(len(date_hour)==1,"0".date_hour.":00",date_hour.":00") 
| rex "Total1\:\s(?<total1>[\d+]+)" 
| rex "Total2\:\s(?<total2>[\d+]+)" 
| stats count(total1) as total1, count(total2) as total2 by date_hour,sourcetype 
| eval granttotal = total1+total2 
| fields - _time 
| chart values(granttotal) over date_hour by sourcetype | transpose header_field=date_hour

I did some minor correction in your search and added sourcetype in stats. I think following search not getting sourcetype.

Please try and let me know.

Thanks

Re: Can you help me answer a question with the chart command?

woodcock — Fri, 21 Dec 2018 17:56:51 GMT

You have many mistakes. See this run-anywhere fixed example:

index=_* 
| eval date_hour=strftime(_time, "%H") 
| eval date_hour=if(len(date_hour)==1,"0".date_hour.":00",date_hour.":00") 
| eval total1=random(), total2=random()
| stats sum(total1) AS total1 sum(total2) AS total2 BY date_hour sourcetype
| eval grand_total = total1 + total2 
| chart values(grand_total) OVER date_hour BY sourcetype 
| transpose header_field=date_hour

Which means your fixed search should be:

index=* sourcetype=*
| eval date_hour=strftime(_time, "%H") 
| eval date_hour=if(len(date_hour)==1,"0".date_hour.":00",date_hour.":00")
| rex  "Total1\:\s(?<total1>[\d+]+)" 
| rex  "Total2\:\s(?<total2>[\d+]+)" 
| stats sum(total1) AS total1 sum(total2) AS total2 BY date_hour sourcetype
| eval grand_total = total1 + total2 
| chart values(grand_total) OVER date_hour BY sourcetype
| transpose header_field=date_hour