Splunk Search

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

krusovice
Path Finder

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
Tags (2)
0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

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
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

0 Karma

inventsekar
Ultra Champion

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

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
| transpose header_field=date_hour
| fillnull value=sourcetype

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

0 Karma

krusovice
Path Finder

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

0 Karma

inventsekar
Ultra Champion

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

0 Karma

krusovice
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...