data from csv file and working on that with splunk query

[ektasiwani]

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?

[vganjare · Accepted solution]

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

[ektasiwani]

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.

[vganjare]

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

[ektasiwani]

Thanks vganjare , this is working for me.
thnku for your time and help...:)

[vganjare]

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

[ektasiwani]

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?

[vganjare]

Do you have access to raw events?

[ektasiwani]

ya i have access to raw events