Splunk Search

How to create a linechart with Percentages via Timechart

giventofly08
Explorer

I'm looking to create a timechart that will show the percentage of success versus failure of 6 different fields over the past 6 months (broken up by each month, so I believe it's span=1mon). The goal is to have the percentages in a linechart of each respective Operating System over the past 6 months.

My current syntax will display the percentage of the current month of these 6 fields, but I cannot figure out the timechart syntax to have it show the previous 6 months.

search query
| dedup comp_id check_id
| eval state_Win10=if(name="Windows 10",if((state="passed"), "Passed", "Failed"), null())
| eval state_Win7=if(name="Windows 7",if((state="passed"), "Passed", "Failed"), null())
| eval state_Win2008=if(name="Windows 2008",if((state="passed"), "Passed", "Failed"), null())
| eval state_Win2012=if(name="Windows 2012",if((state="passed"), "Passed", "Failed"), null())
| eval state_RHEL6=if(name="RHEL 6",if((state="passed"), "Passed", "Failed"), null())
| eval state_RHEL7=if(name="RHEL 7",if((state="passed"), "Passed", "Failed"), null())
| stats count(eval(state_Win10="Passed")) AS Win10Passed, count(eval(state_Win10="Failed")) AS Win10Failed, count(eval(state_Win7="Passed")) AS Win7Passed, count(eval(state_Win7="Failed")) AS Win7Failed, count(eval(state_Win2008="Passed")) AS Win2008Passed, count(eval(state_Win2008="Failed")) AS Win2008Failed, count(eval(state_Win2012="Passed")) AS Win2012Passed, count(eval(state_Win2012="Failed")) AS Win2012Failed, count(eval(state_RHEL6="Passed")) AS RHEL6Passed, count(eval(state_RHEL6="Failed")) AS RHEL6Failed, count(eval(state_RHEL7="Passed")) AS RHEL7Passed, count(eval(state_RHEL7="Failed")) AS RHEL7Failed 
| eval Win10PC=round(100-((Win10Failed/(Win10Passed+Win10Failed))*100),1), Win7PC=round(100-((Win7Failed/(Win7Passed+Win7Failed))*100),1)
| eval Win2008PC=round(100-((Win2008Failed/(Win2008Passed+Win2008Failed))*100),1)
| eval Win2012PC=round(100-((Win2012Failed/(Win2012Passed+Win2012Failed))*100),1)
| eval RHEL6PC=round(100-((RHEL6Failed/(RHEL6Passed+RHEL6Failed))*100),1)
| eval RHEL7PC=round(100-((RHEL7Failed/(RHEL7Passed+RHEL7Failed))*100),1)
| fields Win10PC Win7PC Win2008PC Win2012PC RHEL6PC RHEL7PC

Thank you for the assistance or advice to help me solve this.

0 Karma
1 Solution

Anantha123
Communicator

instead of stats use timechart and in last add _time in fields.

search query
| dedup comp_id check_id
| eval state_Win10=if(name="Windows 10",if((state="passed"), "Passed", "Failed"), null())
| eval state_Win7=if(name="Windows 7",if((state="passed"), "Passed", "Failed"), null())
| eval state_Win2008=if(name="Windows 2008",if((state="passed"), "Passed", "Failed"), null())
| eval state_Win2012=if(name="Windows 2012",if((state="passed"), "Passed", "Failed"), null())
| eval state_RHEL6=if(name="RHEL 6",if((state="passed"), "Passed", "Failed"), null())
| eval state_RHEL7=if(name="RHEL 7",if((state="passed"), "Passed", "Failed"), null())
| timechart count(eval(state_Win10="Passed")) AS Win10Passed, count(eval(state_Win10="Failed")) AS Win10Failed, count(eval(state_Win7="Passed")) AS Win7Passed, count(eval(state_Win7="Failed")) AS Win7Failed, count(eval(state_Win2008="Passed")) AS Win2008Passed, count(eval(state_Win2008="Failed")) AS Win2008Failed, count(eval(state_Win2012="Passed")) AS Win2012Passed, count(eval(state_Win2012="Failed")) AS Win2012Failed, count(eval(state_RHEL6="Passed")) AS RHEL6Passed, count(eval(state_RHEL6="Failed")) AS RHEL6Failed, count(eval(state_RHEL7="Passed")) AS RHEL7Passed, count(eval(state_RHEL7="Failed")) AS RHEL7Failed
| eval Win10PC=round(100-((Win10Failed/(Win10Passed+Win10Failed))*100),1), Win7PC=round(100-((Win7Failed/(Win7Passed+Win7Failed))*100),1)
| eval Win2008PC=round(100-((Win2008Failed/(Win2008Passed+Win2008Failed))*100),1)
| eval Win2012PC=round(100-((Win2012Failed/(Win2012Passed+Win2012Failed))*100),1)
| eval RHEL6PC=round(100-((RHEL6Failed/(RHEL6Passed+RHEL6Failed))*100),1)
| eval RHEL7PC=round(100-((RHEL7Failed/(RHEL7Passed+RHEL7Failed))*100),1)
| fields _time Win10PC Win7PC Win2008PC Win2012PC RHEL6PC RHEL7PC

View solution in original post

0 Karma

Anantha123
Communicator

instead of stats use timechart and in last add _time in fields.

search query
| dedup comp_id check_id
| eval state_Win10=if(name="Windows 10",if((state="passed"), "Passed", "Failed"), null())
| eval state_Win7=if(name="Windows 7",if((state="passed"), "Passed", "Failed"), null())
| eval state_Win2008=if(name="Windows 2008",if((state="passed"), "Passed", "Failed"), null())
| eval state_Win2012=if(name="Windows 2012",if((state="passed"), "Passed", "Failed"), null())
| eval state_RHEL6=if(name="RHEL 6",if((state="passed"), "Passed", "Failed"), null())
| eval state_RHEL7=if(name="RHEL 7",if((state="passed"), "Passed", "Failed"), null())
| timechart count(eval(state_Win10="Passed")) AS Win10Passed, count(eval(state_Win10="Failed")) AS Win10Failed, count(eval(state_Win7="Passed")) AS Win7Passed, count(eval(state_Win7="Failed")) AS Win7Failed, count(eval(state_Win2008="Passed")) AS Win2008Passed, count(eval(state_Win2008="Failed")) AS Win2008Failed, count(eval(state_Win2012="Passed")) AS Win2012Passed, count(eval(state_Win2012="Failed")) AS Win2012Failed, count(eval(state_RHEL6="Passed")) AS RHEL6Passed, count(eval(state_RHEL6="Failed")) AS RHEL6Failed, count(eval(state_RHEL7="Passed")) AS RHEL7Passed, count(eval(state_RHEL7="Failed")) AS RHEL7Failed
| eval Win10PC=round(100-((Win10Failed/(Win10Passed+Win10Failed))*100),1), Win7PC=round(100-((Win7Failed/(Win7Passed+Win7Failed))*100),1)
| eval Win2008PC=round(100-((Win2008Failed/(Win2008Passed+Win2008Failed))*100),1)
| eval Win2012PC=round(100-((Win2012Failed/(Win2012Passed+Win2012Failed))*100),1)
| eval RHEL6PC=round(100-((RHEL6Failed/(RHEL6Passed+RHEL6Failed))*100),1)
| eval RHEL7PC=round(100-((RHEL7Failed/(RHEL7Passed+RHEL7Failed))*100),1)
| fields _time Win10PC Win7PC Win2008PC Win2012PC RHEL6PC RHEL7PC

0 Karma

giventofly08
Explorer

This worked perfectly. Thanks for preventing me from pulling more hair out!

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...