About bowesmana

bowesmana

This won't work as you want | stats latest(Deployed_Data_time) AS Deployed_Data_time values(env) AS env max(latestVersion) AS latestVersion BY app latest() function is based on the _time field, so if you want Deployed_Data_time to be _time then you need to evaluate it | eval _time=strptime(Deployed_Data_time,"%m/%d/%Y %H:%M") but you also cannot do max(latestVersion) as that is simply doing a numeric comparison on the date, which is a string, so 4/16/2024 is LESS than 9/15/2023 - 4 is less than 9. If you ever want to do string based date comparisons, you need them to be ISO8601, i.e. YYYY-MM-DD-HH:MM:SS So, using your example data, is this what you want? | makeresults format=csv data="Deployed_Data_time,env,app,version 4/16/2024 15:29,axe1,app1,v-228 4/16/2024 15:29,axe1,app1,v-228 9/15/2023 8:12,axe1,app1,v-131 9/15/2023 8:05,axe2,app1,v-120 9/12/2023 1:19,axe2,app1, v-128 4/16/2024 15:29,axe2,app2,v-628 4/16/2024 15:26,axe2,app2,v-626 9/15/2023 8:12,axe2,app2,v-531 9/15/2023 8:05,axe1,app2,v-530 9/12/2023 1:19,axe1,app2, v-528" | rex field=version "v-(?<v>\d+)" | stats max(v) AS version BY app env | table app,version,env | chart values(version) by app, env limit=0 | fillnull value="Not Deployed"

bowesmana

Yes, unfortunately this is the way it works - I have never fully worked out why this is the case - but most of the time it doesn't really matter as - I have used techniques to solve this where I needed to only get the fields that pertained to the particular event, but that involved quite a bit of other work You can do something simple like search bla | transpose 0 | where isnotnull('row 1') | transpose 0 header_field=column | fields - column If this is just about data investigation and looking for things. Give us more on any use case where this is an issue and we can see if there is a way to solve it.

bowesmana

The number of values in the subsearch cannot be too large as it will perform really badly, but one slight change to @ITWhisperer subsearch is to do <main index> NOT [ search <other source> NoHandlerFoundException | stats values(xCorrelationId) as search | format] which will perform faster. It will change the outer search from <main index> NOT ( ( ( xCorrelationId=A OR xCorrelationId=B OR... ) ) ) to <main index> NOT ( ( ( A OR B OR C OR D ... ) ) ) where A, B etc are the values or xCorrelationId The key point is having a field name 'search' in the output rather than xCorrelationId, which changes the effect of the format command.

bowesmana

As @PickleRick says, without knowing your data, it's not totally clear what may work in your case. Are you actually searching the same index in both cases? From your search example, the subsearch will contain the events from the outer search. You can generally combine two data sets into a single search and do some data munging before doing stats values() so (index="index" "mysearchtext") OR (index="index") ``` This will get a field called mr_id from any event that has a field called message that has the pattern match ``` | rex field=message ", request_id: \\\"(?<mr_id>[^\\\"]+)" ``` This statement will extract the request.id from a JSON payload ``` | spath request.id ``` This creates a common field called id, which is either taken from a non null request.id or from the mr_id. Depending on the event type the id is extracted from, you should hopefully end up with a single id. Note that if your source index really is "index" then this assumes that the id coming from events with mysearchtext will NOT also have a JSON payload with the request id ``` | eval id=coalesce('request.id', mr_id) ``` This then aggregates the fields by the common id. Note it uses values() so you don't know which event came at which time but that can be solved if needed ``` | stats values(mynewfield) as mynewfield values(_time) as times by request_id ``` This just formats the multivalue times field to human readable | eval times=strftime(times, "%F %T.%Q")

bowesmana

@GaryZ Unfortunately you can't go from chart to table. Look at the XML and you will see that <chart> or <table> are completely separate XML types and have a whole different bunch of configuration options. The token replacement mechanism simply replaces the <option name="charting.chart">$chart$</option> with the appropriate piece of text - all other options being equal, the replacement will work. The way to solve this problem is to have both panels, one for charts and one for table and use token dependency to switch between the panels, like this example. Note that it uses a base search which is used to populate both searches, so no duplication. You can then use any post processing in a panel that requires additional processing to suit the visualisation. <form version="1.1"> <label>Visualisation Selection</label> <search id="base_panel_data"> <query> | makeresults count=5000 | eval car=mvindex(split("Volvo,Mercedes,VW,Porsche,Jaguar,Tesla,BYD,Toyota,Suzuki",","), ((random() % 97) * (random() % 71)) % 9) | stats count by car </query> </search> <fieldset submitButton="false"></fieldset> <row> <panel> <input type="dropdown" token="viz_type" searchWhenChanged="true"> <label>What viz type</label> <choice value="pie">Pie</choice> <choice value="bar">Bar</choice> <choice value="line">Line</choice> <choice value="column">Column</choice> <choice value="table">Table</choice> <change> <condition value="table"> <unset token="show_chart"></unset> <set token="show_table"></set> </condition> <condition> <set token="show_chart"></set> <unset token="show_table"></unset> </condition> </change> </input> <chart depends="$show_chart$"> <search base="base_panel_data"> <query> </query> </search> <option name="charting.chart">$viz_type$</option> <option name="charting.drilldown">all</option> </chart> <table depends="$show_table$"> <search base="base_panel_data"> <query> </query> </search> </table> </panel> </row> </form>

