How do I exclude columns in timechart when limit doesn't do the trick?

[ADTJedi]

I am conducting the following search (account names have been hidden):

sourcetype=WinEventLog:Security EventCode=4740 (Account_Name=****** OR Account_Name=******) 
|timechart span=1w count by Account_Name

In the events that meet the search criteria, there are two associated account names: one identified in the search and an account name of a server or computer accessed in the same event. As a result, my results are cluttered with extra columns (see example below).

_time↕         Server1↕   Server2↕   ******↕   ******↕ 
2015-09-20       449        181        175       178  
2015-09-27       225         0         165        0  

Can I write my search in such a way that only the users (i.e. ******) show up in the results? I tried using Limit=# and Useother=0, but if the server totals outweigh the user numbers, that doesn't help.

Very new user here. I did search the knowledgebase for awhile. Thank you for any help!

[ADTJedi]

To clarify the above, the Account_Name examples above are actually 6 characters. I just replaced with asterisks to anonymize. Also, the server names that are showing up are all different. Examples that aren't real but give the idea would be something along the lines of: SanDiehg76, Dall783thg, Seatthhn783. Also, just to clarify, each event lists TWO Account_Name results. One is always the user I am interested in appearing in the report, the other is a server I am not. Hope this helps clarify.

[ramdaspr]

Thats happening because the Account_Name field is multi Value field and both get counted because they are a part of the same event.

your query would need to be

sourcetype=WinEventLog:Security EventCode=4740  | mvexpand Account_Name| where (Account_Name=****** OR Account_Name=******) 
 |timechart span=1w count by Account_Name

The mvexpand breaks the Account_Name field and then you filter it down to the account names you care about.

[ADTJedi]

ramdaspr,
Thank you for your reply. I tried your solution and I didn't get any results. My original solution returned thousands of results (with the extra columns I don't want.) Thanks again for trying. It looks like I have a couple more options to try so I will try those next.
Russ

[ramdaspr]

Thats odd, because I am pretty sure that is the solution (I am using it on one of my dashboards).
If you have the time for it, can you check out the query part by part
first run through
sourcetype=WinEventLog:Security EventCode=4740 and verify output then

sourcetype=WinEventLog:Security EventCode=4740  | mvexpand Account_Name and verify output then

sourcetype=WinEventLog:Security EventCode=4740  | mvexpand Account_Name| where (Account_Name=****** OR Account_Name=******)  to verify that output?

[yuanliu]

If the asterisks are merely tokens for anonymization in this post, i.e., if your search is like sourcetype=WinEventLog:Security EventCode=4740 (Account_Name=User1 OR Account_Name=User2), I cannot see how this search can turn up Server1 and Server2. (You probably need to give slightly more specific examples.)

If using asterisks ( *) is a requirement in your search (and data), there can be two approaches.

• If your server name has a pattern, e.g., Server1, Server2, you can do sourcetype=WinEventLog:Security EventCode=4740 (Account_Name=****** OR Account_Name=*****) Account_Name!=Server |timechart span=1w count by Account_Name
• Otherwise use regex after search sourcetype=WinEventLog:Security EventCode=4740 (Account_Name=****** OR Account_Name=***) | where match(Account_Name,"***") |timechart span=1w count by Account_Name Something like that. You need to fine tune regex and logic.

[ADTJedi]

yuanliu,
Thank you for your ideas. I cannot use your first idea. I clarified my question above, but basically, the Server names I used in the example were given just as general examples. However, unfortunately they aren't conveniently named as I originally implied (i.e. "Server#"). Instead, each one is very different, and if I'm going to use this as something I can run in different time selections, I will not be able to generalize with a wildcard.

Your second idea looks promising. Quick question...if I have several Account_Name items, in the "where" statement you suggest, do I list all of the account names like this:

 |where match(Account_Name, "*****1" "*****2" "*****3")

Or do I separate with commas or some other syntax?
Thank you again for your ideas!
Russ

[yuanliu]

Ah, to simultaneously pick out several fixed accounts, you can do | where match(Account_Name, "ABC|DEF|GHI"). Vertical bars in regular expressions are meta characters to indicate alternatives. If there is some pattern, it is easier to use a regular expression to match pattern. For example, if server names contain a certain letter pattern like a company name, and ends with a number, whereas a user name will never end with number (just hypothetical), you can say |where !match(Account_Name,".+-MyCo.+\d"). Regular expression is very powerful, but requires a little learning curve.