Splunk Search

How do I show other fields after top?

Communicator

I'm not able to show other fields after top, below is my search string.

index=* type=event subtype=system logid=0100041000 | rex field=_raw "virdb\((?<virdbver>.*?)\) etdb\((?<etdbver>.*?)\)" | top 1 virdbver by devname | fields - percent count | sort -date -time | rename date as "Date:", time as "Time:", devname as "Device Name:", virdbver as "AV Definitions:"

The other fields I'm trying to show are, the date and time field. When I searched regarding this, I kept getting results saying that it's not possible to show other fields after doing top.

1 Solution

Esteemed Legend

You are misunderstanding what top does and assuming that it is like head. Even though there is a head command, it cannot be vectored like you are desiring. What you need is the dedup command. Try this:

index=* type=event subtype=system logid=0100041000 | rex field=_raw "virdb\((?<virdbver>.*?)\) etdb\((?<etdbver>.*?)\)"
| dedup devname
| table date time devname virdbver
| rename date AS "Date:" time AS "Time:", devname AS "Device Name:" virdbver AS "AV Definitions:"

Alternatively, you may be seeking this (if there can ever be downgrades to the virdbver):

index=* type=event subtype=system logid=0100041000 | rex field=_raw "virdb\((?<virdbver>.*?)\) etdb\((?<etdbver>.*?)\)"
| sort 0 - virdbver
| dedup devname
| table date time devname virdbver
| rename date AS "Date:" time AS "Time:", devname AS "Device Name:" virdbver AS "AV Definitions:"

Note: perhaps you will need to use sort 0 virdbver instead of sort 0 - virdbver; try both.

View solution in original post

Esteemed Legend

You are misunderstanding what top does and assuming that it is like head. Even though there is a head command, it cannot be vectored like you are desiring. What you need is the dedup command. Try this:

index=* type=event subtype=system logid=0100041000 | rex field=_raw "virdb\((?<virdbver>.*?)\) etdb\((?<etdbver>.*?)\)"
| dedup devname
| table date time devname virdbver
| rename date AS "Date:" time AS "Time:", devname AS "Device Name:" virdbver AS "AV Definitions:"

Alternatively, you may be seeking this (if there can ever be downgrades to the virdbver):

index=* type=event subtype=system logid=0100041000 | rex field=_raw "virdb\((?<virdbver>.*?)\) etdb\((?<etdbver>.*?)\)"
| sort 0 - virdbver
| dedup devname
| table date time devname virdbver
| rename date AS "Date:" time AS "Time:", devname AS "Device Name:" virdbver AS "AV Definitions:"

Note: perhaps you will need to use sort 0 virdbver instead of sort 0 - virdbver; try both.

View solution in original post

Communicator

Yes! Thank you so much! The second one worked like a charm! First one doesn't work because like I said in my other comment, some events don't have virdbver fields. The sorting of virdbver removes those events without virdbver field. Genius!

0 Karma

Esteemed Legend

Now do you see why I said your request was "nonsensical"? The only context that you gave us was your search in which you were (MIS)using the top command. Here are your mistakes:

1: You did not take the time to clearly explain what you were trying to do.
2: You made assumptions about how the top command works without reading the documentation.
3: Despite many comments and answers, you did not clearly restate your desires.
4: You downvoted people who were 100% correct (about your question being nonsensical).

As a result, many people wasted much time trying to help you and the worst part is that some were actually penalized for it. This is not the way to get help in the future. The bottom line is:

The BETTER QUALITY question that you ask, then quicker and better quality answers you will get. It is mostly up to you. We don't know what you mean; we have no choice but to go by what you say.

0 Karma

Esteemed Legend

You cannot show fields after top. This command does a statistical summary of the raw events and this process (obviously) consumes (supplants) those raw events. Think about it: If you asked "What were the top 10 most dangerous cities last year?" What "date" would you use? If your answer is 2015 then you can do this by adding | addinfo to the end of your search. This will add info_min_time and info_max_time to your search and you can do what you please with that. If you had any other answer, you are not only out of luck, but a rather strange person.

0 Karma

Communicator

addinfo unfortunately adds information about that search though. the date and time fields are from the event itself.

0 Karma

Communicator

I understand your "What were the top 10 most dangerous cities last year?" thingy but look at it this way instead,
"What's the latest AV definition on this device and when did it update?" That's what I want to know.

0 Karma

Esteemed Legend

Now I see your problem; see new answer.

0 Karma

Esteemed Legend

As I said, your desire is nonsensical. If you can explain a rational context for your desire (what kind of _time value makes any sense at all) then people can give you a solution that uses a stats instead of top.

0 Karma

Communicator

I'm sorry but, I don't get you. Why is my desire nonsensical? I'm trying to Splunk to only show the highest definition value of devname and show the date and time of when it updated to said value of definition. Why is this desire nonsensical?

0 Karma

Esteemed Legend

I already explained why it is nonsensical. Go back and re-read it. Maybe you need to read the documentation for the top command:

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/top

0 Karma

Super Champion

by this fields - percent count, you are restricting the results only to two fields - "percent count".
please try - fields - percent count date time

 index=* type=event subtype=system logid=0100041000 | rex field=_raw "virdb\((?.*?)\) etdb\((?.*?)\)" | top 1 virdbver by devname | fields - percent count date time | sort -date -time | rename date as "Date:", time as "Time:", devname as "Device Name:", virdbver as "AV Definitions:"
0 Karma

Communicator

Don't know why my previous reply to your comment got removed but, "fields - percent count" removes those fields.

0 Karma

Builder

After the top command you are creating a table with 4 fields: virdbver, devname, count and percent. If you want the field date and time, you must use them on the top command e.g:

index=* type=event subtype=system logid=0100041000 | rex field=_raw "virdb\((?.*?)\) etdb\((?.*?)\)" | top 1 virdbver by devname, date, time
0 Karma

Communicator

Wow. Thanks! I didn't put the comma and it didn't work. That was why I asked the question. But I do have another problem. How do I move the columns?

EDIT: Sorry, it doesn't work. Once I put in the date and time, all the values come back. I only want the one with the highest virdbver value of each devname.

0 Karma

Super Champion

But I do have another problem. How do I move the columns?
on the "rename" command, you can change/move the "order" to move the columns.
rename virdbver as "AV Definitions:", devname as "Device Name:", date as "Date:", time as "Time:"

0 Karma

Esteemed Legend

If you have fields C A B in that order, you can rearrange them like this | fields A B C and to rename then you just add this | rename A AS X B AS Y C AS Z.

0 Karma

Communicator

but putting fields after top will undo the top. For example if I don't have fields after top, it gets me the top value for virdbver by devname as such,

              firewall1                   definition1.11
              firewall2                   definition1.11
              firewall3                   definition1.09

but after I put in fields, it becomes like this,

              firewall1                   definition1.11
              firewall1                   definition1.10
              firewall1                   definition1.09
              firewall2                   definition1.11
              firewall3                   definition1.09
0 Karma

Communicator

That doesn't work. As you can see from above, I've already set it to rename date as "Date:", time as "Time:", devname as "Device Name:", virdbver as "AV Definitions:" but yet, it's coming out as Device Name, Date, Time, AV Definitions.

0 Karma