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!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...