Splunk Search

How to edit my search to create a time chart with a count and percentage for each day?

umsundar2015
Path Finder

Hi,

My scenario is to get a time chart with each day's values for a particular period of time (ex: 7 days) and their percentage of each day.

My search is like below:

base search |stats count by status | addinfo |eval _time=info_max_time | timechart span=1d values(count) as count by status |rename "pass" AS p|rename "fail" AS f|eval T=p+f|eval ppct=round((p/T)*100,3)|eval fpct=round((f/T)*100,3)|rename f AS "fail" | rename p AS "pass"| fields _time fail pass|eventstats max(*) as *

With this I am getting values as below:

_time        pass     fail
24/11/2016  32.956  67.044
25/11/2016  32.956  67.044
26/11/2016  32.956  67.044
27/11/2016  32.956  67.044
28/11/2016  32.956  67.044
29/11/2016  32.956  67.044
30/1/2016    32.956 67.044

It shows same values for all the days, but I need different values for pass and fail for each day.
Can anyone suggest me a way?

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

You're loosing original _time value in the base search as each search (including append subsearch) is doing stats without _time. Give this a try

index=server ins_status=Ins NOT m_virtual=true | bucket span=1d _time | stats count by _time name,group,m_envi,mode_id   | eval name=lower(name) | rex field=name "(?[a-z]\w+)" | fields _time name,group,m_envi,c_host,mode_id   |rename m_envi as env | eval Lrc="db"  | append [search index = gua1  | eval es_des=case(like(es_des,"%ORA%"),"ORA",LIKE(es_des,"%MS%"),"MS_end",LIKE(es_des,"%base%"),"base",1=1,es_des)  | bucket span=1d _time | stats count by _time Hst,es_des| lookup mip_host.csv IP_Address as Hst |lookup slookup c_ip as Hst | eval name=lower(coalesce(lower(coalesce(Name,c_hst)),Hst)) | rex field=name "(?[a-z]+\w+)" | eval Lrc="gv"] | stats count values(*) as * by _time c_hst  |eval Lrc=mvjoin(Lrc,"_") |mvexpand name | replace db_gv with supporting db with "Not supporting" in Lrc  | rename Lrc as supporting_stt  | search NOT supporting_stt="gv"  | search (group="*") AND (mode_id="*")|stats count by _time supporting_stt  | timechart span=1d values(count) as count by supporting_stt |rename "supporting" AS r|rename "Not supporting" AS nr|eval T=nr+r|eval nrpct=round((nr/T)*100,3)|eval rpct=round((r/T)*100,3)|rename nrpct AS "Not supporting" | rename rpct AS "supporting"| fields _time "Not supporting" supporting |eventstats max(*) as *

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

You're loosing original _time value in the base search as each search (including append subsearch) is doing stats without _time. Give this a try

index=server ins_status=Ins NOT m_virtual=true | bucket span=1d _time | stats count by _time name,group,m_envi,mode_id   | eval name=lower(name) | rex field=name "(?[a-z]\w+)" | fields _time name,group,m_envi,c_host,mode_id   |rename m_envi as env | eval Lrc="db"  | append [search index = gua1  | eval es_des=case(like(es_des,"%ORA%"),"ORA",LIKE(es_des,"%MS%"),"MS_end",LIKE(es_des,"%base%"),"base",1=1,es_des)  | bucket span=1d _time | stats count by _time Hst,es_des| lookup mip_host.csv IP_Address as Hst |lookup slookup c_ip as Hst | eval name=lower(coalesce(lower(coalesce(Name,c_hst)),Hst)) | rex field=name "(?[a-z]+\w+)" | eval Lrc="gv"] | stats count values(*) as * by _time c_hst  |eval Lrc=mvjoin(Lrc,"_") |mvexpand name | replace db_gv with supporting db with "Not supporting" in Lrc  | rename Lrc as supporting_stt  | search NOT supporting_stt="gv"  | search (group="*") AND (mode_id="*")|stats count by _time supporting_stt  | timechart span=1d values(count) as count by supporting_stt |rename "supporting" AS r|rename "Not supporting" AS nr|eval T=nr+r|eval nrpct=round((nr/T)*100,3)|eval rpct=round((r/T)*100,3)|rename nrpct AS "Not supporting" | rename rpct AS "supporting"| fields _time "Not supporting" supporting |eventstats max(*) as *
0 Karma

umsundar2015
Path Finder

Hi,

Thank you for the search provided .

But here the search runs for long time and getting only date for 7 days .The Supporting and Non supporting columns shows null values i mean no values for all the 7 days.

Please provide me a solution.I am facing this issue for long time now .

Regards,

0 Karma

umsundar2015
Path Finder

Hi,

This is my full query ,

