topic Re: Timechart not gives correct values after stats in Splunk Search

Timechart not gives correct values after stats

dtccsundar — Mon, 22 Nov 2021 08:05:21 GMT

My query ,

index=s_New sourcetype IN (Compare,Fire)
| stats values(*) as * values(sourcetype) as sourcetype by sysid _time
| fillnull value=""
| eval Status=if(Fire_Agent_Version = "" AND Compare_Agent_Version = "","Not Covered","Covered")
| search OS="*" Group="*" Name="***" Environment="*"
| timechart span=1d count by Status
| addtotals
| eval "Covered %"=round((Covered/Total)*100,2)
| eval "Not Covered %"=round(('Not Covered'/Total)*100,2)
| fields _time "Covered %" "Not Covered %"

The above search not providing expected count as i get i get for Status count as below ,

iindex=s_New sourcetype IN (Compare,Fire)
| stats values(*) as * values(sourcetype) as sourcetype by sysid
| fillnull value=""
| eval Status=if(Fire_Agent_Version = "" AND Compare_Agent_Version = "","Not Covered","Covered")
| search OS="*" Group="*" Name="***" Environment="*"

I think i am missing something in timechart search .How to get he exact count for timechart as in below search using stats alone.

Re: Timechart not gives correct values after stats

ITWhisperer — Mon, 22 Nov 2021 08:37:53 GMT

Your first search is counting sysids per days by status, the second is counting sysids by just status. In the first instance a sysid may appear on more than one day. This is why your counts are different.

Re: Timechart not gives correct values after stats

dtccsundar — Mon, 22 Nov 2021 09:35:57 GMT

Yes you are right ,but is it possible to make the timechart show a particular days data alone to match the second search in this case ?

Re: Timechart not gives correct values after stats

ITWhisperer — Mon, 22 Nov 2021 10:00:20 GMT

I am not sure what you are asking for - the timechart is already breaking it down by days and the second search has no time element in it - do you just want to add the time element into the second search?

Re: Timechart not gives correct values after stats

dtccsundar — Mon, 22 Nov 2021 10:36:37 GMT

No , what i am getting in 2nd search is the expected count .IS it possible to make the timechart display the same count,bcoz that is the requirement .I need to show the % for the same count as in Second count .

Is it possible to use chart or something else to bring the same count in timechart .

Re: Timechart not gives correct values after stats

ITWhisperer — Mon, 22 Nov 2021 10:37:52 GMT

| chart count by sysid Status

Re: Timechart not gives correct values after stats

dtccsundar — Mon, 22 Nov 2021 11:00:18 GMT

hi,

Nope , this didnt worked out .Please let me know if this can achieved other way ?

Re: Timechart not gives correct values after stats

ITWhisperer — Mon, 22 Nov 2021 11:16:35 GMT

Please can you give examples of what you are getting from the two searches and how you would like them to be different?

Re: Timechart not gives correct values after stats

dtccsundar — Mon, 22 Nov 2021 11:33:35 GMT

Search 1: ( which needs to be modified)

index=s_New sourcetype IN (Compare,Fire) | stats values(*) as * values(sourcetype) as sourcetype by sysid _time | fillnull value="" | eval Status=if(Fire_Agent_Version = "" AND Compare_Agent_Version = "","Not Covered","Covered") | search OS="*" Group="*" Name="***" Environment="*" | timechart span=1d count by Status | addtotals | eval "Covered %"=round((Covered/Total)*100,2) | eval "Not Covered %"=round(('Not Covered'/Total)*100,2) | fields _time "Covered %" "Not Covered %"

Output i receive :

_time	Covered	Not Covered
2021-11-21	7918	2614

The above search not providing expected count as i get i get for Status count as below ,

Search 2 : (Correct one with expected count )
index=s_New sourcetype IN (Compare,Fire) | stats values(*) as * values(sourcetype) as sourcetype by sysid | fillnull value="" | eval Status=if(Fire_Agent_Version = "" AND Compare_Agent_Version = "","Not Covered","Covered") | search OS="*" Group="*" Name="***" Environment="*" | stats count by Status | eventstats sum(*) as sum_* | foreach * [ eval "Status %"=round((count/sum_count)*100,2)] | rename count as Count | fields - sum_count | sort - Count

Output i receive :

Status	Count	Status %
Covered	5030	93.97
Not Covered	323	6.03

I want the count as in search 2 for timechart too (as in search 1 above ) for last 30 days or so .

I hope this helps you .Please let me know if you need more info .

Re: Timechart not gives correct values after stats

ITWhisperer — Mon, 22 Nov 2021 12:03:37 GMT

Your searches are a bit confused. Try executing them one line at a time to see what they are doing to see if you can work out what might be wrong.

Having said that, in search 1, your initial stats in based on _time (as well as sysid). Since you haven't done any bucketing at this point, you could get multiple stats for the same day, so you could try putting

| bin _time span=1d

before the initial stats to see if that gives you what you are looking for.

In the second search, the foreach applies to each field (column) for each row, and since it is evaluating the same thing every time, there is no need to enclose it in a foreach, just use

| eval "Status %"=round((count/sum_count)*100,2)

After all that, your two searches are still doing different things (unless you time period is already restricted to a single day). This is because there is no time element in search 2 (as I said earlier), so you won't get the same result from the two searches (for periods greater than a single day).

Re: Timechart not gives correct values after stats

dtccsundar — Mon, 22 Nov 2021 13:24:31 GMT

This made the search to work ,

| bin _time span=1d

Thank you !!