LEQL question - order by

Hi there!

Is it possible to have a dashboard card with a list, ordered by timestamp showing user, event and timestamp? I’d use this to create a card for remote access for VPN to check who was the last one connecting/authenticating and how (if there are different VPN methods in the logs).

Example how I want the list to be:
Robert | VPN1 | 2022-12-12 10:00
Michael | VPN2 | 2022-12-12 09:00
Robert | VPN2 | 2022-12-12 08:00

I always end up with a list of users and a count of the timestamps next to it, there I can see how often one single user has connected within the timeframe of the dashboard, but I want to see who and in which order logged in when within the timeframe of the dasboard. Is that possible?

Example how the list actual is:
Michael | VPN2 | 1
Robert | VPN1 | 1
Robert | VPN2 | 1

Best regards

Hey @RHolzer,

Let me give this a whirl


What is the keyword you use for your VPN1 or VPN2, is it structured or are you pulling that out of source_data either by regex or custom data parser?


In short I would recommend using the “sort(asc#key)” post groupby(). I did something similar which grabs the entire date/time without the miliseconds, it annoying still has the T in the middle but at least you don’t need to do two field extractions, I’ll post both:

where(/"timestamp":"(?P<date_time>[^\.]*)/)groupby(user, service, date_time)sort(asc#key, asc#key, asc#key)

where((/"timestamp":"(?P<date>[^"T"]*)/) AND (/"timestamp":"\d{4}-\d{2}-\d{2}T(?P<time>[^\.]*)/))groupby(user,service,date,time)sort(asc#key, asc#key, asc#key, asc#key)

I had to use multiple “sort(asc#key)” to ensure that all groupby keywords would be in ascending order. It’s ugly but should help you out.

1 Like

Hi @SDavis, thanks for your help!

I’ve tried you query on my dashboard in a card as a table chart, but again didn’t get the table I wanted. It now looks like this and I can click on the user to get more information (service, timestamp):

| User | Count |
| A | 2 |
| B | 1 |
| C | 3 |
| D | 1 |

But I want it to look like this:

| User | Service | timestamp |
| D | VPN | 2022-12-13 12:00 |
| A | VPN | 2022-12-13 11:00 |
| C | VPN | 2022-12-13 10:30 |
| C | VPN | 2022-12-13 10:00 |
| B | VPN | 2022-12-13 09:00 |
| A | VPN | 2022-12-13 08:00 |
| C | VPN | 2022-12-13 07:00 |

Basically I want a table, showing User, Service and Timestamp of an event source (everything is structered data), ordered by timestamp, maybe with a limit of 10 thus only showing the most recent events. In this case I don’t need the count of the connections, ordered by name of the user or count of the connections, only a list of the most recent events.

Dashboard cards for IDR are currently limited to only two columns and the second column would have to be a calculation e.g. count, unique count, etc

Drilling down into the second and third levels of groupby are the only way the dashboard currently shows that data unfortunately.

1 Like

It has certainly been suggested to increase the functionality of the dashboards and the scope of the table style cards, however we don’t have a set hard time for delivery on that.

Depending on what you’re trying to do, you could essentially accomplish the same thing with a saved search and only selecting the specific columns to be displayed that you want.

1 Like

Hi @john_hartman, thanks for the clarification. The idea was to display this kind of information on a dashboard card without the need to go to the log search anymore. I know I can show filtered date in a table at the log search, that’s fine, but it would be nice to have this information next to all my other information from my dashboard.

What i suggest is that you create a support ticket as an RFE that way you can track the progress and get updates of that project. Like I said, I’m pretty sure this has also been recommended so there’s probably a jira ticket you could get assigned to.

As is most things, our priorities are heavily focused on customer requests so the more the merrier when it comes to these RFEs

Oh man, I thought you were just looking for the query, my bad, misread your original post, yeah what @john_hartman stated, that functionality isn’t there yet.

Good news, the dashboards have now been updated to allow for this capability!

Start with a WHERE clause but no functions and then select the table option. From there you can manage columns and create the table that you want!

1 Like

Perfect! Yes, that’s the spirit! :smiley:

… and now let’s talk about bulk edit for investigations! :innocent: