Splunk Search

Distinct count of machine names for the last 7 days

davidcraven02
Communicator

I want to count distinct machine names only once for each day for the last 7 days. The machine name is signified in the logs as 'Name0'

   index=<index> source=<source> 
       | dedup Name0 |eval machine=lower(Name0)  
    | search 
        [ search `ProductionWorkstations` 
        | table machine]  
    | bucket _time span=day | stats count by _time

I have uploaded two screenshots which use 'uniq Name0' and 'dedup Name0' in the search but the uniq search doesn't show distinct machines as the typical count usingdedup values within a 24 hour period is around the '4100' mark so the dedup search below is only counting distinct machines across 7 days.

alt text

alt text

0 Karma
1 Solution

cmerriman
Super Champion

try something like this:

index=<index> source=<source> 
     |eval machine=lower(Name0)  
     | search 
         [ search `ProductionWorkstations` 
         | table machine]  
      | timechart span=1d dc(machine) as distinct_machines

also, i'm not sure what your macro ProductionWorkstations is, exactly, but it looks like it at least retruns some machine names and you're filtering based on that. you might be able to do something like the below to filter in the base search.
EDITED

index=<index> source=<source> [`ProductionWorkstations` |table machine|rename machine as Name0|format]
 |eval machine=lower(Name0)
 | timechart span=1d dc(machine) as distinct_machines

View solution in original post

cmerriman
Super Champion

try something like this:

index=<index> source=<source> 
     |eval machine=lower(Name0)  
     | search 
         [ search `ProductionWorkstations` 
         | table machine]  
      | timechart span=1d dc(machine) as distinct_machines

also, i'm not sure what your macro ProductionWorkstations is, exactly, but it looks like it at least retruns some machine names and you're filtering based on that. you might be able to do something like the below to filter in the base search.
EDITED

index=<index> source=<source> [`ProductionWorkstations` |table machine|rename machine as Name0|format]
 |eval machine=lower(Name0)
 | timechart span=1d dc(machine) as distinct_machines

davidcraven02
Communicator

Thank you for taking the time to respond but unfortunately this did not work.

To make things simpler for me how would you approach getting a count for unique usernames for the last 7 days? This is what I require with the only difference being its machine names not users.

0 Karma

cmerriman
Super Champion

i would do exactly as i posted above:

index=_internal |timechart span=1d dc(user)

gives me a distinct count of users each day.

index=_internal |eventstats dc(user) as total_distinct_users|timechart span=1d dc(user) max(total_distinct_users)

gives me a distinct count of users each day plus the total distinct users across the timeframe.

davidcraven02
Communicator

Yes it worked. I realized that the date period was set to 1 day instead of 7. My mistake.

I eventually want to be able to have a pass or fail checking that the data is correct.

I wanted to introduce a check to use the last 7 days to check that the count is around the same 4100 mark but I don't want to hardcore the number 4100 like below:

  | eval Status=if(count > 4000, "Pass", "Fail")

I also thought about using the event size to get an average

|  eval eventSize=len(_raw) 
| stats avg(eventSize)

Any suggestions on the best approach?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Have you tried ... | stats dc(machine) by _time?

---
If this reply helps you, Karma would be appreciated.

davidcraven02
Communicator

I tried this but it did not work. Thank you 🙂

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...