Splunk Search

Timechart with dedup of 2 fields

utk123
Path Finder

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.

Tags (2)
0 Karma
1 Solution

utk123
Path Finder

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

View solution in original post

0 Karma

utk123
Path Finder

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

0 Karma

jpolvino
Builder

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

0 Karma

utk123
Path Finder

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

0 Karma

to4kawa
Ultra Champion
| 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?

0 Karma

utk123
Path Finder

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

0 Karma

to4kawa
Ultra Champion

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?

0 Karma

utk123
Path Finder

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.

0 Karma

to4kawa
Ultra Champion

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

utk123
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Build Scalable Security While Moving to Cloud - Guide From Clayton Homes

 Clayton Homes faced the increased challenge of strengthening their security posture as they went through ...

Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...

Cloud Platform | Migrating your Splunk Cloud deployment to Python 3.7

Python 2.7, the last release of Python 2, reached End of Life back on January 1, 2020. As part of our larger ...