Guidance on query that creates a chart over time of company emails impacted by breaches

[cobalt]

Hello,

I have 2 queries related to a chart I am creating in relation to showing company email addresses part of data breaches represented as a stacked column chart.

The first issue is the conversion of the year and month breakdown using regex:

index=all_breaches company_email=* breach=* | rex field="breach_date" "^(?<year>[^-]+)-(?<month>[^-]+)-(?<day>.+)" | eval month=strftime("month","%b") | chart count by year, month

The issue with the above query is that I want to convert the month to abbreviated month e.g. Jan, Feb rather than 01,02. This query only shows the first month (Jan) rather than all months (changing eval month is "mon" results in Null value field). How do show all months represented in abbreviated form?

**See the chart below that uses this query

index=all_breaches company_email=* breach=* | rex field="breach_date" "^(?<year>[^-]+)-(?<month>[^-]+)-(?<day>.+)" | chart count by year, month

Second issue, how do I edit the above search  to show the "company_email" as part of the "breach" that is already broken down into months? Ideally the company_email forms part of the breach column chart per month.

Thanks in advance!

[kamlesh_vaghela]

@cobalt 

Can you please share some sample data of  breach_date & company_email ?

KV

[cobalt]

Sure @kamlesh_vaghela 

"firstname.lastname@company.com","2008-07-01","MySpace"

The above is the format for the fields: email, date, breach site

Some of the dates have a space after them which is why I have to do a regex on them.

[kamlesh_vaghela]

@cobalt 

Try

YOUR_SEARCH
| eval breach_date=strftime(strptime(breach_date,"%Y-%m-%d"),"%Y-%b")
| chart count by breach_date

and for email count try

| eval _time=strptime(breach_date,"%Y-%m-%d")
| timechart dc(email) as email_count

KV

[cobalt]

Those queries are not working unfortunately @kamlesh_vaghela 

My end state is to have one query which extracts the year-month-date, show it as month abbreviated per year that breaks down the number of emails part of overall breaches.

Original query:

index=all_breaches company_email=* breach=* | rex field="breach_date" "^(?<year>[^-]+)-(?<month>[^-]+)-(?<day>.+)" | eval month=strftime("month","%b") | chart count by year, month

The result is this:

However it need to show all months not just January, if I don't abbreviate the months then it works:

Once that is working, I would like to show within the same query the emails over breaches as currently you only see the totals for all emails involved in breaches and not the company emails part of it.

Ideal query:

index=all_breaches company_email=* breach=* | rex field="breach_date" "^(?<year>[^-]+)-(?<month>[^-]+)-(?<day>.+)" | ##Abbreviate month query## | chart count by year, month ## query to show company emails per all breaches across each month##

[kamlesh_vaghela]

@cobalt 

Can you please try this?

YOUR_SEARCH
| eval breach_date=strptime(breach_date,"%Y-%m-%d") 
| eval year=strftime(breach_date,"%Y") 
| eval month=strftime(breach_date,"%m-%b")
| chart count by year, month

My Sample Search :

| makeresults count=9 
| eval a=1 
| accum a 
| eval email="firstname.lastname@company.com,firstname"+a+"lastname@company.com",breach_date="2020-0"+a+"-01",breach_site="MySpace",email=split(email,",") 
| mvexpand email 
| rename comment as "Upto Now is sample data only" 
| table email breach_date breach_site 
| eval breach_date=strptime(breach_date,"%Y-%m-%d") 
| eval year=strftime(breach_date,"%Y") 
| eval month=strftime(breach_date,"%m-%b")
| chart count by year, month

Here I have used below eval for arranging Months in proper order.

| eval month=strftime(breach_date,"%m-%b")

You can replace it with,

| eval month=strftime(breach_date,"%b")

KV

[cobalt]

@kamlesh_vaghela I figured out the date to render in abbreviated form:

index=breaches email=* breach_site=* | rex field="breach_date" "^(?<year>[^-]+)-(?<month>[^-]+)-(?<day>.+)"| eval mon=case(month="01","Jan",month="02","Feb",month="03","Mar",
month="04","Apr",month="05","May",month="06","Jun",month="07","Jul",
month="08","Aug",month="09","Sep",month="10","Oct",month="11","Nov",
month="12","Dec") | chart count by year mon useother=false

I just need to tally the amount of "email" as part of overall "breach_site".

The current output only shows all emails part of "breach_site" while I need to include "email" as part of "breach_site".

Ideally, you can should be able to determine per month how many company emails were part of all emails in a breach. As I mentioned above the below only shows all emails with no breakdown to include company emails.