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!

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...

Admin Console: A Single, Unified Interface for All Your Cloud Admin Needs

WATCH NOWJoin us to learn how the admin console can save you time and give you more control over the Splunk® ...