Splunk Search

How to edit my search to calculate percentage for each row?

sonila
Path Finder
earliest=-72h@h latest=@h index=dga | transaction  EventType maxevents=2 |stats count as total | appendcols [search earliest=-72h@h latest=@h index=dga | transaction  EventType maxevents=2| where Result="False" OR Result="false" |timechart span=1h  count  | eval time=_time-now()%259200 | timechart span=24h sum(count) as count |  tail 3 | tail 2 | eval _time=_time+now()%259200] | eval percentage=count*100/total | table count, p

Why can't it calculate the whole percentage? It calculates only for the first row

0 Karma
1 Solution

sonila
Path Finder

nope doesnt work this

earliest=-72h@h latest=@h index=dga | transaction  EventType maxevents=2| where Result="False" OR Result="false" |timechart span=1h  count | eval time=_time-now()%259200 | timechart span=24h sum(count) as count |  tail 3 | tail 2 | eval _time=_time+now()%259200 | appendcols [ search earliest=-72h@h latest=@h index=dga | transaction  EventType maxevents=2 | timechart span=1h  count as total |eval time=_time-now()%259200 | timechart span=24h sum(total) as total |  tail 3 | tail 2 | eval _time=_time+now()%259200  ] | eval p=count*100/total | eval p = if(isnull(p), 0, p) |fields + p

this gave me the solution wanted

View solution in original post

0 Karma

woodcock
Esteemed Legend

Your transaction commands look strange to me and I suspect that are gross overkill for what you are trying to do. Please show a few sample events and the desire end result. I am sure that we can create something in a much more efficient way than the path that you are on.

0 Karma

sonila
Path Finder

I need to count two events as one. thats why i used transaction command

0 Karma

sonila
Path Finder

nope doesnt work this

earliest=-72h@h latest=@h index=dga | transaction  EventType maxevents=2| where Result="False" OR Result="false" |timechart span=1h  count | eval time=_time-now()%259200 | timechart span=24h sum(count) as count |  tail 3 | tail 2 | eval _time=_time+now()%259200 | appendcols [ search earliest=-72h@h latest=@h index=dga | transaction  EventType maxevents=2 | timechart span=1h  count as total |eval time=_time-now()%259200 | timechart span=24h sum(total) as total |  tail 3 | tail 2 | eval _time=_time+now()%259200  ] | eval p=count*100/total | eval p = if(isnull(p), 0, p) |fields + p

this gave me the solution wanted

View solution in original post

0 Karma

aaraneta_splunk
Splunk Employee
Splunk Employee

@sonila - I just want to confirm: The solution you found above is the answer to your question? If yes, let me know and I can convert your comment as the answer to "Accept". If no and you want to leave your question open for other suggestions, no action needs to be taken.

0 Karma

sonila
Path Finder

Yes this is the solution

0 Karma

somesoni2
Revered Legend

Could you describe what's the problem you see with the results?

0 Karma

sonila
Path Finder

Your solution didnt calculate percentage of each row.
Instead my version gave me the results for each row.
earliest=-72h@h latest=@h index=dga | transaction EventType maxevents=2| where Result="False" OR Result="false" |timechart span=1h count | eval time=_time-now()%259200 | timechart span=24h sum(count) as count | tail 3 | tail 2 | eval _time=_time+now()%259200 --> this gave me the number of events of errors counted by time
and
appendcols [ search earliest=-72h@h latest=@h index=dga | transaction EventType maxevents=2 | timechart span=1h count as total |eval time=_time-now()%259200 | timechart span=24h sum(total) as total | tail 3 | tail 2 | eval _time=_time+now()%259200 ] | eval p=count*100/total ---> this gave me the total and percentage of each row

0 Karma

somesoni2
Revered Legend

The first query of your's is giving only single row (output of stats) hence the field total is only populated in row1 and thus percentage is only available in row1. I believe something like this would work efficiently and give you expected result.

earliest=-72h@h latest=@h index=dga | transaction EventType maxevents=2 | eventstats count as total
| where Result="False" OR Result="false" |timechart span=1h count max(total) as total | eval time=_time-now()%259200 | timechart span=24h sum(count) as count max(total) as total | tail 2 | eval _time=_time+now()%259200 | eval percentage=count*100/total | table count, p
0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!