topic Re: How to combine three queries and visualize it in timechart in Splunk Search

How to combine three queries and visualize it in timechart

amitrinx — Tue, 14 Mar 2023 03:42:26 GMT

I have three queries:

Overall Traffic to LogOn page

sourcetype="od" operation=LogOn http_method=GET http_url="*LogOn*" | timechart count span=1m

OAuth1 Traffic to LogOn page

sourcetype="od" operation=LogOn http_method=GET http_url="*LogOn*" http_url!="*authorization.ping*" identity_consumer_key!="" | timechart count span=1m

OAuth2 Traffic to LogOn page

This is what i wrote
sourcetype="oxygen-standard" identity_operation=LogOn http_method=GET
| eval url= case(http_url=="*LogOn*","Overall",
http_url=="*LogOn*" http_url!="*authorization.ping*" identity_consumer_key!="","OAuth1",
http_url=="*authorization.ping*","OAuth2")
| stats count by url

It is not allowing multiple checks in one case

sourcetype="od" operation=LogOn http_method=GET http_url="*authorization.ping*" | timechart count span=1m

How can i combine these three to show in one timechart?

Re: How to combine three queries and visualize it in timechart

yeahnah — Tue, 14 Mar 2023 04:18:56 GMT

Hi @amitrinx

I've tried to decipher your 3 queries and make a query to meet what you are looking to achieve. Without seeing any sample event data it's not always that easy so I've made some assumptions about the event data and detecting OAuth 1 or 2, which you may need to tweak.

sourcetype="oxygen-standard" OR sourcetype=od identity_operation=LogOn http_method=GET http_url=*
| eval url=if(isnull(identity_consumer_key) AND match(authorization.ping, "OAuth2"), if(isnull(identity_consumer_key) OR identity_consumer_key!="", "OAuth1", "unknown"), "noauthping")
| timechart span=1m
    count AS overall
    count(eval(url=="OAuth1")) AS OAuth1
    count(eval(url=="OAuth2")) AS OAuth2
    count(eval(url=="unknown")) AS unknown
    count(eval(url=="noauthping")) AS noauthping

OR, this should provide the same result set

sourcetype="oxygen-standard" OR sourcetype=od identity_operation=LogOn http_method=GET http_url=* ]
| eval url=if(isnull(identity_consumer_key) AND match(authorization.ping, "OAuth2"), if(isnull(identity_consumer_key) OR identity_consumer_key!="", "OAuth1", "unknown"), "noauthping")
| timechart span=1m
    count
  BY url
| addtotals fieldname="overall"

Hope this helps

Re: How to combine three queries and visualize it in timechart

gcusello — Tue, 14 Mar 2023 07:42:07 GMT

Hi @amitrinx ,

I'm not sure that's possible, aniway, you could try to use append, something like this:

sourcetype="od" operation=LogOn http_method=GET http_url="*LogOn*" http_url!="*authorization.ping*" identity_consumer_key!="" | timechart count span=1m | append [ search sourcetype="oxygen-standard" identity_operation=LogOn http_method=GET | eval url= case(http_url=="*LogOn*","Overall", http_url=="*LogOn*" http_url!="*authorization.ping*" identity_consumer_key!="","OAuth1", http_url=="*authorization.ping*","OAuth2") | timechart span=1m count by url ] | append [ search sourcetype="od" operation=LogOn http_method=GET http_url="*authorization.ping*" | timechart count span=1m ]

remember to setup the same span in all searches.

Ciao.

Giuseppe

Re: How to combine three queries and visualize it in timechart

PickleRick — Tue, 14 Mar 2023 08:21:05 GMT

OK. Try to describe what you want to achieve (in terms of what events you have and what result you want to get from them), not what you're trying to do.