Reporting

[Search] Change Pivot search to datamodel search

test_qweqwe
Builder

Hi!
I never used Pivot command and now I need to change this:

| pivot Authentication Authentication count(Authentication) AS "Count of Authentication" SPLITROW src AS src SPLITCOL action LIMIT src BY TOP 15 count(is_Failed_Authentication) FILTER action isNot unknown FILTER action isNot search FILTER src isNot "10.10.20.58" FILTER src isNot "10.10.20.140" FILTER src isNot "10.10.20.200" FILTER src isNot unknown FILTER src isNot *az* FILTER src isNot struts FILTER src isNot *DESKTOP* SORT 1000 src ROWSUMMARY 0 COLSUMMARY 0 NUMCOLS 100 SHOWOTHER 0 |rename src AS Host | sort -failure

to something like:

| tstats `summariesonly` values(Authentication.app) as app,count from datamodel=Authentication.Authentication by Authentication.action,Authentication.src | `drop_dm_object_name("Authentication")` | eval success=if(action="success",count,0) | eval failure=if(action="failure",count,0) | stats values(app) as app,sum(failure) as failure,sum(success) as success by src | where success > 0 | xswhere failure from failures_by_src_count_1d in authentication is above medium | `settags("access")`

^ this search shows success/failed authentication only by remote (and i need in general all success/failed by all users)
And by low skill with working tstats and Pivot syntax I'm stack :<

And one more question, it's possible to run my Pivot search with acceleration?

0 Karma
1 Solution

test_qweqwe
Builder

This.

| tstats `summariesonly` count from datamodel=Authentication.Authentication where Authentication.src!=unknown by Authentication.action,Authentication.src 
| `drop_dm_object_name("Authentication")` 
| eval success=if(action="success",count,0) 
| eval failure=if(action="failure",count,0) 
| stats sum(failure) as failure,sum(success) as success by src 
| where success > 0
| xswhere failure from failures_by_src_count_1d in authentication is above medium 
| fields + src failure success 
| rename src as Host failure as Failure success as Success
| sort -Failure

View solution in original post

0 Karma

test_qweqwe
Builder

This.

| tstats `summariesonly` count from datamodel=Authentication.Authentication where Authentication.src!=unknown by Authentication.action,Authentication.src 
| `drop_dm_object_name("Authentication")` 
| eval success=if(action="success",count,0) 
| eval failure=if(action="failure",count,0) 
| stats sum(failure) as failure,sum(success) as success by src 
| where success > 0
| xswhere failure from failures_by_src_count_1d in authentication is above medium 
| fields + src failure success 
| rename src as Host failure as Failure success as Success
| sort -Failure
0 Karma

mayurr98
Super Champion

hey @test_qweqwe
Try this,

| tstats count from datamodel=Authentication where (Authentication.action!="unknown" OR Authentication.action!="search") AND (Authentication.src!="10.10.20.58" OR Authentication.src!="10.10.20.200" OR Authentication.src!="10.10.20.140" OR Authentication.src!="unknown" OR Authentication.src!=*az* OR Authentication.src!="struts" OR Authentication.src!=*DESKTOP*) by Authentication.src Authentication.action | rename Authentication.* as *  | <further query>

This is not an exact query but this is how you must start.
Let me know if it helps!

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

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

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