Splunk Search

Timechart not gives correct values after stats

dtccsundar
Path Finder

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.

 

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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.

0 Karma

dtccsundar
Path Finder

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 ?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

dtccsundar
Path Finder

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 .

 

 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| chart count by sysid Status

 

0 Karma

dtccsundar
Path Finder

hi,

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

dtccsundar
Path Finder

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 :

_timeCoveredNot Covered
2021-11-2179182614



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 :

StatusCount Status %
Covered503093.97
Not Covered3236.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 .

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Path Finder

This made the search to work ,

| bin _time span=1d

Thank you !!

 

0 Karma
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 ...