How to add counts for "missing" values

[Muthu_Vinith]

"Hey Splunk experts! I'm a Splunk newbie and working with data where running `stats count by status` gives me 'progress' and 'Not Started'. I'd like to include 'Wip progress' and 'Completed' in the results. When running `stats count by status`. Desired output is:

- Not Started
- Progress
- Wip Progress
- Completed 

Any tips or examples on how to modify my query to achieve this would be fantastic! Thanks 

[gcusello]

Hi @Muthu_Vinith ,

if you have only four statuses you can run something like this to be sure to have all the statuses even if there aren't values for someone of them:

<Your_search>
| stats count BY status
| append [ | makeresults | eval status="Not Started", count=0 | fields status count ]
| append [ | makeresults | eval status="Progress", count=0 | fields status count ]
| append [ | makeresults | eval status="Wip Progress", count=0 | fields status count ]
| append [ | makeresults | eval status="Completed", count=0 | fields status count ] 
| stats sum(count) AS total BY status

If the statuses can be more, you can also use a lookup to list all of them.

Ciao.

Giuseppe

[Muthu_Vinith]

• I'm working on visualizing completion versus target date in Splunk, and I'm facing a challenge because there's no completion level specified in my data. I have the target date and actual date,in actual date there is no dates mentioned and I want to create a chart that shows the progress towards the target date.

• I've tried the following search query:

 eval 'Target Date' = strptime('Target Date', "%m/%d/%y")
eval _time = 'Target Date'

timechart span=1mon dc(sno) as Target

Could please guide me on how to modify this query or suggest an alternative approach to visualize completion versus target date when completion data is absent?

@gcusello @bowesmana 

[gcusello]

Hi @Muthu_Vinith ,

I suppose that the status=completed is an event with a timestamp, so you could take the earliest and latest timestamps in your events:

<your_search>
| stats 
   earliest(_time) As earliest 
   latest(_time) AS latest 
   count 
   BY status
| append [ 
   | makeresults | eval status="Not Started", count=0 | fields status count ]
| append [ 
   | makeresults | eval status="Progress", count=0 | fields status count ]
| append [ 
   | makeresults | eval status="Wip Progress", count=0 | fields status count ]
| append [ 
   | makeresults | eval status="Completed", count=0 | fields status count ] 
| stats 
   values(earliest) AS earliest 
   values(latest) AS latest 
   sum(count) AS total 
   BY status
| eval 
   status=if(total=0,"NA",status),
   earliest=strftime(earliest,"%m/%d%y"),
   latest=strftime(latest,"%m/%d%y") 

Ciao.

Giuseppe

[Muthu_Vinith]

My target is 100. If anything is completed completed line graph should populate  @gcusello 

[Muthu_Vinith]

I actually tried this query also

| eval 'Target Date' = strptime('Target Date', "%m/%d/%y")

| eval _time = 'Target Date' | timechart span=1mon dc(sno) as Target

leval "Actual Date"=strptime('Actual Date', "%m/%d/%y")

leval _time='Actual Date'

| timechart span=1mon dc(sno) as Completed

Istreamstats sum(Completed) as Completed]

Istats values(*)as'* by _time

@gcusello 

[gcusello]

Hi @Muthu_Vinith ,

did you tested my approach?

Ciao.

Giuseppe

[Muthu_Vinith]

Yes @gcusello 

[gcusello]

Hi @Muthu_Vinith ,

so what's its issue or different requirement?

Ciao.

Giuseppe

[Muthu_Vinith]

My requirement is I need to show chart completed vs target my target value is 100 based on this I need to show what is the query for that @gcusello 

[gcusello]

hi @Muthu_Vinith ,

using my search, you have the count for each status, so you can sum thre three values using eval and calculate the percentage, this is a simplified version:

<your_search>
| stats 
   count(eval(status="Completed")) AS Completed_count
   count(eval(status!="Completed")) AS Not_Completed_count 
   BY status
| eval perc=(Completed_count/Not_Completed_count/*100

without eventual missing statuses.

Ciao.

Giuseppe

[Muthu_Vinith]

Yeah I got it 

Thanks @gcusello 

[Muthu_Vinith]

I have a single values panel with a distinct count, and I've specified a function for drill down. When clicking on a value like '25,' the table is displaying all values instead of the exact ones. Any guidance on refining the drill down for precise results @gcusello 

[Muthu_Vinith]

Thanks @gcusello 

[gcusello]

Hi @Muthu_Vinith ,

good for you, see next time!

let us know if we can help you more, or, please, accept one answer for the other people of Community.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[bowesmana]

If you don't see those other statuses in the results, then they don't exist as values of status in the data, because

search...
| stats count by status

will give you all values for status that are present in the data.

[Muthu_Vinith]

Okay, but how to add those extra fields that is my task. Is it possible to use eval command? If yes can you suggest me a query 

[bowesmana]

Can you share an example of the data where you have each type of status - if status values are being extracted for some events, but not others, it would indicate your data is not in a standard format.

Where is your data coming from and perhaps you can share an anonymised version of it.

[Muthu_Vinith]

Thanks @bowesmana