Timechart not gives correct values after stats

[dtccsundar]

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="*"

| 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

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

[ITWhisperer]

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.

[dtccsundar]

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 ?

[ITWhisperer]

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?

[dtccsundar]

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 .

[ITWhisperer]

| chart count by sysid Status

[dtccsundar]

hi,

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

[ITWhisperer]

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

[dtccsundar]

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 .

[ITWhisperer · Accepted solution]

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).

[dtccsundar]

This made the search to work ,

| bin _time span=1d

Thank you !!