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

[umsundar2015]

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?

[somesoni2 · Accepted solution]

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 *

[umsundar2015]

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,

[umsundar2015]

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,

[sundareshr]

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]

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

[umsundar2015]

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

[sundareshr]

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]

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

Any other alternative sundareshr ..

[sundareshr]

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

[umsundar2015]

Hi,

Thank you .

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

Regards,

[sundareshr]

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

[umsundar2015]

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,

[sundareshr]

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?