bowesmana

As @yuanliu says, my queries give you a table - if you are indicating the table does not come back in the order the panels are defined in the CSV, that's unfortunately a feature of Splunk. You can add this final line to order the columns as per the CSV | fields [ | inputlookup panels.csv | eval Panels="\"".Panels."\"" | stats list(Panels) as Panels | return $Panels ]

bowesmana

Unfortunately adding KV stores does require a level of privileges - I believe you need admin_all_objects. You do have to create the collections and transforms conf files - I suspect you will need to run this past the admin, as they will most likely have to create it for you - the Splunk app for lookup file editing does allow you to create KV store definitions, both collections and transforms, but you will need those privileges. There mey be some environmental issues around using the KV store in that admins would like to know about 😞

bowesmana

so what did you try and what gave you the wrong results This is the basic search index=_internal source=/opt/splunk/var/log/splunk/license_usage.log idx=* st=* | stats sum(b) as bytes by idx | eval gb=round(bytes/1024/1024/1024,3) Run that over the time range you want

bowesmana

There is a table visualisation in Splunk and when you run that command you are getting a table visualisation. Perhaps you can describe your data better, because you are clearly looking for something different than just panels a b c. Your post describing this Panels Blacklisted Software Exceptions Clients missing critical updates Clients with blacklisted Software Clients with old Defender patterns Critical severity vulnerabilities Defender enrollment status High severity vulnerabilities Local virtual machines Outdated operating systems - Endpoint Outdated operating systems - Unknown Outdated operation systems - Server Servers with blacklisted Software Systems not found in patch management database Total Installed blacklisted Software Vulnerabilities solved doesn't actually tell me anything useful - can you describe your lookup data, what it contains and give a better description of how you want the data to look in your table.

bowesmana

You can also add this on the end of that previous post which will make the column name the value of the panel and the value of the column=1 | foreach row* [ eval {<<FIELD>>}=1 ] | fields - row*

bowesmana

You can do this | inputlookup panels.csv | transpose 0 what do you want the column headings to be? That will give you columns called row 1, row 2, row 3 and so on with the values found.

bowesmana

Building on @gcusello approach, it can be done more efficiently by not using mvexpand and just filtering out the ones that do not match the max value. index=abc host IN () | eval col=_time."|".response_time | stats max(response_time) AS max_response_time values(col) AS col BY URL | eval times=mvmap(col, if(match(col, "\|".max_response_time."$"), mvindex(split(col, "|"), 0), null())) | fields URL max_response_time times | eval times=strftime(times, "%F %T.%Q") | rename max_response_time as "Maximum Response Time" | sort - "Maximum Response Time" Note that if you have LOTS of values and lots of URLs you may get a spike in memory usage retaining all the values. Note this also handles the situation where the max response time occurs in more than one time. You can also do this with eventstats index=abc host IN () | fields _time response_time URL | eventstats max(response_time) AS max_response_time by URL | where response_time=max_response_time | stats values(max_response_time) AS "Maximum Response Time" values(_time) as times BY URL | eval times=strftime(times, "%F %T.%Q") | sort - "Maximum Response Time" Check which will perform better with your data - eventstats can be slow if crunching lots of data. You can see an example of how this works using either of the techniques above by replacing index=abc... with this, which will give you some simulated data | makeresults count=1000 | streamstats c | eval _time=now() - c | eval response_time=random() % 1000 | eval URL=mvindex(split("URL1,URL2,URL3,URL4",","), random() % 4) | fields - c

bowesmana

Try this | inputlookup your_lookup.csv | stats values(panels) as panels | eval panels=mvjoin(panels, " ")

bowesmana

@anooshacno it's not, you need to look at the $result.has_runtime$ token - see my example

bowesmana

And if you then want to make that a bar chart, replace the fields - c at the end with | fields myFIELD | mvexpand myFIELD | eval count=tonumber(myFIELD)

bowesmana

bowesmana

Yes, it's very easy. Just edit the chart type setting in the XML to use a token and then in your input give the appropriate options, e.g. <panel> <input type="dropdown" token="viz_type" searchWhenChanged="true"> <label>What viz type</label> <choice value="pie">Pie</choice> <choice value="bar">Bar</choice> <choice value="line">Line</choice> <choice value="column">Column</choice> </input> <chart> <search> <query> | makeresults count=5000 | eval car=mvindex(split("Volvo,Mercedes,VW,Porsche",","),random() % 4) | stats count by car </query> </search> <option name="charting.chart">$viz_type$</option> <option name="charting.drilldown">all</option> </chart> </panel>

bowesmana

What's your SPL?

bowesmana

I am not sure how you managed to create that because that XML is completely broken and is not a valid dashboard. Your <choice> values are not valid XML, e.g. you can't have value=multiple quoted strings. <choice value="appdev1host","logdev1host","cordev1host">DEV1</choice> Why don't you just make your choice value something like <choice value="*dev1host">DEV1</choice> and so on. Also, not sure what you are trying to achieve with your SPL - are "Total count" and "Incoming count" fields in your data? Using appendcols is not a good technique as you are repeating almost the identical search, which is not necessary. If you want to share an example of your data I can help suggest a correct search.

bowesmana

summaryindex and collect are synonyms - I believe summaryindex is just an alias for the documented collect command. Your understand is correct re the two searches. (1) happens before (2) and (2) can be done as often as needed in the same day until (1) happens again the following day. That link is about moving existing CSV contents to KV store. You don't need a CSV to get data to a lookup. You can simply search data | outputlookup kv_store_lookup Note that a KV store lookup is a lookup definition, not a lookup table file. A CSV is a lookup table file, but can also have a definition associated with it (and it's good practice), whereas a KV store lookup definition just requires the definition and an associated collection to be defined. You can create collections using the Splunk app for lookup editing https://docs.splunk.com/Documentation/SplunkCloud/latest/Knowledge/DefineaKVStorelookupinSplunkWeb

bowesmana

As @ITWhisperer you only have access to the first result of the table in the <done> clause, but assuming you only have a single result then you can set the token based on that very simply using <eval> <done> <eval token="tok_runtime">if($result.has_runtime$="Yes", "true", null())</eval> </done> If you have multiple results, then this would work <search> <query> index="abc" sourcetype="abc" Info.Title="$Title$" |spath output=Runtime_data path=Info.runtime_data | eval has_runtime = if(isnotnull(Runtime_data), 1, 0) | table _time, has_runtime | eventstats max(has_runtime) as has_runtime </query> <done> <eval token="tok_runtime">if($result.has_runtime$>0, "true", null())</eval> </done> </search>

bowesmana

A bit more information would be useful, but this is a start and is the general technique for combining two data types on a common field index=bla | stats values(*) as * by Info.Title

bowesmana

I guess I'm only seeing half the picture here. I understand you're trying to make a lookup into a index so the idea of rolling forward data is to make 'yesterday' have the entire dataset you care about regardless of any update dates. collect is just a Splunk command that you add to the end of your SPL. Manual or automatic is about whether a search is scheduled or not, nothing to do with what the SPL does. https://docs.splunk.com/Documentation/SplunkCloud/9.1.2312/SearchReference/collect If you have a scheduled saved search collect will just collect to the summary index. It is the same as enabling summary indexing on a scheduled saved search, but you have direct control of the parameters. KV store uses a database in Splunk - it used to be mongodb - not sure if that's still the case. You don't need to care - for all intents and purposes, it's just a lookup, just backed by a database, not a CSV. https://docs.splunk.com/Documentation/Splunk/latest/Knowledge/DefineaKVStorelookupinSplunkWeb As for the append - I don't know what you're actually trying to merge together from the vulnerabilities index and what comes from the dbxquery - that's a perfectly valid technique for combining data - but what will you do with that when you have it. As I said, I've only got half the picture of your whole journey...

bowesmana

I don't see why it needs to be split - the events not coming back in subsecond order does not matter to you, so why not just add the 200k in one go - is that causing a problem?

bowesmana

Sorry, I don't understand what you are talking about re splitting your data - what is being split with the dbxquery?

Posts	2862
Solutions	644
Karma Given	246
Karma Received	1065
Member Since	‎09-05-2011

Online Status	Offline
Date Last Visited	yesterday

Windows TA not extracting key_path as registry_pat...

Why does FIELDALIAS work or doesn't depending on t...

How can I write this regex for rex to extract mult...

How to rename fields in a table without changing c...

Why doesn't drilldown eval token statement work un...

Performance fields/stats/table

Infoblox TA not extracting response code

Is there any way to create CSS that can be dynamic...

How to break up groups of <input> elements onto th...

tokenlinks.js in Splunk 8.24 broken- working dashb...

Re: group threefields and get the latest timestamp...

Re: Table showing fields from excluded events afte...

Re: Extend search results data by correlation-id (...

Re: Replace join with a more performant query

Re: Can you dynamically change the charts (ie. fro...

Re: splunk lookup

Re: Re-write DBXquery to summaryindex daily

Re: Daily Ingest per index for last 7 days

Re: splunk lookup

Re: splunk lookup

Re: splunk lookup

Re: need to add the respective time for the maximu...

Re: splunk lookup

Re: How to match 2 data sets in JSON events using ...

Re: generating a graphic in dashboard xml code

Re: generating a graphic in dashboard xml code

Re: Can you dynamically change the charts (ie. fro...

Re: Empty rows in Table

Re: host drop down

Re: Re-write DBXquery to summaryindex daily

Re: How to match 2 data sets in JSON events using ...

Re: How to match 2 data sets in JSON events using ...

Re: Re-write DBXquery to summaryindex daily

Re: How to solve " Events might not be returned in...

Re: How to solve " Events might not be returned in...