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!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...