Splunk Search

How do I create a stacked column chart from event tracking ?

sprayer122
Engager

Hi everybody,

I have some event data that looks like the tutorial data which you can find here : https://docs.splunk.com/Documentation/Splunk/latest/SearchTutorial/GetthetutorialdataintoSplunk

My data represents logs of some software that tracks the progress of customer incident cases.

In this data, you have multiple fields like time (format dd/mm/YYYY hh:mm:ss), current state (like 'open'/'in progress'/'closed'), id of the case.

Basically, what I want to do is to make a timechart of the number of cases that have been closed per month and to display the information of the maturity of the cases in the system like "cases that are in the system since more than 30 days" / "cases that are in the system since 15 and 30 days" etc.

As I'm pretty new to Splunk, I can't figure out how to write the right query, so could you help me please ?

At the moment I've got something like that :

index=myindex sourcetype=* current_state="closed" | timechart count by id_case

But that's pretty far from what I want to do...

Thanks in advance,

Regards,

0 Karma

mstjohn_splunk
Splunk Employee
Splunk Employee

hi @sprayer122,

Did you have a chance to check out niketnilay's answer? If it worked, please resolve this post by approving it! If your problem is still not solved, keep us updated so that someone else can help ya.

Thanks for posting!

0 Karma

niketn
Legend

@sprayer122 the query you have posted above kind of does count of closed tickets over time.

index=myindex sourcetype=* current_state="closed" 
| timechart span=1d count as closed

If this is working fine for closed tickets over time, then for finding open tickets by duration you can try the following:

 index=myindex sourcetype=* state IN ("open","in progress","closed")
| stats values(current_state) as states earliest(_time) as _time by id_case
| search states IN ("open","in progress") AND NOT(states="closed")
| eval duration=now()-_time
| eval open_duration=case(duration>=0 AND duration<86400,"1. <24hr", duration>=86400 AND duration<604800,"2. >24hr <7days", duration>=604800 AND duration<1296000,"3. >7days <15days", duration>=1296000 AND duration<2592000,"4. >15days <30days",true(),"5. >30days")
| stats count by open_duration
| append 
    [| makeresults
    5. >30days
    | eval dummyRows="open_duration=\"1. <24hr\",count=0;open_duration=\"2. >24hr <7days\",count=0;open_duration=\"3. >7days <15days\",count=0;open_duration=\"4. >15days <30days\",count=0;open_duration=\"5. >30days\",count=0" 
    | makemv dummyRows delim=";" 
    | mvexpand dummyRows 
    | rename dummyRows as _raw 
    | KV 
    | table open_duration count] 
| dedup open_duration
| sort open_duration

PS: append has been used to insert dummy rows for each open duration field value to show 0 count in case a range is not present in actual data.

For finding tickets that have been opened but not closed over time, you can try the following (PS: earliest(_time) should give ticket's open time as per business logic)

 index=myindex sourcetype=* state IN ("open","in progress","closed")
| stats values(current_state) as states earliest(_time) as _time by id_case
| search states IN ("open","in progress") AND NOT(states="closed")
| timechart count as Open

Please try out and confirm!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...