I have a csv file created by splunk search:
It contain ip count for last 7 days.
it looks like this:
_time 192.168.10.20 192.168.30.46
2015-05-08 145 45
2015-05-09 200 200
2015-05-10 300 34
Now i want to subtract each day count from sum of total count of that ip
for example:
for ip 192.168.30.46 i want out put for date 2015-05-08 as (45+200+34)-45
how can i achieve this with splunk query?
If you have access to raw events, you can try following query:
...select the raw events.. | table _time ipAddress | eval date = strftime(_time, "%Y-%m-%d") | stats count as countByDate by date,ipAddress | eventstats count as totalcount by ipAddress | eval customCount = totalcount - countByDate
Thanks!!
If you have access to raw events, you can try following query:
...select the raw events.. | table _time ipAddress | eval date = strftime(_time, "%Y-%m-%d") | stats count as countByDate by date,ipAddress | eventstats count as totalcount by ipAddress | eval customCount = totalcount - countByDate
Thanks!!
the query is working but the result is not what i want.
can you explain me above query?
totalcount here is not the sum of day wise counts of an ip address.
my requirement is:
if 192.168.10.3 count for 2nd of may is 340 and 3rd of feb is 45 and fourth of feb is 10
i want to show on 2nd of feb deviation is (340+45+10)-340 , so customCount should show result of this (340+45+10)-340.
Hi,
My bad. Change the eventstats block to following:
| eventstats sum(countByDate) as totalcount by ipAddress
This should fix the problem. Can you please verify and confirm?
Thanks!!
Thanks vganjare , this is working for me.
thnku for your time and help...:)
Do you have control over the csv creation? The number of IP addresses will change over the time and hence building a dynamic query will be a challenge. If you have access to raw events, then you can use eventStats sum(count) by ipAddress and then substract the current count.
Thanks!!
no i dont have control over ip address. i need dynamic query.
ip address number will keep on changing.
is there any way to create dynamic query for this?
Do you have access to raw events?
ya i have access to raw events