topic Re: Timechart with dedup of 2 fields in Splunk Search

Timechart with dedup of 2 fields

utk123 — Thu, 16 Apr 2020 09:12:23 GMT

For my logs with IP and Vulnerability ID (VID), I have few duplicate values. Which I can easily remove with "dedup IP, VID". As this will only show single value in logs for an IP+VID combination.

But with timechart over 1 month it doesn't work, as if I dedup before timechart, it removes duplicate values and doesn't show exact results for every week.

I need dedup to run for every week separately under timechart to give correct results.

Currently running:

My main search.... | dedup IP, VID | timechart span=w@1w count

Results what I get with incorrect count:
_time ** ** count
2020-03-17 2224
2020-03-17 218
2020-03-17 689
2020-03-17 1432
2020-03-17 666

But actually if "dedup IP, VID" works separately for each week, then each week's result should be around 2000.

Thanks in advance.

Re: Timechart with dedup of 2 fields

to4kawa — Thu, 16 Apr 2020 09:23:07 GMT

| makeresults count=20
| fillnull IP
| eval IP = 1
| accum IP as VID
| dedup IP VID

this is sample. try this and |dedup IP | dedup VID

your main search | bin _time span=w@1w  | stats dc(IP) as IP dc(VID) as VID by _time | addtotals

How about this?

Re: Timechart with dedup of 2 fields

utk123 — Thu, 16 Apr 2020 10:15:57 GMT

First answer with makeresults doesn't give any results.

Second answer's logic I understand, but it doesn't seem to remove the duplicate values for IP and VID.
Maybe if you can help to modify second answer to remove duplicate values, it would work. I have tried few changes to your second answer, but no luck.

Re: Timechart with dedup of 2 fields

to4kawa — Thu, 16 Apr 2020 10:22:42 GMT

First answer with makeresults doesn't give any results.
my result:

IP  VID _time
1   1   2020/04/16 19:18:03
1   2   2020/04/16 19:18:03
1   3   2020/04/16 19:18:03
1   4   2020/04/16 19:18:03
1   5   2020/04/16 19:18:03
....

Re: Timechart with dedup of 2 fields

jpolvino — Thu, 16 Apr 2020 11:02:50 GMT

Are you saying that you want to look at each week in isolation, dedup the IP and VIP in that week's group of events? If so, then try adding a "weekbreaker" field and deduping that before your timechart:
| eval weekbreaker=relative_time(_time, "@w") | dedup IP,VIP,weekbreaker

Re: Timechart with dedup of 2 fields

utk123 — Thu, 16 Apr 2020 11:23:27 GMT

It works if I just change the sequence of _time in bin command.

main search...
| bin span=w@1w _time
| stats count by IP VID _time
| stats count by _time

Re: Timechart with dedup of 2 fields

utk123 — Thu, 16 Apr 2020 11:27:23 GMT

tried it, but it just shows Events, and no Statistics or Visualization to check results.

Re: Timechart with dedup of 2 fields

utk123 — Thu, 16 Apr 2020 11:29:45 GMT

Below answer works. Thanks to @to4kawa for helping.

main search...
| bin span=w@1w _time
| stats count by IP VID _time
| stats count by _time

Re: Timechart with dedup of 2 fields

utk123 — Thu, 16 Apr 2020 11:31:46 GMT

Not this answer @to4kawa, but the answer you gave earlier worked. it seems you changed it to addtotals.

Re: Timechart with dedup of 2 fields

to4kawa — Thu, 16 Apr 2020 11:44:41 GMT

first but it doesn't seem to remove the duplicate values for IP and VID.
so, I amended.
second the answer you gave earlier worked.
Isn't it terrible?