index=server ins_status=Ins NOT m_virtual=true | stats count by name,group,m_envi,mode_id   | eval name=lower(name) | rex field=name "(?[a-z]\w+)" | fields name,group,m_envi,c_host,mode_id   |rename m_envi as env | eval Lrc="db"  | append [search index = gua1  | eval es_des=case(like(es_des,"%ORA%"),"ORA",LIKE(es_des,"%MS%"),"MS_end",LIKE(es_des,"%base%"),"base",1=1,es_des)  | stats count by Hst,es_des| lookup mip_host.csv IP_Address as Hst |lookup slookup c_ip as Hst | eval name=lower(coalesce(lower(coalesce(Name,c_hst)),Hst)) | rex field=name "(?[a-z]+\w+)" | eval Lrc="gv"] | stats count values(*) as * by c_hst  |eval Lrc=mvjoin(Lrc,"_") |mvexpand name | replace db_gv with supporting db with "Not supporting" in Lrc  | rename Lrc as supporting_stt  | search NOT supporting_stt="gv"  | search (group="*") AND (mode_id="*")|stats count by supporting_stt | addinfo |eval _time=info_max_time  | timechart span=1d values(count) as count by supporting_stt |rename "supporting" AS r|rename "Not supporting" AS nr|eval T=nr+r|eval nrpct=round((nr/T)*100,3)|eval rpct=round((r/T)*100,3)|rename nrpct AS "Not supporting" | rename rpct AS "supporting"| fields _time "Not supporting" supporting |eventstats max(*) as *

Can anyone pls help me out .

Regards,

0 Karma

sundareshr
Legend

Try this

base search earliest=-7d@d | timechart span=1d count by status | addtotals | eval ppct=round((pass/Total)*100, 3)| eval fpct=round((fail/Total)*100, 3)| table _time fail fpct pass ppct

umsundar2015
Path Finder

hi , thank you for your response .

I tried with this but getting "No results found". In my opinion the usage of eventstats and "eval _time=info_max_time" are causing this problem.

Is there any function or other option can be used in same "eval _time=info_max_time" and other function for eventstats at the last ???

0 Karma

umsundar2015
Path Finder
index=server ins_status=Ins NOT m_virtual=true | stats count by name,group,m_envi,mode_id   | eval name=lower(name) | rex field=name "(?[a-z]\w+)" | fields name,group,m_envi,c_host,mode_id   |rename m_envi as env | eval Lrc="db"  | append [search index = gua1  | eval es_des=case(like(es_des,"%ORA%"),"ORA",LIKE(es_des,"%MS%"),"MS_end",LIKE(es_des,"%base%"),"base",1=1,es_des)  | stats count by Hst,es_des| lookup mip_host.csv IP_Address as Hst |lookup slookup c_ip as Hst | eval name=lower(coalesce(lower(coalesce(Name,c_hst)),Hst)) | rex field=name "(?[a-z]+\w+)" | eval Lrc="gv"] | stats count values(*) as * by c_hst  |eval Lrc=mvjoin(Lrc,"_") |mvexpand name | replace db_gv with supporting db with "Not supporting" in Lrc  | rename Lrc as supporting_stt  | search NOT supporting_stt="gv"  | search (group="*") AND (mode_id="*")|stats count by supporting_stt | addinfo |eval _time=info_max_time  | timechart span=1d values(count) as count by supporting_stt |rename "supporting" AS r|rename "Not supporting" AS nr|eval T=nr+r|eval nrpct=round((nr/T)*100,3)|eval rpct=round((r/T)*100,3)|rename nrpct AS "Not supporting" | rename rpct AS "supporting"| fields _time "Not supporting" supporting |eventstats max(*) as *

This is my query ..

0 Karma

sundareshr
Legend

Unfortunately, there's too much going on with this query and I am having a hard time following. I edited the portions I think is relevant to your question. In short, I removed the append as I don't know if it is doing what you expect it to do. Start with this, see if it gets you going

index=server ins_status=Ins NOT m_virtual=true 
| eval name=lower(name) 
| rex field=name "(?<c_hst>[a-z]\w+)" 
| fields _time name group m_envi c_hst mode_id 
| rename m_envi as env 
| eval Lrc="db" 
....
....
....
| timechart span=1d values(count) as count by supporting_stt 
| rename "supporting" AS r 
| rename "Not supporting" AS nr 
| addtotals labelfield=T
| eval nrpct=round((nr/T)100,3) 
| eval rpct=round((r/T)100,3) 
| rename nrpct AS "Not supporting" 
| rename rpct AS "supporting" 

umsundar2015
Path Finder

Yes its doing a lot .
No , its giving me error like invalid argument 'T' .

Any other alternative sundareshr ..

0 Karma

sundareshr
Legend

My bad, change fieldname=T to labelfield=T in the addtotals command

0 Karma

umsundar2015
Path Finder

Hi,

Thank you .

But this too not satisfied my requirement .Can you help me with someother techniques.

Regards,

0 Karma

sundareshr
Legend

What do you get with query. Can you share sample output and what you like to see changed.

0 Karma

umsundar2015
Path Finder

This search gives me "No results found" .

I want to show the trending chart shows different values for different day in span of example 7 days.

But now it is showing same values for all day as i mentioned above in my first post.I dont know why it happens.

Please provide me answer for rectification.

Regards,

0 Karma

sundareshr
Legend

You should not be using eventstats & addinfo in my search. The only change you should make is to add index=indexname sourcetype=sourcetypename in place of base search. Everything else should be as-is other than correcting field names.

Can you share your entire query?

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