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!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...