Splunk Search

Put stats(values) and stats(count) in the same table (with tstats)

3DGjos
Communicator

Hello,

I need help with a dashboard Panel I need to make for a client. This guy wants a failed logins table, but merging it with a a count of the same data for each user. My data is coming from an accelerated datamodel so I have to use tstats.

Let me give you an example of what I need to do:

I need to merge this query:

| tstats summariesonly=true allow_old_summaries=true count from datamodel=Authentication.Authentication where   Authentication.action="failure"   by Authentication.user

with this one:

| tstats summariesonly=true allow_old_summaries=true values from datamodel=Authentication.Authentication where   Authentication.action=failure  by _time  Authentication.user Authentication.src Authentication.dest Authentication.app
|`truncate_name("Authentication")`
| eval Time = strftime(_time, "%d-%b-%Y %H:%M:%S")
| table Time  user app dest src
| rename user AS User src AS From dest AS "Destination" app AS "App"
| sort -Time

in the same table.

Thanks!

0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

| tstats summariesonly=true allow_old_summaries=true values
FROM datamodel=Authentication.Authentication 
WHERE nodename=Authentication.Failed_Authentication 
BY _time Authentication.user Authentication.src Authentication.dest Authentication.app 
| rename Authentication.* AS *
| rename _time AS Time
| fieldformat Time = strftime(Time, "%d-%b-%Y %H:%M:%S") 
| rename user AS User src AS From dest AS Destination app AS App
| table Time User App Destination From 
| sort 0 -Time
| eventstats count AS TotalForThisUser BY User

View solution in original post

3DGjos
Communicator

Hey, I managed to do it this way:

index=myindex action=failure

   | eval "Time"=strftime(_time, "%d/%m/%Y %H:%M:%S")
   | eval "accesstype" =`mymacro(1)`
     | table Time "accesstype" user action app src dest
  |  rename myrenames

     | eventstats count AS "Total by destination" BY src, user
     | sort - "Total by destination"

Thanks!

0 Karma

woodcock
Esteemed Legend

Like this:

| tstats summariesonly=true allow_old_summaries=true values
FROM datamodel=Authentication.Authentication 
WHERE nodename=Authentication.Failed_Authentication 
BY _time Authentication.user Authentication.src Authentication.dest Authentication.app 
| rename Authentication.* AS *
| rename _time AS Time
| fieldformat Time = strftime(Time, "%d-%b-%Y %H:%M:%S") 
| rename user AS User src AS From dest AS Destination app AS App
| table Time User App Destination From 
| sort 0 -Time
| eventstats count AS TotalForThisUser BY User

chrisyounger
SplunkTrust
SplunkTrust

Does this work:

| tstats summariesonly=true allow_old_summaries=true count from datamodel=Authentication.Authentication where Authentication.action="failure" by Authentication.user 
| append 
    [| tstats summariesonly=true allow_old_summaries=true values from datamodel=Authentication.Authentication where Authentication.action=failure by _time Authentication.user Authentication.src Authentication.dest Authentication.app]
| `truncate_name("Authentication")` 
| eval Time = strftime(_time, "%d-%b-%Y %H:%M:%S") 
| stats values(Time) as Time values(app) as "App"  values(dest) as "Destination" values(src) as "Source" sum(count) by user
| rename user AS User
| sort -Time

3DGjos
Communicator

Hello, sorry for the delay. I took your query and it worked, then my client changed the requirement and I had to go with something like woodcock did.

Thanks

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...