Splunk Search
Highlighted

How to create a linechart with Percentages via Timechart

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
Highlighted

Re: How to create a linechart with Percentages via Timechart

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
Highlighted

Re: How to create a linechart with Percentages via Timechart

Explorer

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

0 Karma