topic Re: Timchart - Search queries in Splunk Search

Timchart - Search queries

ppurokit — Mon, 28 Sep 2020 16:34:46 GMT

Hi All,

I have the following search queries with me.

index=XXX CISE_Failed_Attempts | timechart span=30m count by CISE_Failed_Attempts | rename NULL as "Failed Authentication Count"

index=XXX CISE_Passed_Authentications | timechart span=30m count by CISE_Passed_Authentications | rename NULL as "Passed Authentications Count"

Is there a way where i could combine both the queries together into a single query like the following

index=XXX CISE_Passed_Authentications OR CISE_Failed_Attempts | timechart span=30m count by CISE_Passed_Authentications , CISE_Failed_Attempts

Individually the search queries work fine. But when i try to do a timechart with two fields it fails.

Please suggest a way to overcome this issue.

Re: Timchart - Search queries

martin_mueller — Sun, 11 May 2014 15:29:29 GMT

The most naive way to combine the two would be appendcols:

index=XXX CISE_Failed_Attempts | timechart span=30m count by CISE_Failed_Attempts | rename NULL as "Failed Authentication Count"
| appendcols 
[ search index=XXX CISE_Passed_Authentications | timechart span=30m count by CISE_Passed_Authentications | rename NULL as "Passed Authentications Count" ]

Depending on your data, you may be able to combine the two more smartly, for example like this:

index=XXX CISE_Failed_Attempts OR CISE_Passed_Authentications | eval CISE_Combined = coalesce(CISE_Failed_Attempts, CISE_Passed_Authentications) | timechart span=30m count by CISE_Combined

Note, I've assumed that those two fields have reasonable values... I'm doubting that assumption a bit because of your use of the NULL column. Post some sample data to investigate that.

Re: Timchart - Search queries

ppurokit — Sun, 11 May 2014 16:39:18 GMT

Thanks for the update. Actually my intention is to combine these into a single query and put it into a summary index using sitimechart command and then again write a query to populate back the dashboard,

Re: Timchart - Search queries

ppurokit — Sun, 11 May 2014 16:45:12 GMT

I hope i cant make use of the coalesce command because both are combined together into a single column. But i need both to be separate.

As said i can make use of the said appendcols,but fear that im executing two searches at the same time which could take more time to run.

Re: Timchart - Search queries

martin_mueller — Sun, 11 May 2014 16:47:20 GMT

What values do those two fields CISE_Failed_Attempts and CISE_Passed_Authentications have that you use to group by? If their values are distinct then combining them before the timechart will still yield their distinct values for each column.