topic How to add counts for "missing" values in Splunk Search

How to add counts for "missing" values

Muthu_Vinith — Tue, 28 Nov 2023 10:20:14 GMT

"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

Re: Splunk

bowesmana — Tue, 28 Nov 2023 02:06:05 GMT

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.

Re: Splunk

Muthu_Vinith — Tue, 28 Nov 2023 04:02:07 GMT

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

Re: Splunk

bowesmana — Tue, 28 Nov 2023 07:45:51 GMT

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.

Re: Splunk

gcusello — Tue, 28 Nov 2023 08:39:31 GMT

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:

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

Ciao.

Giuseppe

Re: Splunk

Muthu_Vinith — Tue, 28 Nov 2023 09:01:42 GMT

Thanks @gcusello

Re: Splunk

Muthu_Vinith — Tue, 28 Nov 2023 09:02:13 GMT

Thanks @bowesmana

Re: Splunk

gcusello — Tue, 28 Nov 2023 09:06:50 GMT

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 😉

Re: Splunk

Muthu_Vinith — Tue, 28 Nov 2023 09:25:56 GMT

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

Re: Splunk

gcusello — Tue, 28 Nov 2023 09:34:51 GMT

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:

Ciao.

Giuseppe

Re: Splunk

Muthu_Vinith — Tue, 28 Nov 2023 09:44:14 GMT

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

Re: Splunk

Muthu_Vinith — Tue, 28 Nov 2023 09:47:23 GMT

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

Re: Splunk

gcusello — Wed, 29 Nov 2023 04:54:06 GMT

Hi @Muthu_Vinith ,

did you tested my approach?

Ciao.

Giuseppe

Re: Splunk

Muthu_Vinith — Wed, 29 Nov 2023 05:08:48 GMT

Yes @gcusello

Re: Splunk

gcusello — Wed, 29 Nov 2023 05:11:18 GMT

Hi @Muthu_Vinith ,

so what's its issue or different requirement?

Ciao.

Giuseppe

Re: Splunk

Muthu_Vinith — Wed, 29 Nov 2023 05:37:09 GMT

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

Re: Splunk

gcusello — Wed, 29 Nov 2023 05:51:37 GMT

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

Re: Splunk

Muthu_Vinith — Wed, 29 Nov 2023 12:28:04 GMT

Yeah I got it

Thanks @gcusello

Re: Splunk

Muthu_Vinith — Wed, 29 Nov 2023 12:49:10 GMT

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