Deployment Architecture

How to Transpose Correct Query

strawberry28
Explorer

OLD Query:

source="http:Emerson_P1CDN" AND status_code=200 AND path=*/catalog* AND path!=*thumb* AND path!=*CartRefreshStatusJSON* AND path!=*PriceAjaxView* | bucket span=1m _time | stats count by client_isp,_time | where count >= 600 | convert timeformat="%m/%d/%Y %H:%M:%S" ctime(_time) | sort - count | transpose header_field=_time



When I set this one as an alert, it considers the client_isp and _time as part of the query so even there where no result it is sending a blank alert only the client_isp and time on the first column.

New Query:

source="http:Emerson_P1CDN" AND status_code=200 AND path=*/catalog* AND path!=*thumb* AND path!=*CartRefreshStatusJSON* AND path!=*PriceAjaxView* | bucket span=1m _time | stats count by client_isp,_time | transpose header_field=_time | sort - count | where count >= 600 | convert timeformat="%m/%d/%Y %H:%M:%S" ctime(_time)



While on this one, there were no result at all.

What maybe wrong on this query?

Labels (2)
Tags (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

The transpose command has an option to deal with this situation.

You could try something like this

source="http:Emerson_P1CDN" AND status_code=200 AND path=*/catalog* AND path!=*thumb* AND path!=*CartRefreshStatusJSON* AND path!=*PriceAjaxView* 
| bucket span=1m _time 
| stats count by client_isp,_time 
| where count >= 600 
| convert timeformat="%m/%d/%Y %H:%M:%S" ctime(_time) 
| sort - count 
| transpose header_field=_time include_empty=f

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

The transpose command has an option to deal with this situation.

You could try something like this

source="http:Emerson_P1CDN" AND status_code=200 AND path=*/catalog* AND path!=*thumb* AND path!=*CartRefreshStatusJSON* AND path!=*PriceAjaxView* 
| bucket span=1m _time 
| stats count by client_isp,_time 
| where count >= 600 
| convert timeformat="%m/%d/%Y %H:%M:%S" ctime(_time) 
| sort - count 
| transpose header_field=_time include_empty=f
0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

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

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...